Google Sheets的Apps Script触发器无法在公式更新单元格时自动触发怎么办

问题根因 #

你当前配置的On edit/On change触发器仅能响应人为手动修改表格内容的操作，公式自动计算更新的单元格值不会触发这两类事件，所以你的代码在第一列值被公式自动改成3的时候不会运行。

解决方案 #

改用时间驱动触发器定期巡检表格，同时新增邮件发送标记列避免重复发邮件，具体操作如下：

1. 先在你的SUBSCRIPTIONS表新增1列（比如第15列/O列），用于记录邮件发送状态，空值代表未发送，发过的邮件会自动标记为「已发送」
2. 替换原有代码为以下版本：

function checkExpiringSubscriptions() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SUBSCRIPTIONS');
  const allRows = sheet.getDataRange().getValues();
  // 如果你的表格没有表头，把下方i的初始值改成0即可
  for (let i = 1; i < allRows.length; i++) {
    const row = allRows[i];
    // 第一列值为3且未发送过邮件才触发逻辑
    if (row[0] == 3 && row[14] !== '已发送') {
      let mis = row[1];
      let dat = new Date(row[2]).toLocaleDateString("en-US");
      let cos = row[3];
      let aut = row[4];
      let typ = row[5];
      let des = row[6];
      let pro = row[7];
      let not = row[8];
      let cnu = row[9];
      let cna = row[10];
      let sta = row[13];
      let now = new Date().toLocaleString("en-US");

      let msg = ":: AUTOMATED EMAIL FROM - NP PROJECTS SHEET 2021 / SUBSCRIPIONS PAGE :: " + "\n" + "\n" + sta + " SUBSCRIPTION FOR: " + "\n" + dat + " (" + mis + " days missing)"  + "\n" + "Cost: " + cos + "$" + "\n" + "Is autopay active: " + aut + "\n" + "\n" +"CLIENT: " + cna + " (" + cnu + ")" + "\n" + "\n" + "Product: " + typ + "\n" + "\n"+"Descprition: " + des  + "\n" + "\n" + "Provider: " + pro  + "\n" + "\n"+ "Notes: " + not  + "\n" + "\n"+ "\n" + "(This email was triggered at:" +  now + ")";
      
      GmailApp.sendEmail("myemail@gmail.com", "IMPENDING SUBSCRIPTION (automail)", msg);
      // 标记已发送，避免后续重复发邮件
      sheet.getRange(i+1, 15).setValue('已发送');
    }
  }
}

触发器配置步骤 #

1. 打开Apps Script编辑器，点击左侧「触发器」按钮（闹钟图标）
2. 点击右下角「添加触发器」，按以下参数配置：
   • 选择要运行的函数：checkExpiringSubscriptions
   • 选择部署版本：Head
   • 选择事件源：时间驱动
   • 选择类型：日计时器（如果更新频率要求高也可以选每小时触发一次）
   • 选择时间：你想要每天巡检的时间段
3. 保存后按提示完成权限授权即可。

内容的提问来源于stack exchange，提问作者Nova Prospect