如何修改AppScript实现Velocity Report页签M列BQ数据公式自动刷新

以下是适配需求的修改后的AppScript代码，实现仅在"Velocity Report"页签被编辑时，自动刷新该页签M列所有关联BigQuery的数据源公式：

function onEdit(e) {
  // 仅响应"Velocity Report"工作表的编辑操作
  const targetSheet = e.source.getActiveSheet();
  if (targetSheet.getName() !== "Velocity Report") return;

  // 启用数据源执行权限
  SpreadsheetApp.enableAllDataSourcesExecution();

  // 获取M列中包含数据的有效范围（自动适配最后一行有内容的单元格）
  const mColumnRange = targetSheet.getRange("M1:M" + targetSheet.getLastRow());
  const dataSourceFormulas = mColumnRange.getDataSourceFormulas();

  // 遍历M列所有单元格，刷新存在数据源公式的单元格
  dataSourceFormulas.forEach((formulaRow, rowIndex) => {
    formulaRow.forEach((formula, colIndex) => {
      if (formula) {
        mColumnRange.getCell(rowIndex + 1, colIndex + 1).refreshData();
      }
    });
  });
}

关键修改说明 #

• 触发范围限制：通过事件对象e获取当前编辑的工作表，对比名称后仅在目标页签执行后续逻辑，避免无关编辑触发刷新。
• 批量刷新M列：不再局限于单个M5单元格，而是动态获取M列的有效数据范围，遍历所有包含BigQuery数据源公式的单元格并逐一刷新。
• 高效遍历逻辑：先获取所有单元格的数据源公式集合，仅对存在公式的单元格执行刷新操作，减少不必要的执行开销。

注意事项 #

• 上述代码使用简单触发器onEdit，会自动响应工作表的编辑操作，无需手动绑定。若遇到权限相关问题（比如复杂数据源权限），可改为创建安装式触发器：
  1. 打开脚本编辑器，点击顶部菜单「编辑」→「当前项目的触发器」
  2. 添加新触发器，选择onEdit函数，触发事件选择「从电子表格提交」→「编辑」
• 如果M列的公式仅集中在特定行（比如M5到M200），可将getRange("M1:M" + targetSheet.getLastRow())改为getRange("M5:M200")，进一步缩小处理范围提升效率。

内容的提问来源于stack exchange，提问作者J.Betts