应用程序脚本邮件合并：将值设置为 "EMAIL NOT SENT YET" 到 "EMAIL SENT"

Question

问题：

我的邮件合并脚本使用html模板和收件人列表发送电子邮件。收件人列表由16列组成(第一列为A，最后一列为P)。一旦发送了电子邮件，第11列(因此，列"K")中的值就应该从"K"变为"K">电子邮件未发送"；至">电子邮件发送"；每个收件人。并且任何已经具有"；电子邮件发送"；在第11列中(因此，列"K")不应接收另一封电子邮件。

尝试的解决方案：

邮件合并的脚本如下。正如你肯定会意识到的那样，我陷入了代码的最后一部分，我根本不知道如何包含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]" }
);
});
}

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