Google App Script：数组公式更新I列时触发函数失败排查

核心原因 #

onEdit和onChange触发器仅对用户手动执行的编辑操作生效，通过ArrayFormula等程序化方式自动更新的单元格内容，不属于用户交互操作范畴，因此不会触发这两个触发器。

解决方案 #

以下提供两种可行的实现方式，可根据实际场景选择：

方案1：时间驱动触发器（通用型）

通过定期扫描I列，检测新增数据并触发目标函数，适配所有ArrayFormula更新场景：

// 执行一次该函数，安装时间驱动触发器
function installTimeTrigger() {
  ScriptApp.newTrigger("checkIColumnChanges")
    .timeBased()
    .everyMinutes(5) // 可按需调整间隔，比如1分钟
    .create();
  Logger.log("时间驱动触发器已安装");
}

function checkIColumnChanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Main");
  const lastRow = sheet.getLastRow();
  const props = PropertiesService.getScriptProperties();
  // 获取上次检查记录的最后行号，默认从第1行开始
  const lastCheckedRow = parseInt(props.getProperty("lastCheckedRow")) || 1;

  // 检查是否有新增行
  if (lastRow > lastCheckedRow) {
    // 遍历所有新增行，验证I列是否有内容
    for (let row = lastCheckedRow + 1; row <= lastRow; row++) {
      const cellValue = sheet.getRange(row, 9).getValue();
      if (cellValue !== "") {
        // 触发目标函数，可修改为传递row参数实现单行处理
        fillTemplateAndDuplicate();
      }
    }
    // 更新记录的最后检查行号
    props.setProperty("lastCheckedRow", lastRow.toString());
  }
}

// 可选：重置检查记录的行号（如需重新扫描所有行时执行）
function resetLastCheckedRow() {
  const props = PropertiesService.getScriptProperties();
  props.setProperty("lastCheckedRow", "1");
  Logger.log("已重置最后检查行");
}

注意事项：

• 避免重复安装触发器，可在脚本编辑器的「编辑」→「当前项目的触发器」中查看/管理已安装的触发器
• 若fillTemplateAndDuplicate需要针对单行处理，可修改函数接收row参数，避免重复执行全量逻辑

方案2：监听行插入事件（针对性）

如果ArrayFormula仅在新增行时自动填充I列，可通过onChange触发器监听行插入事件：

// 执行一次该函数，安装onChange触发器
function installOnChangeTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger("handleRowInsert")
    .forSpreadsheet(ss)
    .onChange()
    .create();
  Logger.log("onChange触发器已安装");
}

function handleRowInsert(e) {
  // 仅处理行插入事件
  if (e.changeType !== "INSERT_ROW") return;
  
  const sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() !== "Main") return; // 仅处理目标工作表

  const props = PropertiesService.getScriptProperties();
  const lastRowBefore = parseInt(props.getProperty("lastRowBefore")) || sheet.getLastRow() - 1;
  const currentLastRow = sheet.getLastRow();

  // 验证是否为新增行，并检查I列内容
  if (currentLastRow > lastRowBefore) {
    const insertedRow = currentLastRow;
    const cellValue = sheet.getRange(insertedRow, 9).getValue();
    if (cellValue !== "") {
      fillTemplateAndDuplicate();
    }
  }
  // 更新记录的最后行号
  props.setProperty("lastRowBefore", currentLastRow.toString());
}

优缺点：

• 优点：延迟低，仅在行插入时触发
• 缺点：仅对主动插入行的场景生效，若ArrayFormula是在已有空行中填充内容，无法触发

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