Google Sheets跨所有标签页指定单元格求和脚本自动更新需求

原sum3D自定义函数仅在手动重新输入公式时才会刷新结果，无法响应工作表数据变化或新增工作表的情况，以下是具体解决办法：

一、修改自定义函数+添加编辑触发器 #

这个方案会在编辑目标范围内的工作表时，自动刷新所有使用sum3D函数的单元格结果。

修改后的完整代码 #

// 自定义求和函数，可指定目标单元格（默认G1）
function sum3D(targetRange = "G1") {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shs = ss.getSheets();
  // 定义求和的工作表范围：从表'b'到表'e'之前的所有工作表
  const startSheetIndex = ss.getSheetByName('b').getIndex();
  const endSheetIndex = ss.getSheetByName('e').getIndex();
  
  let total = 0;
  for (let i = startSheetIndex; i < endSheetIndex; i++) {
    const currentSheet = shs[i];
    try {
      // 累加目标单元格的值，空单元格按0处理
      total += currentSheet.getRange(targetRange).getValue() || 0;
    } catch (err) {
      // 跳过无目标单元格的异常情况
      continue;
    }
  }
  return total;
}

// 编辑触发器：编辑目标范围内的工作表时自动刷新结果
function onEdit(e) {
  const ss = e.source;
  const editedSheet = e.range.getSheet();
  const startIndex = ss.getSheetByName('b').getIndex();
  const endIndex = ss.getSheetByName('e').getIndex();
  
  // 仅当编辑的工作表在求和范围内时执行刷新
  if (editedSheet.getIndex() >= startIndex && editedSheet.getIndex() < endIndex) {
    // 找到所有使用sum3D函数的单元格
    const formulaCells = ss.createTextFinder('=sum3D')
      .matchFormulaText(true)
      .findAll();
    
    // 强制刷新单元格：清空后重新设置公式
    formulaCells.forEach(cell => {
      const originalFormula = cell.getFormula();
      cell.setFormula('');
      cell.setFormula(originalFormula);
    });
  }
}

使用说明 #

1. 将代码替换原脚本后，在工作表中使用公式=sum3D()即可求和所有目标表的G1单元格，也可指定其他单元格如=sum3D("A2")
2. 当你编辑'b'到'e'之间任意工作表的目标单元格时，所有sum3D公式的结果会自动刷新

二、新增工作表时自动刷新（可选） #

如果需要在新增工作表（且该表在'b'到'e'范围内）时自动刷新结果，需添加可安装触发器：

新增触发器代码 #

// 更改触发器：新增工作表时自动刷新sum3D结果
function onChange(e) {
  // 仅在新增工作表时执行
  if (e.changeType === 'INSERT_GRID') {
    const ss = e.source;
    const formulaCells = ss.createTextFinder('=sum3D')
      .matchFormulaText(true)
      .findAll();
    
    formulaCells.forEach(cell => {
      const originalFormula = cell.getFormula();
      cell.setFormula('');
      cell.setFormula(originalFormula);
    });
  }
}

配置可安装触发器步骤 #

1. 打开脚本编辑器，点击顶部菜单「编辑」→「当前项目的触发器」
2. 点击「添加触发器」，按以下设置：
   • 选择函数：onChange
   • 选择部署类型：「Head deployments」
   • 选择事件源：「电子表格」
   • 选择事件类型：「更改」
3. 保存并授权触发器权限

关键优化点 #

• 新增可选参数targetRange，支持自定义求和的目标单元格
• 添加异常处理，避免因工作表无目标单元格导致函数报错
• 通过触发器监听编辑和新增工作表事件，强制刷新公式结果

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