Google Apps Script - Send Email based on value in cell

0
votes

I am new with Google App Scripts and have a spreadsheet with three columns: name, overdue and last contacted: https://docs.google.com/spreadsheets/d/1W04JiFZwpGCD-qddUkNJ9xQ1xiYwZnGhissudF1EvxM/edit?usp=sharing

I would like to send an email reminder to myself which contains the name of anyone with the value overdue in column B.

So far, I have only been able to write a script that sends an email to myself for row 2. I can't figure out how to get the script to loop through the values in column B.

Here is my script:

function sendEmail() {
  
  var overdueRange = SpreadsheetApp.getActiveSpreadsheet().getRange("B2"); 
  var overdueValue = overdueRange.getValue();
  if (overdueValue === "Overdue"){
    var nameRange = SpreadsheetApp.getActiveSpreadsheet().getRange("A2");
    var name = nameRange.getValues();
    var message = 'Reach out to ' + name 
    var subject = 'Reach out to this person.'
    MailApp.sendEmail('[email protected]', subject, message);
    }
}

sendEmail()
1
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-apps
You are calling sendEmail twice like that. careful also with getValues which returns a 2d array. Also in this way you are requesting for the active sheet. Instead, specify the sheet name. This won't work as a time driven trigger function if you are planning to use an active sheet like that. After you sort all of this out you can think of how you can create a loop. - soMario

1 Answers

2
votes

Explanation:

Just a few points to make the process clear:

  • I assume you are executing sendEmail() from the script editor. In this way, since you also have sendEmail() outside of the function, you will end up calling sendEmail() twice. Remove the 'global' call of the function and call only sendEmail() from the script editor.

  • It is not recommended to apply getRange on a spreadsheet object. Define a particular sheet by its name or position. In my example script below, I define the sheet by its name (Sheet1). Change that name to your actual case.

  • name in your code is a 2D array and not a single value. Be careful with this as it is not the correct way to do it.

  • In my script below, I define an array of columns A and B. Names are defined in column A and overdue status in column B. In this way you can iterate this array, and get single values per iteration by accessing the array itself. I used forEach but feel free to use your own iteration method.

Solution:

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1'); // change Sheet1 to the name of your sheet
  const data = sh.getRange('A2:B'+sh.getLastRow()).getValues();
  data.forEach(r=>{
     let overdueValue = r[1];  
     if (overdueValue === "Overdue"){
         let name = r[0];
         let message = 'Reach out to ' + name;
         let subject = 'Reach out to this person.'
         MailApp.sendEmail('[email protected]', subject, message); 
     }
  });  
}

Sheet that works for the script:

example