如何通过Google Sheets API batchUpdate实现跨spreadsheetId复制

原问题说明 #

现有脚本仅支持同一电子表格内的跨工作表数值复制，需改造为可将指定范围数据写入不同的spreadsheetId对应的目标表格，公开可查的copyPaste操作示例均仅支持单表格内调用。

现有可运行脚本（单表格内复制） #

function Test_Copy() {
  const spreadsheetId = "1hLXq9zq-tAaGpPtGiY6859h4oKxIbNimxCHPPZOzdE0";
  const sourceSheetId = "1792457718";
  const targetSheetId = "760382355";

  // 配置源数据范围
  const rangeFrom = Sheets.newGridRange();
  rangeFrom.sheetId = sourceSheetId;
  rangeFrom.startRowIndex = 0;
  rangeFrom.endRowIndex = 23637;  
  rangeFrom.startColumnIndex = 0;
  rangeFrom.endColumnIndex = 23;

  // 配置目标粘贴范围
  const rangeTo = Sheets.newGridRange();
  rangeTo.sheetId = targetSheetId;
  rangeTo.startRowIndex = 0;
  rangeTo.endRowIndex = 23637;  
  rangeTo.startColumnIndex = 0;
  rangeTo.endColumnIndex = 23;  

  // 构建复制粘贴请求
  const copyPasteRequest = Sheets.newCopyPasteRequest();
  copyPasteRequest.destination = rangeTo;    
  copyPasteRequest.source = rangeFrom;
  copyPasteRequest.pasteType = "PASTE_VALUES";
  copyPasteRequest.pasteOrientation = "NORMAL";

  // 发起批量更新请求
  const resource = Sheets.newBatchUpdateSpreadsheetRequest();
  const request = Sheets.newRequest();
  request.copyPaste = copyPasteRequest;
  resource.requests = [request];

  Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
}

操作限制说明 #

CopyPasteRequest是电子表格批量更新接口的子请求类型，必须绑定同一个spreadsheetId的上下文发起调用，天然不支持跨电子表格操作，直接修改spreadsheetId参数会触发无效请求错误。

跨表格复制改造方案 #

要实现不同spreadsheetId间的纯数值复制，拆分两步独立API调用即可实现：

1. 调用values.get接口，从源电子表格的指定范围读取全量单元格值
2. 调用values.update接口，将读取到的值写入目标电子表格的对应范围
   改造后的可运行代码：

function Test_CrossSpreadsheetCopy() {
  // 源表格配置
  const sourceSpreadsheetId = "替换为源电子表格ID";
  // 源范围对应原脚本配置：0-23636行（1基为1-23637行）、0-22列（A-W列）
  const sourceRange = "源工作表名称!A1:W23637";

  // 目标表格配置
  const targetSpreadsheetId = "替换为目标电子表格ID";
  const targetRange = "目标工作表名称!A1:W23637";

  // 读取源表格范围数据，空范围默认返回空数组避免报错
  const sourceData = Sheets.Spreadsheets.Values.get(sourceSpreadsheetId, sourceRange).values || [];

  // 写入目标表格对应范围
  Sheets.Spreadsheets.Values.update(
    {values: sourceData},
    targetSpreadsheetId,
    targetRange,
    {valueInputOption: "USER_ENTERED"}
  );
}

注意事项 #

• 原脚本使用的GridRange为0基索引，转换为A1格式表示法时需注意边界：endRowIndex/endColumnIndex为不包含的终止索引
• 上述方案仅实现纯数值复制，与原脚本设置的PASTE_VALUES行为完全一致；如需保留单元格格式、公式校验规则等内容，需额外调用spreadsheets.get接口读取格式配置后，再通过批量更新接口写入目标表格，复杂度较高
• 写入操作会直接覆盖目标范围内的原有数据，可根据需求调整目标范围的起始位置

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