Google Sheets自定义函数与QUERY在工作表重命名后不更新问题

方法一：使用内置函数替代自定义函数（推荐） #

自定义函数存在缓存机制，复制工作表后不会自动重新计算。改用内置函数组合获取工作表名，无需脚本即可自动更新：

将原公式替换为：

=QUERY('raw export'!A:C, "SELECT * WHERE Col1 = '"&REGEXEXTRACT(CELL("filename"), "'(.*)'")&"'", 1)

原理说明： #

• CELL("filename") 返回当前工作表的完整路径（格式为[表格名] 工作表名）
• REGEXEXTRACT(..., "'(.*)'") 提取单引号包裹的工作表名称，确保匹配客户名称
• 复制工作表并重命名后，CELL 函数会自动更新值，QUERY 随之提取对应客户的数据

方法二：修改自定义函数强制更新 #

若必须保留自定义函数，可通过添加易变参数打破缓存：

1. 修改自定义函数代码：

function sheetName(trigger) {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

2. 更新工作表中的公式：

=QUERY('raw export'!A:C, "SELECT * WHERE Col1 = '"&sheetName(NOW())&"'", 1)

原理说明： #

• NOW() 是易变函数，每秒自动更新，会强制触发自定义函数重新计算
• 复制工作表并重命名后，新工作表的sheetName会读取当前工作表名称，自动匹配客户数据

方法三：脚本自动处理工作表重命名 #

通过onChange触发器实现自动化，重命名工作表时自动更新公式：

1. 打开Google Sheets的「扩展程序」→「Apps脚本」，粘贴以下代码：

function onSheetRename(e) {
  // 仅处理工作表重命名相关事件
  if (e.changeType === 'OTHER') {
    const activeSheet = e.source.getActiveSheet();
    const sheetName = activeSheet.getName();
    // 跳过原始数据工作表
    if (sheetName !== 'raw export') {
      // 生成对应客户的QUERY公式
      const targetFormula = `=QUERY('raw export'!A:C, "SELECT * WHERE Col1 = '${sheetName}'", 1)`;
      // 将公式写入A1单元格（可根据实际调整位置）
      activeSheet.getRange('A1').setFormula(targetFormula);
    }
  }
}

2. 设置触发器：
   • 在Apps脚本界面点击左侧「触发器」→「添加触发器」
   • 选择函数onSheetRename，事件类型选「从电子表格」→「更改」
   • 保存后，重命名工作表时会自动更新公式

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