CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Roman
Roman

Reputation: 1

Google Apps script. For loop is not working

I tried to write an Apps script for sending emails for those who forgot to update status report using google form. It's my 1st script and I tried to fix it for almost 5 hours unsuccessfully.

Here is code:

function sendEmails() {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("summary");
  var lastRow = sheet.getLastRow();

    for ( var i=2; i<=lastRow; i++){

      var currentEmail = sheet.getRange(i,11).getValue();
      var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("emailbody").getRange(1,1).getValue();
      var emailRecipient = sheet.getRange(i,2).getValue();
      var messageBody = templateText.replace("{name}",emailRecipient);

      var oldCheck = sheet.getRange(i,12).getValue();

      if (oldCheck = "Old"){
      MailApp.sendEmail(currentEmail,"Reminder: Fill the weekly status update form",messageBody); 
      } 
      else {
        continue;
      }

    }
  
}

It sends only 1 email for the first "old" check.

Upvotes: 0

Views: 873

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

Issue:

You want to compare the value of oldCheck with "Old" and not assign "Old" to oldCheck. You need to use == instead of =.

Improvements:

It is computationally expensive to use getValue inside a for loop. Instead use getValues before the for loop and work with the arrays. In this way, you eliminate reduntant API calls that make your script heavy especially when i increases.

Solution:

function sendEmails() {
    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheetByName("summary");
    var data = sheet.getDataRange().getValues();
    var templateText = spreadsheet.getSheetByName("emailbody").getRange(1,1).getValue();
    for (var i=1; i<data.length; i++){    
      var currentEmail = data[i][12];
      var emailRecipient = data[i][3];
      var messageBody = templateText.replace("{name}",emailRecipient);
      var oldCheck = data[i][13];  
      // be careful! == instead of =
      if (oldCheck == "Old"){
        MailApp.sendEmail(currentEmail,"Reminder: Fill the weekly status update form",messageBody); 
        } 
        else {
          continue;
        }
    }
}

Upvotes: 1

Related Questions