Apps script: Send html emails based on dates specified in different columns

Question

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

@Kos: Unfortunately not. And here is why: as explained in my initial question, the script you suggested considers just 1 column with reminder dates. But I have 3 columns with reminder dates. So, I need a script that can trigger my sendEmail() function, as soon as the reminder date is one of the dates specified in one of the 3 columns (L, M or N). Does it make sense? — Bob Hardball, Oct 22 '21 at 12:31
@Kos: By the way, I am pretty new at using Apps Script. So, please forgive me, if I am messing up something here. Thanks :) — Bob Hardball, Oct 22 '21 at 12:33
But you not just looking for free code right? So you can check linked answer and understand how it works, and then apply to 3 or 4 or 999 columns. If it's too hard, then hire someone to do this job for you — Kos, Oct 22 '21 at 12:41
@Kos - Not looking for free code. Just trying to figure out how to do it by myself and asking around at the same time. — Bob Hardball, Oct 22 '21 at 12:43

ale13 · Accepted Answer · 2021-10-22T14:05:55.560

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.

I just adapted your code to mine, and it´s working like magic. Thank you so much!!!! — Bob Hardball, Oct 22 '21 at 13:31
By the way, if you allow me this little remark: there was a bracket missing at the end (so, before the final curly bracket) in your modified sendEmail function. Thank you so much again :) — Bob Hardball, Oct 22 '21 at 14:04

Apps script: Send html emails based on dates specified in different columns

1 Answers1

Reference