Google Apps Script中QUERY函数[]转义及多工作表数据合并

原代码的核心问题 #

你写的代码里直接在Apps Script中调用QUERY({sheets[i]!A2:Z})是错误的——QUERY是Google Sheets的内置工作表函数，不能直接在Apps Script中这样执行，而且动态引用工作表名称时需要处理转义（比如名称含空格/特殊字符时要用单引号包裹）。

方案1：动态构建QUERY拼接公式 #

如果偏好使用工作表函数实现，可以通过Apps Script生成拼接后的QUERY公式，自动遍历所有目标工作表：

function listAllProductsWithFormula() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheets()[0]; // 第一个表作为合并目标
  const sourceSheets = ss.getSheets().slice(2); // 跳过前两个表（对应你原代码的i>1）

  // 构建每个表的QUERY片段
  const queryParts = sourceSheets.map(sheet => {
    // 处理工作表名称的转义：含特殊字符/空格时用单引号包裹
    const sheetName = sheet.getName().includes(' ') || sheet.getName().includes("'") 
      ? `'${sheet.getName().replace(/'/g, "''")}'` 
      : sheet.getName();
    return `QUERY(${sheetName}!A2:Z, "select * where Col1 is not null", 0)`;
  });

  // 拼接成最终的公式：用{}合并所有结果，再统一处理表头（如果需要去重表头）
  const finalFormula = `={${targetSheet.getName()}!A1:Z1; ${queryParts.join('; ')}}`;

  // 清空目标表原有内容，写入公式
  targetSheet.clearContents();
  targetSheet.getRange(1, 1).setFormula(finalFormula);
}

关键细节 #

• 工作表名称转义：如果名称包含空格、单引号等特殊字符，必须用单引号包裹，且单引号本身要转义为两个单引号（replace(/'/g, "''")）
• 公式结构：用{}纵向合并所有表的数据，先保留目标表的表头，再拼接各表的查询结果（0表示QUERY不返回表头）

方案2：用Apps Script直接读写数据（更适合大量工作表） #

如果工作表数量多、数据量大，直接用Apps Script读取所有数据再写入目标表，效率比公式拼接更高（避免公式重复计算的性能问题）：

function listAllProductsWithDirectRead() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheets()[0];
  const sourceSheets = ss.getSheets().slice(2); // 跳过前两个表

  // 收集所有数据：先保留目标表的表头
  const allData = [targetSheet.getRange(1, 1, 1, 26).getValues()[0]];

  sourceSheets.forEach(sheet => {
    // 获取当前表的有效数据范围（避免读取空行）
    const dataRange = sheet.getDataRange();
    const values = dataRange.getValues();
    // 跳过表头，过滤Col1不为空的行
    const filteredRows = values.slice(1).filter(row => row[0] !== '' && row[0] !== null);
    allData.push(...filteredRows);
  });

  // 清空目标表，写入所有数据
  targetSheet.clearContents();
  if (allData.length > 0) {
    targetSheet.getRange(1, 1, allData.length, allData[0].length).setValues(allData);
  }
}

优势 #

• 性能更好：一次性读取和写入数据，避免公式的实时计算开销
• 更灵活：可以自定义过滤规则、数据处理逻辑（比如修改列值、添加来源表标识等）
• 无公式依赖：合并后的数据是静态的，不会因为源表修改自动更新（如果需要自动更新，可以添加触发器）

额外优化建议 #

• 如果需要自动更新合并结果，可以给函数添加时间驱动触发器（比如每天更新一次），或者绑定到菜单按钮手动触发
• 若源表结构不一致，可以在读取数据时统一列数（比如补全空值到26列）
• 对于超大量数据（十万行以上），可以考虑分批次写入，避免内存溢出

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