google-apps-script - Apps 脚本邮件合并：将值从“EMAIL NOT SENT YET”设置为“EMAIL SENT”

Question

问题：

我的邮件合并脚本使用 html 模板和收件人列表发送电子邮件。收件人列表由 16 列组成（第一列是 A，最后一列是 P）。发送电子邮件后，第 11 列（即“K”列）中的值应从每个收件人的“ EMAIL NOT SENT YET ”更改为“ EMAIL SENT ”。并且在第 11 列（因此，“K”列）中已经有“EMAIL SENT”的任何收件人都不应再收到一封电子邮件。

尝试的解决方案：

邮件合并的脚本如下。正如你肯定意识到的那样，我被困在代码的最后一部分，我根本不知道如何包含一个 IF 语句来完成工作。

问题：

我应该更改脚本的哪一部分以确保A)为第 11 列中的每个收件人自动更新状态（因此，列“K）和B）已经收到电子邮件的人没有收到另一封邮件？

非常感谢您的帮助！

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: "[e-mail]" }
    );
  });
}

标签： google-apps-script

Answer 1

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: "[e-mail]" });
      sh.getRange(i + sr, 11).setValue("EMAIL SENT");//stops emails from being sent again
    }
  });
}