如何让Google Sheets自定义函数getStartTime在参数编辑后自动重算？

问题根源 #

1. 遍历效率低下：原代码遍历整个数据范围的所有单元格，数据量大时易超时，且clearContent()+setFormula()的组合受Google Sheets缓存机制影响，常导致重算失效。
2. 触发器类型限制：若使用简单onEdit触发器，会受权限、执行时间等限制，仅偶尔生效。
3. 自定义函数依赖未声明：如果getStartTime未将依赖的数据源单元格作为参数传入，Google Sheets无法自动感知数据变化，必须强制触发重算。

修复方案 #

1. 替换触发器代码 #

使用更高效的单元格定位方式和可靠的重算逻辑：

function onEditTrigger(e) {
  const sheet = e.range.getSheet();
  
  // 精准定位所有包含getStartTime的公式单元格
  const formulaCells = sheet.createTextFinder('getStartTime')
    .matchFormulaText(true)
    .findAll();
  
  if (formulaCells.length === 0) return;
  
  // 强制触发重算
  formulaCells.forEach(cell => {
    const originalFormula = cell.getFormula();
    cell.setFormula('=NOW()');
    SpreadsheetApp.flush(); // 立即执行操作，避免缓存
    cell.setFormula(originalFormula);
  });
}

2. 安装可编辑触发器 #

必须将函数设置为可安装触发器，避免简单触发器的限制：

• 打开Google Sheets的脚本编辑器（顶部菜单栏「工具」→「脚本编辑器」）
• 点击左侧闹钟图标进入触发器管理页面
• 点击「添加触发器」：
  • 选择函数：onEditTrigger
  • 部署类型：「从电子表格」
  • 事件类型：「编辑时」
  • 完成授权并保存

3. 优化自定义函数（可选） #

若getStartTime是内部读取数据源而非通过参数传入，修改函数将数据源范围作为参数，让Google Sheets自动感知变化：
修改后的函数示例：

function getStartTime(employeeName, date, dataRange) {
  const data = dataRange.getValues();
  // 原有的查找逻辑保持不变
  // ...
}

使用时公式示例：

=getStartTime(A2, B2, $A$1:$Z$1000)

（可根据实际数据范围调整，或用INDIRECT实现动态范围）

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