如何在两个不同Google Spreadsheet间同时复制单元格值与公式

问题根因梳理 #

• 跨表直接调用copyTo的报错原因：原生copyTo方法仅支持同一电子表格文件内部的范围复制，跨独立文件调用时，传入的目标范围不属于源表的上下文范围，会直接抛出范围不匹配错误，典型错误写法如下：
  SpreadsheetA.getRange("A:E").copyTo(SpreadsheetB.getRange("A:E"), {contentsOnly:true});
• 拆分执行setValues+setFormulas的冲突原因：两个方法执行时都会全量覆盖目标区域对应范围的内容，先后执行必然冲掉前一步写入的内容，无法实现同一单元格按类型匹配保留静态值/计算公式的需求

核心实现逻辑 #

• 分别读取源范围的静态值二维数组、公式二维数组
• 逐单元格做内容匹配：如果源单元格存在非空公式，就取公式内容写入；如果没有公式，就取对应的静态值写入，拼接成完整的待写入数据集
• 一次性将拼接好的数据集写入目标范围，全程仅执行一次写入操作，从根源避免多次写入带来的覆盖问题

自定义函数代码 #

function CopyData(SourceSpreadsheet, DestinationSpreadsheet, SourceRange, DestinationRange) {
  // 初始化源、目标范围对象
  const sourceRangeObj = SourceSpreadsheet.getRange(SourceRange);
  const destRangeObj = DestinationSpreadsheet.getRange(DestinationRange);

  // 校验源与目标范围的行列尺寸是否匹配
  if (
    sourceRangeObj.getNumRows() !== destRangeObj.getNumRows() ||
    sourceRangeObj.getNumColumns() !== destRangeObj.getNumColumns()
  ) {
    throw new Error("源范围与目标范围的行列数不匹配，请核对入参");
  }

  // 分别拉取源区域的静态值、公式数据
  const sourceValues = sourceRangeObj.getValues();
  const sourceFormulas = sourceRangeObj.getFormulas();

  // 逐单元格合并数据集：优先保留公式，无公式则保留静态值
  const writeDataSet = sourceValues.map((row, rowIndex) => {
    return row.map((cellStaticValue, colIndex) => {
      const cellFormula = sourceFormulas[rowIndex][colIndex];
      return cellFormula ? cellFormula : cellStaticValue;
    })
  });

  // 一次性写入目标范围
  destRangeObj.setValues(writeDataSet);
}

注意事项 #

• 公式写入目标表后，会自动遵循目标表的引用上下文，如果需要保留对原源表的跨表引用，可在公式拼接阶段补充源表的唯一标识做引用适配
• 上述实现仅同步静态值与公式内容，如果需要同步单元格格式（如字体、背景色、数据校验规则），可在读取阶段额外拉取对应格式属性，匹配写入目标范围

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