Google Apps Script：新增下拉状态定时跟进与到期提醒邮件功能

现有功能 #

当前脚本实现：当"Individual Licenses"工作表第6列（状态列）的值设为Request Sent时，自动向指定收件人发送定制化邮件。

新增需求 #

• 跟进提醒：当某行状态设为Request Sent后，若7天内状态未变更，自动发送跟进提醒邮件
• 到期提醒：针对表格H列（到期日），自动在到期前3天发送提醒邮件

现有代码 #

function checkMySheet(e) {
  let range = e.range;
  let source = e.source.getActiveSheet();
  let row = range.getRow();
  let col = range.getColumn();
  let val = range.getValue();

  if (source.getName() == "Individual Licenses" && col == 6 && val == 'Request Sent') {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName(source.getName());
    let data = sheet.getRange(row, 1, 1, 12).getValues().flat();
   
      MailApp.sendEmail({
        to: data[4],
        subject: `ACTION REQUIRED: License Item Needed for ${data[0]}`,
        htmlBody: `
       Hey ${data[3]},<br />
       <br />
       Please see the below details for an individual license item that requires your attention so we can clear this with the regulator. It is important that you please review this as soon as possible and let me know if you have any questions on what may be needed to address and clear the item.<br />
      <br />
       <strong>STATE</strong>: ${data[0]}<br />
       <strong>LICENSE ITEM</strong>: ${data[1]}<br />
       <strong>PRIORITY</strong>: ${data[2]}<br />
       <strong>ASSIGNED TO</strong>: ${data[3]}<br />
       <strong>DUE DATE</strong>: ${Utilities.formatDate(new Date(data[7]), ss.getSpreadsheetTimeZone(),"M/d/yy")}<br />
       <strong>NOTES</strong>: ${data[11]}<br />
       <br />
       If you need any help or additional clarification then please don't hesitate to reach out and let me know. Also please keep me updated on the status and your expected time to have this completed.<br />
       <br />
       Thanks!<br />
       <br />
       `
      })

  ss.toast("Email successfully sent!", "STATUS", 5);
  }
}

表格结构示例 #

修改后的完整代码 #

// 原有功能：状态设为Request Sent时发送初始邮件
function checkMySheet(e) {
  let range = e.range;
  let source = e.source.getActiveSheet();
  let row = range.getRow();
  let col = range.getColumn();
  let val = range.getValue();

  if (source.getName() == "Individual Licenses" && col == 6 && val == 'Request Sent') {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName(source.getName());
    let data = sheet.getRange(row, 1, 1, 12).getValues().flat();
   
    MailApp.sendEmail({
      to: data[4],
      subject: `ACTION REQUIRED: License Item Needed for ${data[0]}`,
      htmlBody: `
      Hey ${data[3]},<br />
      <br />
      Please see the below details for an individual license item that requires your attention so we can clear this with the regulator. It is important that you please review this as soon as possible and let me know if you have any questions on what may be needed to address and clear the item.<br />
      <br />
      <strong>STATE</strong>: ${data[0]}<br />
      <strong>LICENSE ITEM</strong>: ${data[1]}<br />
      <strong>PRIORITY</strong>: ${data[2]}<br />
      <strong>ASSIGNED TO</strong>: ${data[3]}<br />
      <strong>DUE DATE</strong>: ${Utilities.formatDate(new Date(data[7]), ss.getSpreadsheetTimeZone(),"M/d/yy")}<br />
      <strong>NOTES</strong>: ${data[11]}<br />
      <br />
      If you need any help or additional clarification then please don't hesitate to reach out and let me know. Also please keep me updated on the status and your expected time to have this completed.<br />
      <br />
      Thanks!<br />
      `
    });

    ss.toast("Email successfully sent!", "STATUS", 5);
  }
}

