使用Apps Script从BigQuery导入数据到Spreadsheet时遇内存不足错误求解决

你的代码目前会一次性将BigQuery返回的所有结果加载到内存中（通过rows.concat拼接所有分页数据），再构建完整的二维数组写入Sheet，当数据量较大时就会触发Out of memory error。下面是针对性的优化方案：

核心优化思路 #

• 分批处理查询结果，每获取一页数据就直接写入Sheet，不把所有数据存在内存里
• 减少不必要的中间数组构建，降低内存占用
• 保留高效的批量写入方式（setValues），避免频繁调用appendRow影响性能

修改后的代码 #

/**
 * Runs a BigQuery query and logs the results in a spreadsheet (分批处理版)
 */
function runQuery() {
  const projectId = 'data-ru-2dlj';
  const request = {
    query: 'SELECT * FROM `data-ru-2dlj.finance_wh.d_model`;',
    useLegacySql: false,
    // 可选：设置每页返回的行数，根据数据列数调整，避免单页数据过大
    maxResults: 5000
  };

  let queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;

  // 等待查询完成
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('brand models');
  sheet.clearContents();

  // 写入表头
  const headers = queryResults.schema.fields.map(field => field.name);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  let currentRow = 2; // 从第二行开始写数据

  // 分批处理并写入数据
  do {
    // 处理当前页的行数据
    const pageData = queryResults.rows.map(row => row.f.map(col => col.v));
    
    // 批量写入当前页数据
    sheet.getRange(currentRow, 1, pageData.length, headers.length).setValues(pageData);
    
    // 更新下一次写入的起始行
    currentRow += pageData.length;
    
    // 获取下一页数据
    if (queryResults.pageToken) {
      queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
        pageToken: queryResults.pageToken,
        maxResults: 5000 // 和请求时保持一致
      });
    }
  } while (queryResults.pageToken);

  Logger.log('数据导入完成，表格链接: %s', spreadsheet.getUrl());
}

关键优化点说明 #

1. 分批获取数据：通过maxResults指定每页返回的行数（建议根据列数调整，比如5000-10000行，列多就调小），避免单页数据过大
2. 边处理边写入：获取到一页数据后直接转换格式并写入Sheet，不存储所有行到内存
3. 高效写入：用getRange().setValues()批量写入，比appendRow性能更高，同时减少API调用次数
4. 移除内存密集操作：删除了原代码中rows.concat和构建完整data数组的逻辑，大幅降低内存占用

如果数据量极大（比如几十万行），还可以考虑：

• 在BigQuery中先对数据进行过滤（减少返回行数）
• 使用BigQuery.Jobs.getQueryResults的startIndex参数分段拉取，避免依赖pageToken（不过pageToken方式更简单）

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