Google Apps Script如何基于表格不同列指定日期发送多封HTML邮件

前置准备 #

• 先在你的「LIST_OF_RECIPIENTS」表格新增3列，作为后续3封邮件的发送状态标识：O列对应第二封邮件状态、P列对应第三封、Q列对应第四封，所有行初始值设为NOT SENT
• 准备好3封后续邮件对应的HTML模板，命名比如HTML_TEMPLATE_2、HTML_TEMPLATE_3、HTML_TEMPLATE_4，如果内容和第一封差异不大，也可以直接在原模板里加类型变量区分内容

修改后的完整代码 #

// 日期格式化工具函数，统一转成dd.MM.yyyy格式匹配你的表格日期规则
function formatDate(date) {
  const day = String(date.getDate()).padStart(2, '0');
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const year = date.getFullYear();
  return `${day}.${month}.${year}`;
}

function sendEmail() {
  // 基础变量定义
  const anrede = 2;
  const nachname = 3;
  const emailAdresse = 5;
  const terminTag = 6;
  const terminUhrzeit = 8;
  const terminURL = 9;
  // 列索引定义（从0开始计数）
  const FIRST_EMAIL_STATUS_COL = 10; // K列 第一封状态
  const SECOND_EMAIL_DATE_COL = 11; // L列 第二封发送日期
  const THIRD_EMAIL_DATE_COL = 12; // M列 第三封发送日期
  const FOURTH_EMAIL_DATE_COL = 13; // N列 第四封发送日期
  const SECOND_EMAIL_STATUS_COL = 14; // O列 第二封状态
  const THIRD_EMAIL_STATUS_COL = 15; // P列 第三封状态
  const FOURTH_EMAIL_STATUS_COL = 16; // Q列 第四封状态

  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LIST_OF_RECIPIENTS");
  const adminV = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("admin");
  // 如果后续邮件主题不同，可以在admin表新增单元格存储对应主题，这里示例第一封主题取A20，第二封A21，第三封A22，第四封A23
  const betreffList = [
    adminV.getRange("A20").getValue(),
    adminV.getRange("A21").getValue(),
    adminV.getRange("A22").getValue(),
    adminV.getRange("A23").getValue()
  ];
  const aliases = GmailApp.getAliases();
  const sr = 3; // 数据起始行
  // 注意这里取列数调整为17列，覆盖到新增的状态列
  const data = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, 17).getValues();
  const todayStr = formatDate(new Date()); // 获取今天的格式化日期

  data.forEach((row, i) => {
    const currentRow = i + sr;
    // 发送第一封邮件的原有逻辑
    if (row[FIRST_EMAIL_STATUS_COL] == "EMAIL NOT SENT YET") {
      let emailTemp = HtmlService.createTemplateFromFile('HTML_TEMPLATE');
      emailTemp.anrede1 = row[anrede];
      emailTemp.nachname1 = row[nachname];
      emailTemp.emailAdresse1 = row[emailAdresse];
      emailTemp.terminTag1 = row[terminTag];
      emailTemp.terminUhrzeit1 = row[terminUhrzeit];
      emailTemp.terminURL1 = row[terminURL];
      const htmlMessage = emailTemp.evaluate().getContent();
      GmailApp.sendEmail(row[emailAdresse],
        betreffList[0], 
        "This is an html email. Please change your setting to be able to read it.",
        { from: aliases[0], htmlBody: htmlMessage, replyTo: aliases[0] }
      );
      sh.getRange(currentRow, FIRST_EMAIL_STATUS_COL + 1).setValue("EMAIL SENT");
    }

    // 第二封邮件发送逻辑：日期匹配且未发送
    if (row[SECOND_EMAIL_STATUS_COL] == "NOT SENT" && formatDate(row[SECOND_EMAIL_DATE_COL]) === todayStr) {
      let emailTemp = HtmlService.createTemplateFromFile('HTML_TEMPLATE_2');
      // 按需给模板变量赋值，和第一封逻辑一致
      emailTemp.anrede1 = row[anrede];
      emailTemp.nachname1 = row[nachname];
      const htmlMessage = emailTemp.evaluate().getContent();
      GmailApp.sendEmail(row[emailAdresse],
        betreffList[1], 
        "This is an html email. Please change your setting to be able to read it.",
        { from: aliases[0], htmlBody: htmlMessage, replyTo: aliases[0] }
      );
      sh.getRange(currentRow, SECOND_EMAIL_STATUS_COL + 1).setValue("EMAIL SENT");
    }

    // 第三封邮件发送逻辑
    if (row[THIRD_EMAIL_STATUS_COL] == "NOT SENT" && formatDate(row[THIRD_EMAIL_DATE_COL]) === todayStr) {
      let emailTemp = HtmlService.createTemplateFromFile('HTML_TEMPLATE_3');
      emailTemp.anrede1 = row[anrede];
      emailTemp.nachname1 = row[nachname];
      const htmlMessage = emailTemp.evaluate().getContent();
      GmailApp.sendEmail(row[emailAdresse],
        betreffList[2], 
        "This is an html email. Please change your setting to be able to read it.",
        { from: aliases[0], htmlBody: htmlMessage, replyTo: aliases[0] }
      );
      sh.getRange(currentRow, THIRD_EMAIL_STATUS_COL + 1).setValue("EMAIL SENT");
    }

    // 第四封邮件发送逻辑
    if (row[FOURTH_EMAIL_STATUS_COL] == "NOT SENT" && formatDate(row[FOURTH_EMAIL_DATE_COL]) === todayStr) {
      let emailTemp = HtmlService.createTemplateFromFile('HTML_TEMPLATE_4');
      emailTemp.anrede1 = row[anrede];
      emailTemp.nachname1 = row[nachname];
      const htmlMessage = emailTemp.evaluate().getContent();
      GmailApp.sendEmail(row[emailAdresse],
        betreffList[3], 
        "This is an html email. Please change your setting to be able to read it.",
        { from: aliases[0], htmlBody: htmlMessage, replyTo: aliases[0] }
      );
      sh.getRange(currentRow, FOURTH_EMAIL_STATUS_COL + 1).setValue("EMAIL SENT");
    }
  });
}

最后配置 #

你需要在谷歌应用脚本的触发器页面，给sendEmail函数添加一个每日定时触发器，设置在你想要发件的时间段触发即可，脚本会每天自动检查日期，到了指定日期就自动发送对应邮件，且不会重复发送。

内容的提问来源于stack exchange，提问作者Bob Hardball