How do you set up automatic email alerts triggered by date

Question

I am struggling to write an apps script to trigger automatic emails based on a date. I have reviewed other questions/answers but can't get it right. My Google Sheet is below:

Google Sheet

My alert data is in the sheet called H&S Reviews. My email message is in cell A1 of the Email Alerts Sheet.

The trigger date to send an email is column J of the H&S Reviews sheet and I thought that the code could be written against this and cell J1 which is today's date.

Column J = date email to be sent
Column I = first name of email recipient
Column H = email address
Column B = task per message
Column C = description per message

My code is called functionsendEmails`. You will see that it needs your expertise.

Also, I am hoping that the alert could be written to the email recipients Google Calendar but not sure if this is possible.

Can I ask you about your question? 1. What is the issue of your script? 2. Although your script can be seen at your shared Spreadsheet, can you show the script related to your question to your question? 3. About the alert could be written to the email recipients Google Calendar, can you explain about the detail information? — Tanaike

Andres Duarte Andres Duarte · Accepted Answer · 2019-11-11T17:00:15

Here is the code from your script modified to send the email when date on J column match the date in J1. I recommend you change your code to use getValues() [1] and loop the resulting array instead of using getValue() every time, is much more optimized and will prevent you from hitting the Google quotas[2].

function sendEmails() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("H&S Reviews").activate();
  var lastRow = sheet.getLastRow();
  var message = spreadsheet.getSheetByName("Email Alerts").getRange(1,1).getValue();

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

    var emailAddress = sheet.getRange(i, 8).getValue();
    var firstName = sheet.getRange(i, 9).getValue();
    var todaysDate = sheet.getRange(1, 10).getValue();
    var date = sheet.getRange(i, 6).getValue();
    date = Utilities.formatDate(date,'GMT+0200','dd MMMM yyyy');
    var task = sheet.getRange(i, 2).getValue();
    var description = sheet.getRange(i, 3).getValue();
    var messageBody = message.replace("{name}",firstName).replace("{Task}",task).replace("{Description}",description).replace("{Date}",date);
    var subject = "Health & Safety Review Task"; 

    var sendDate = sheet.getRange(i, 10).getValue();
    var sheetDate = new Date(sendDate);
    Sdate=Utilities.formatDate(todaysDate,'GMT+0200','yyyy:MM:dd')
    SsheetDate=Utilities.formatDate(sheetDate,'GMT+0200', 'yyyy:MM:dd')
    Logger.log(Sdate+' =? '+SsheetDate)

    if (Sdate == SsheetDate){
      var subject = "Health & Safety Review Task";
      MailApp.sendEmail(emailAddress, subject, messageBody);
      Logger.log('SENT :'+emailAddress+'  '+subject+'  '+messageBody)
    }    
  }
}

[1] https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()

[2] https://developers.google.com/apps-script/guides/services/quotas

How do you set up automatic email alerts triggered by date

1 Answers