如何通过Apps Script程序化插入跨工作表单元格引用？是否有更优方案？

我懂这种“明明应该很简单却卡半天”的感觉！结合你用Apps Script的场景，给你几个不用纠结字符串拼接的实用方案，按需选择：

方案1：直接读写数据（推荐，无公式） #

如果你的需求是同步数据而非动态关联（比如定期把data sheets的数据汇总到summary，不用源数据变了自动更新），直接用getValues()和setValues()是最高效的方法——完全不用公式，直接把值写入目标范围：

function syncDataToSummary() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const summarySheet = ss.getSheetByName("summary");
  // 筛选出所有非summary的data sheets
  const dataSheets = ss.getSheets().filter(sheet => sheet.getName() !== "summary");

  // 假设把每个data sheet的A1:B10同步到summary，每个sheet的数据依次往下排列
  let targetStartRow = 1;
  dataSheets.forEach(sheet => {
    // 指定源数据范围
    const sourceRange = sheet.getRange("A1:B10");
    // 读取源范围的值
    const sourceValues = sourceRange.getValues();
    // 计算目标范围的位置和大小
    const targetRange = summarySheet.getRange(
      targetStartRow, 
      1, 
      sourceValues.length, 
      sourceValues[0].length
    );
    // 写入目标范围
    targetRange.setValues(sourceValues);
    // 更新下一个sheet的起始行
    targetStartRow += sourceValues.length;
  });
}

这个方法的优势：

• 性能远优于公式，数据量大时不会拖慢表格
• 逻辑直观，不用处理公式字符串的转义问题
• 数据直接固化在summary里，不用担心源sheet改名导致公式失效

方案2：批量生成动态公式（需要实时同步） #

如果你需要summary的数据随源sheet实时更新，那还是得用公式，但可以让脚本帮你批量生成，不用手动拼接字符串：

function createDynamicReferences() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const summarySheet = ss.getSheetByName("summary");
  const dataSheets = ss.getSheets().filter(sheet => sheet.getName() !== "summary");

  let targetStartRow = 1;
  dataSheets.forEach(sheet => {
    const sheetName = sheet.getName();
    // 要生成公式的目标范围（对应源sheet的A1:B10）
    const targetFormulaRange = summarySheet.getRange(targetStartRow, 1, 10, 2);
    
    // 批量生成公式，自动处理sheet名称的单引号（比如带空格的sheet名）
    const formulas = targetFormulaRange.getValues().map((row, rowIndex) => 
      row.map((_, colIndex) => {
        // 转换为A1表示法，比如第1行第1列就是A1
        const cellA1 = SpreadsheetApp.getRange(rowIndex+1, colIndex+1).getA1Notation();
        return `='${sheetName}'!${cellA1}`;
      })
    );
    
    // 写入公式到目标范围
    targetFormulaRange.setFormulas(formulas);
    targetStartRow += 10;
  });
}

方案3：原生公式简化（不用脚本） #

如果不想写代码，用原生函数也能实现：

• 用QUERY函数批量合并多sheet数据：=QUERY({DataSheet1!A1:B10; DataSheet2!A1:B10}, "select * where Col1 is not null")
• 用INDIRECT动态引用：比如在summary的A1列存sheet名称，B1写=INDIRECT("'"&A1&"'!B2")，就能自动引用对应sheet的B2单元格

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