如何定时将BigQuery百万级数据写入Google Sheets？

我在配置Google Cloud Workflow，要每日定时把BigQuery查询结果写入Google Sheets时，遇到错误：

"message": "HTTP response exceeded the limit of 2097152 bytes"

查询结果超过100万行，远超阈值。试过用AppsScript从连接BigQuery的Sheet复制数据，但该连接仅显示5000行预览，方案无效。下游工具仅支持Google Sheets，想知道能不能通过Workflows、Sheets宏或GCP存储桶实现定时写入百万行数据？

已尝试的AppsScript代码：

function Copyall() {
  const source = SpreadsheetApp.openById("my_sheet_1");
  const source_range = source.getRange("Export!A:W");
  const source_data = source_range.getValues();

  const paste_to = SpreadsheetApp.openById("my_sheet_2");
  const paste_range_start = paste_to.getRange("Sheet1!A1");
  const paste_sheet = paste_to.getSheetByName(paste_range_start.getSheet().getName());

  paste_sheet.clear();

  const paste_range = paste_sheet.getRange(
    paste_range_start.getRow(),
    paste_range_start.getColumn(),
    source_data.length,
    source_data[0].length
  );

  paste_range.setValues(source_data);
}

方案1：GCP存储桶中转 + AppsScript分批写入 #

操作步骤： #

1. BigQuery定时导出到GCS：创建BigQuery定时查询任务，将结果以CSV格式导出到Cloud Storage桶（建议按日期命名文件，避免覆盖）。
2. AppsScript分批读取写入：利用AppsScript的Cloud Storage服务，分批读取文件内容，分批次写入Sheets，规避单次数据量限制。

核心代码示例：

function importFromGCS() {
  const bucketName = "你的GCS桶名称";
  const fileName = "bq-export-20240520.csv"; // 替换为实际导出文件名
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("目标Sheet");
  
  // 清空现有数据
  targetSheet.clearContents();
  
  // 获取GCS文件内容
  const file = CloudStorageApp.bucket(bucketName).file(fileName);
  const content = file.getBlob().getDataAsString();
  const rows = content.split("\n").filter(row => row.trim() !== ""); // 过滤空行
  
  // 每批写入10000行（可根据性能调整）
  const batchSize = 10000;
  for (let i = 0; i < rows.length; i += batchSize) {
    const batch = rows.slice(i, i + batchSize).map(row => row.split(","));
    // 写入批次数据
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, batch.length, batch[0].length).setValues(batch);
    // 避免超时，每批后短暂休眠
    Utilities.sleep(1000);
  }
}

注意：需在AppsScript编辑器中启用「Cloud Storage API」服务，并给脚本授权对应GCS桶的读取权限。

3. 设置定时触发：在AppsScript中配置时间驱动触发器，每日自动执行上述导入函数。

方案2：Cloud Workflows分页查询+分批写入Sheets #

操作步骤： #

1. BigQuery查询分页：在Workflows中调用BigQuery的jobs.query接口时，使用pageToken参数分页获取结果，每次获取不超过1000行（控制单批次数据量在2MB以内）。
2. 分批调用Sheets API追加写入：每获取一页结果，调用Sheets的spreadsheets.values.append接口写入，循环处理所有分页数据。

核心Workflow YAML示例：

main:
  params: [input]
  steps:
    - init_config:
        assign:
          - project_id: "你的GCP项目ID"
          - query: "SELECT * FROM 你的数据集.你的表"
          - sheet_id: "目标Sheet的ID"
          - write_range: "Sheet1!A1"
          - page_token: ""
    - run_bq_query:
        call: googleapis.bigquery.v2.jobs.query
        args:
          projectId: ${project_id}
          body:
            query: ${query}
            useLegacySql: false
            pageToken: ${page_token}
        result: query_result
    - write_to_sheets:
        call: googleapis.sheets.v4.spreadsheets.values.append
        args:
          spreadsheetId: ${sheet_id}
          range: ${write_range}
          valueInputOption: "RAW"
          body:
            values: ${query_result.rows.map(row => row.f.map(f => f.v))}
    - check_next_page:
        switch:
          - condition: ${query_result.pageToken != ""}
            assign:
              - page_token: ${query_result.pageToken}
            next: run_bq_query
    - complete:
        return: "全量数据写入完成"

注意：需给Workflows服务账号授予BigQuery查询权限和Sheets编辑权限，确保单批次返回数据大小不超过2MB阈值。

方案3：BigQuery Sheets连接器+脚本全量刷新 #

虽然Sheets的BigQuery连接默认仅显示5000行预览，但可通过脚本触发全量数据刷新：

function refreshBQConnectionAndCopy() {
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("连接BigQuery的Sheet");
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("目标Sheet");
  
  // 刷新BigQuery数据源
  const dataSources = sourceSheet.getDataSources();
  for (let ds of dataSources) {
    ds.refresh();
  }
  
  // 等待全量数据加载完成（根据数据量调整等待时间）
  Utilities.sleep(60000);
  
  // 复制全量数据到目标Sheet
  sourceSheet.getDataRange().copyTo(targetSheet.getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

注意：需确保Sheets的BigQuery连接配置为允许加载全量数据，且脚本有足够的执行时间配额。

内容的提问来源于stack exchange，提问作者pb-0