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.
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.
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;
}
}
}