This issue has been discussed in detail here and here. But none of the scripts I´ve seen and analyzed with a great deal of attention seems to solve my problem. That´s why I am calling on all brilliant minds out there to help me.

Here are the details of my problem:

ISSUE:
My current script (see it below) sends out an html email to each recipient listed in my sheet and then set the status for each recipient receiving that html email to “EMAIL SENT” in column K. Now I want to send 3 additional html emails to each of these recipients exactly on the dates that I´ve already specified in column L, M and N for each recipient.

Example: recipient A received the first html email on 11.10.2021 (format used: day/month/year) and he is scheduled to receive the second html email on 25.10.2021, the third html email on 27.10.2021 and the forth/last html email on 29.10.2021. These dates for recipient A are specified in column L (25.10.2021), column M (27.10.2021) and column N (29.10.2021).

QUESTION:
How can I ensure that each recipient receives the additional emails on the dates specified for him in column L, M and N?

I´d really appreciate any help or hint to solve this problem.

Here is my current script:

function sendEmail() {
  // variables for the html template (html file) and the list of recipients (google sheet)
  const anrede = 2;
  const nachname = 3;
  const emailAdresse = 5;
  const terminTag = 6;
  const terminUhrzeit = 8;
  const terminURL = 9;
  let emailTemp = HtmlService.createTemplateFromFile('HTML_TEMPLATE');
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LIST_OF_RECIPIENTS");

  // send email based on my html template and recipients list
  var adminV = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("admin"); // open the sheet containing the subject of the email ("subject in German is "betreff")
  var betreff = adminV.getRange("A20").getValue(); // get the actual content of the sheet containing the subject ("subject in German is "betreff")
  var aliases = GmailApp.getAliases(); // get the alises of my gmail-account
  const sr = 3;//start row of data/recipients list from the google sheet
  const data = sh.getRange(sr, 1, sh.getLastRow() - sr   1, 11).getValues();
  data.forEach((row, i) => {
    if (row[10] == "EMAIL NOT SENT YET") {
      emailTemp.anrede1 = (row[anrede]);
      emailTemp.nachname1 = (row[nachname]);
      emailTemp.emailAdresse1 = (row[emailAdresse]);
      emailTemp.terminTag1 = (row[terminTag]);
      emailTemp.terminUhrzeit1 = (row[terminUhrzeit]);
      emailTemp.terminURL1 = (row[terminURL]);
      var htmlMessage = emailTemp.evaluate().getContent();
      GmailApp.sendEmail(row[emailAdresse],
        betreff, "This is an html email. Please change your seting to be able to read it.",
        { from: aliases[0], htmlBody: htmlMessage, replyTo: aliases[0] });
      sh.getRange(i   sr, 11).setValue("EMAIL SENT");//stops emails this particular email from being sent again, if the recipient has already received it.
    }
  });
}

CodePudding user response：

Since you want to send the emails for dates which are in the future, the best solution would be to use a time-based trigger. In this way, your function sendEmail will end up running every day and by checking the current date with the dates in L, M, N columns the email will be sent accordingly.

function sendEmail() {
   let today = new Date();
   let todayDate = Utilities.formatDate(today, "GMT", "dd.mm.yyyy").toString();
   // the rest of the code
   // add the rest of it in the for loop
   data.forEach((row, i) => {
      let lCell = sh.getRange(i   1, 12).getValue();
      let mCell = sh.getRange(i   1, 13).getValue();
      let nCell = sh.getRange(i   1, 14).getValue();
      if (lCell == todayDate || mCell == todayDate || nCell == todayDate) {
      // send email
      }
   }
}

function createTimeDrivenTrigger() {
    ScriptApp.newTrigger('sendEmail')
      .timeBased()
      .everyDays(1)
      .create();
}

As for the createTimeDrivenTrigger function, this is the function that will create the trigger for the sendEmail function and by using everyDays(1) you are ensuring that this will end up running every day.

Reference

• Apps Script Manage Triggers Programmatically.