如何让Google Sheets QUERY函数的数据源范围动态化？

这个问题我之前帮朋友处理过！核心原因很简单：你的getSheetNames()返回的是文本字符串（比如{'sheet 1'!$A:$C; 'sheet 2'!$A:$C}），但QUERY函数需要的是实际的单元格数据数组/联合区域引用，而不是描述这个区域的文字。直接写{'sheet 1'!$A:$C; ...}时，Google Sheets会自动解析成联合数据区域，但用函数返回这个字符串，它就只是一串普通文本，QUERY自然无法识别，所以才会报#VALUE!错误。

下面给你两种可行的解决方案：

方案一：修改自定义函数，直接返回合并后的数据源数组（推荐） #

不用再返回公式字符串，让函数直接把所有目标工作表的A:C数据合并成一个二维数组，QUERY可以直接用这个数组作为数据源，彻底不用手动更新公式。

修改后的脚本如下：

function getAllSheetData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  let combinedData = [];

  for (const sheet of sheets) {
    // 跳过名为"summary"的工作表，避免重复统计
    if (sheet.getName().toLowerCase() === "summary") continue;
    
    // 这里可以根据需求调整：
    // 1. 如果每个工作表都有表头，要跳过第一行的话，用下面这行代替getRange("A:C")
    // const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3);
    // 2. 如果要取所有有数据的行（不限定A:C），可以用sheet.getDataRange()
    const dataRange = sheet.getRange("A:C");
    const sheetData = dataRange.getValues();
    
    // 把当前工作表的数据合并到总数组中
    combinedData = combinedData.concat(sheetData);
  }

  // 返回合并后的二维数组，供QUERY调用
  return combinedData;
}

使用方法：
在单元格中直接调用：

=QUERY(getAllSheetData(), "SELECT SUM(Col2) WHERE Col1 IS NOT NULL", 1)

• 最后一个参数1表示数据源有1行表头，如果你的数据没有表头，改成0即可；
• 增删工作表后，只需要刷新一下公式（比如双击单元格按回车，或者刷新页面），数据就会自动更新。

方案二：保留字符串生成逻辑，用脚本自动设置QUERY公式 #

如果你还是想保留生成联合区域字符串的逻辑，可以让脚本直接把完整的QUERY公式写入单元格，而不是返回字符串让手动调用。

示例脚本：

function updateQueryFormula() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName("summary"); // 要写入公式的工作表
  const targetCell = targetSheet.getRange("A1"); // 公式要放的单元格
  const columns = "!$A:$C";
  let rangeStr = "";

  const sheets = ss.getSheets();
  for (let i = 0; i < sheets.length; i++) {
    const sheet = sheets[i];
    if (sheet.getName().toLowerCase() === "summary") continue;
    rangeStr += `'${sheet.getName()}'${columns}; `;
  }
  // 构建完整的QUERY公式
  const queryFormula = `=QUERY({${rangeStr.slice(0, -2)}}, "SELECT SUM(Col2) WHERE Col1 IS NOT NULL", 1)`;
  
  // 把公式写入目标单元格
  targetCell.setFormula(queryFormula);
}

使用方法：

1. 运行一次updateQueryFormula()函数（第一次需要授权）；
2. 以后增删工作表后，只需要重新运行这个函数，公式就会自动更新；
3. 还可以设置时间驱动触发器，让脚本定期自动更新，彻底解放双手。

注意事项 #

• 自定义函数第一次运行时，需要点击授权提示，允许脚本访问你的表格数据；
• 如果工作表名称包含特殊字符（比如空格、感叹号），脚本里的单引号包裹已经处理了这个问题，不用担心；
• 方案一中，如果数据量很大，getRange("A:C")可能会取到很多空行，建议改成sheet.getRange(1,1,sheet.getLastRow(),3)，只取有数据的行，提升性能。

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