Google Sheets API多工作表数据复制到另一独立工作表实现方案

之前方法失效的核心原因 #

• 原生spreadsheets.sheets.copyTo接口的默认逻辑就是在目标表格生成带COPY OF 前缀的全新工作表，不支持直接写入目标表格已存在的工作表，也没有参数可以关闭自动加前缀的逻辑
• spreadsheets.sheets.copyPaste接口仅支持同一个电子表格内的跨工作表范围复制，跨电子表格场景下调用会直接报范围不存在的错误，本身就不支持跨文件操作

可落地的接口调用流程 #

按以下步骤调用接口即可实现保留目标表结构、无自动前缀的全量复制：

• 前置元数据拉取：调用spreadsheets.get分别获取源表格、目标表格的元数据，建立源端4个sheet（sheetId、名称、已使用最大行列数）和目标端4个对应sheet（sheetId、名称）的映射关系，避免写入错位
• 源表全量数据拉取：针对每个源sheet，调用spreadsheets.get拉取全量内容，请求参数中指定fields为sheets.properties,sheets.data(rowData.values(effectiveValue,effectiveFormat,hyperlink,note)),sheets.merges,sheets.conditionalFormats,sheets.protectedRanges,sheets.bandedRanges，只拉取复制需要的字段，减少请求体积
• 目标表存量清空：针对每个目标sheet，调用spreadsheets.batchUpdate，通过UpdateCellsRequest清空对应sheet从A1开始的全量单元格内容与格式，避免旧数据残留
• 批量写入目标表：将源表拉取到的所有内容，统一通过一次spreadsheets.batchUpdate请求写入对应目标sheet，所有涉及sheetId的配置项全部替换为目标sheet的对应ID：
  • 单元格值、单元格格式通过UpdateCellsRequest写入，起始位置指定为第0行第0列（即A1单元格）
  • 合并单元格规则通过MergeCellsRequest写入
  • 条件格式、保护范围、交替行样式等配置，分别使用对应类型的request写入即可
• 一致性校验（可选）：写入完成后拉取目标sheet的已使用行列数、抽样单元格值，和源表做比对确认复制完整性

核心请求结构示例 #

{
  "spreadsheetId": "填入目标电子表格的ID",
  "resource": {
    "requests": [
      // 清空目标sheet现有内容
      {
        "updateCells": {
          "range": {
            "sheetId": 填入目标侧对应工作表的sheetId,
            "startRowIndex": 0,
            "startColumnIndex": 0
          },
          "fields": "*"
        }
      },
      // 写入源表全量单元格数据与格式
      {
        "updateCells": {
          "rows": "填入从源表拉取到的rowData数组",
          "range": {
            "sheetId": 填入目标侧对应工作表的sheetId,
            "startRowIndex": 0,
            "startColumnIndex": 0
          },
          "fields": "*"
        }
      },
      // 写入合并单元格规则
      {
        "mergeCells": {
          "range": {
            "sheetId": 填入目标侧对应工作表的sheetId,
            "startRowIndex": 合并范围起始行,
            "endRowIndex": 合并范围结束行,
            "startColumnIndex": 合并范围起始列,
            "endColumnIndex": 合并范围结束列
          },
          "mergeType": "MERGE_ALL"
        }
      }
      // 条件格式、保护范围等其他配置按对应接口结构追加到requests数组即可
    ]
  }
}

大数据量场景替代方案 #

如果单表数据量超过10万行，拉取全量rowData容易触发请求超时，可以用copyTo接口做曲线实现：

1. 先调用spreadsheets.sheets.copyTo将源sheet复制到目标表格，此时生成的新sheet会自动带COPY OF 前缀
2. 调用spreadsheets.batchUpdate，通过UpdateSheetPropertiesRequest将新生成sheet的名称修改为目标要求的正式名称
3. 删除目标表格中原有的同名旧sheet即可

注意这个方案会替换掉目标侧原有的工作表，如果你需要保留目标侧原有sheet的部分配置，不要用这个方案。

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