ISSUE:

My mail merge script sends out emails by using an html template and a list of recipients. The list of recipients is made of 16 columns (first column is A and last column is P). As soon as an email has been sent out, the value in the 11th column (so, column "K") should change from "EMAIL NOT SENT YET" to "EMAIL SENT" for each recipient. And any recipient who already has "EMAIL SENT" in the 11th column (so, column "K") should NOT receive another email.

ATTEMPTED SOLUTION:

The script of the mail merge is below. And as you´d certainly realize it, I got stuck in the last part of the code, where I simply cannot figure out how to include an IF-statement to get the work done.

QUESTION:

Which part of my script should I change to ensure that A) the status is automatically updated for each recipient in the 11th column (so, column "K) and B) nobody who has already received an email does NOT receive another one?

Thank you so much in advance for your help!

function JustSendEmail() {
  // variables to reference the sheet and its content
  var anrede = 2;
  var nachname = 3;
  var emailAdresse = 5;
  var terminTag = 6;
  var terminUhrzeit = 8;
  var terminURL = 9;

  var emailTemp = HtmlService.createTemplateFromFile('emailEinladung');
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("terminBestaetigen");
  var data = ws.getRange("A3:K"   ws.getLastRow()).getValues();

  // replace html template content with values from google sheet list and sent out the personalized content to each recipient
  data.forEach(function (row) {
    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();
  // this is where i am struggling to include an IF-statement,
  // so that A) only recipient with status "EMAIL NOT SENT YET" in column "K" get an email,
  // and B) the status of anybody who has already received an email is marked "EMAIL SENT" in column "K".
    GmailApp.sendEmail(row[emailAdresse],
      betreff,
      "Dies ist eine Nachricht im HTML-Format. Sie müßen Ihre Email-Software entsprechend einrichten.",
      { htmlBody: htmlMessage, replyTo: "[email protected]" }
    );
  });
}

CodePudding user response：

function JustSendEmail() {
  // variables to reference the sheet and its content
  // you can get all of these with three lines of code one to get the header array flattened and one to create an object and a forEach to populate  the object with `obj[h] = i`
  const anrede = 2;
  const nachname = 3;
  const emailAdresse = 5;
  const terminTag = 6;
  const terminUhrzeit = 8;
  const terminURL = 9;
  let emailTemp = HtmlService.createTemplateFromFile('emailEinladung');
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("terminBestaetigen");
  const sr = 3;//start row of data
  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, "Dies ist eine Nachricht im HTML-Format. Sie müßen Ihre Email-Software entsprechend einrichten.", { htmlBody: htmlMessage, replyTo: "[email protected]" });
      sh.getRange(i   sr, 11).setValue("EMAIL SENT");//stops emails from being sent again
    }
  });
}