// 新增功能1：发送Request Sent状态7天未更新的跟进提醒
function sendFollowUpReminders() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Individual Licenses");
  const data = sheet.getDataRange().getValues();
  const timeZone = ss.getSpreadsheetTimeZone();
  const today = new Date();
  today.setHours(0, 0, 0, 0); // 重置时间为当天0点，避免时间差影响判断

  // 从第二行开始遍历（跳过表头）
  for (let i = 1; i < data.length; i++) {
    const rowData = data[i];
    const status = rowData[5];
    const requestDate = new Date(rowData[6]);
    const followUpDate = new Date(requestDate);
    followUpDate.setDate(requestDate.getDate() + 7);
    followUpDate.setHours(0, 0, 0, 0);

    // 判断条件：状态仍为Request Sent，且今天正好是请求日期后第7天
    if (status === "Request Sent" && followUpDate.getTime() === today.getTime()) {
      const recipient = rowData[4];
      const state = rowData[0];
      const licenseItem = rowData[1];
      const owner = rowData[3];
      const dueDate = Utilities.formatDate(new Date(rowData[7]), timeZone, "M/d/yy");
      const notes = rowData[11];

      MailApp.sendEmail({
        to: recipient,
        subject: `FOLLOW-UP: License Item for ${state} Still Pending`,
        htmlBody: `
        Hey ${owner},<br />
        <br />
        This is a follow-up regarding the license item below, which was marked as "Request Sent" 7 days ago but hasn't been updated yet. Please review and provide an update on its status at your earliest convenience.<br />
        <br />
        <strong>STATE</strong>: ${state}<br />
        <strong>LICENSE ITEM</strong>: ${licenseItem}<br />
        <strong>DUE DATE</strong>: ${dueDate}<br />
        <strong>NOTES</strong>: ${notes}<br />
        <br />
        Let me know if you need any assistance to move this forward.<br />
        <br />
        Thanks!<br />
        `
      });

      ss.toast(`Follow-up email sent to ${owner} for row ${i+1}`, "STATUS", 5);
    }
  }
}

// 新增功能2：发送到期前3天的提醒邮件
function sendDueDateReminders() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Individual Licenses");
  const data = sheet.getDataRange().getValues();
  const timeZone = ss.getSpreadsheetTimeZone();
  const today = new Date();
  today.setHours(0, 0, 0, 0);

  // 从第二行开始遍历（跳过表头）
  for (let i = 1; i < data.length; i++) {
    const rowData = data[i];
    const status = rowData[5];
    const dueDate = new Date(rowData[7]);
    const reminderDate = new Date(dueDate);
    reminderDate.setDate(dueDate.getDate() - 3);
    reminderDate.setHours(0, 0, 0, 0);

    // 判断条件：状态未完成（非Completed等），且今天正好是到期前3天
    if (status !== "Completed" && reminderDate.getTime() === today.getTime()) {
      const recipient = rowData[4];
      const state = rowData[0];
      const licenseItem = rowData[1];
      const owner = rowData[3];
      const formattedDueDate = Utilities.formatDate(dueDate, timeZone, "M/d/yy");
      const notes = rowData[11];

      MailApp.sendEmail({
        to: recipient,
        subject: `REMINDER: License Item for ${state} Due in 3 Days`,
        htmlBody: `
        Hey ${owner},<br />
        <br />
        This is a reminder that the following license item is due in 3 days. Please ensure it's completed and updated in the spreadsheet by the due date.<br />
        <br />
        <strong>STATE</strong>: ${state}<br />
        <strong>LICENSE ITEM</strong>: ${licenseItem}<br />
        <strong>DUE DATE</strong>: ${formattedDueDate}<br />
        <strong>NOTES</strong>: ${notes}<br />
        <br />
        Reach out if you need help to meet this deadline.<br />
        <br />
        Thanks!<br />
        `
      });

      ss.toast(`Due date reminder sent to ${owner} for row ${i+1}`, "STATUS", 5);
    }
  }
}

配置说明 #

1. 原有函数触发：checkMySheet 依赖表格的onChange触发器，需确保已在脚本编辑器中配置该触发器（选择"从电子表格部署"，事件类型选"更改"）。
2. 新增函数触发：sendFollowUpReminders 和 sendDueDateReminders 需要配置时间驱动触发器，建议设置为每天运行一次（比如每天凌晨），确保每天检查符合条件的行并发送提醒。

内容的提问来源于stack exchange，提问作者Kamron Kimbrough