优化Google Sheets Apps Script代码以规避6分钟执行时限

现有代码的优化点 #

你的基础优化方向是对的，还有几个细节可以进一步压缩执行时间：

1. 修正索引匹配问题（关键！） #

注意getValues()返回的是0-based数组，但你定义的identifiers里的列号是1-based的表格列数（比如Campaign对应[3,5]，实际是表格第3、5列，对应数组索引应该是2、4）。索引不匹配会导致字典dict60的key无法正确匹配，进而产生大量无效查找，拖慢执行速度。

2. 减少重复对象创建 #

把重复用到的Array(11).fill('')提前定义为常量，避免每次循环都创建新数组：

const EMPTY_11 = Array(11).fill('');

3. 简化数组操作逻辑 #

在output的map处理中，避免先拼接空数组再替换的冗余操作，直接根据匹配结果生成目标片段：不需要先创建newRow = row.concat(EMPTY_11)，如果找到row60就直接拼接对应切片，否则拼接EMPTY_11。

4. 减少内存占用 #

构建字典时只存储需要的11列数据，而非整行；生成output时直接返回要写入的片段，不用保留完整行，降低内存消耗。

5. 调整操作顺序 #

把sheet.insertColumnsAfter(46, 11)放在数据处理完成后再执行，避免提前修改表格结构带来的隐性开销。

使用Google Sheets API的帮助 #

对于大数据集，直接调用Sheets API确实能提升读写效率：

• 批量读写：Sheets API的values.batchGet可一次性读取多个范围，values.batchUpdate可一次性写入多个区域，比SpreadsheetApp的方法在处理超大数据时更高效，减少请求往返次数。
• 列插入优化：用Sheets API的batchUpdate执行列插入操作，比SpreadsheetApp的insertColumnsAfter在处理大量列时更快。
• 注意：数据匹配、字典构建这类内存处理逻辑，和用SpreadsheetApp没有区别，核心还是优化JavaScript数组和对象操作。使用Sheets API需要在Apps Script编辑器中启用「Google Sheets API」（编辑器菜单→资源→高级Google服务）。

优化后的代码 #

function populatespc() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sponsored Products Campaigns');
  const sheet60 = ss.getSheetByName('60 Day Sponsored Products Campaigns');

  // 提前获取行号，避免重复调用
  const lastRow = sheet.getLastRow();
  const lastRow60 = sheet60.getLastRow();

  // 读取必要列（0-based数组，对应表格1-46列）
  const data = sheet.getRange(1, 1, lastRow, 46).getValues();
  const data60 = sheet60.getRange(1, 1, lastRow60, 46).getValues();

  // 修正为0-based列索引（原表格列号-1）
  const identifiers = {
    'Campaign': [2, 4],
    'Bidding Adjustment': [2, 30],
    'Ad Group': [2, 3],
    'Product Ad': [2, 3, 5],
    'Product Targeting': [2, 3, 7],
    'Keyword': [2, 3, 6]
  };

  const header = data[0];
  const newHeader = header.concat(header.slice(35, 46).map(h => `60 Day ${h}`));
  // 提前定义空数组常量
  const EMPTY_11 = Array(11).fill('');

  // 构建60天数据字典（只存需要的11列）
  const dict60 = {};
  for (let i = 1; i < data60.length; i++) {
    const row60 = data60[i];
    const entity = row60[1];
    const ids = identifiers[entity];
    if (!ids) continue;
    // 生成匹配key
    const key = ids.map(col => row60[col]).join('|');
    dict60[key] = row60.slice(35, 46);
  }

  // 直接生成要写入的11列数据
  const output = data.map((row, i) => {
    if (i === 0) return newHeader.slice(46); // 表头部分

    const entity = row[1];
    // 跳过不需要匹配的实体
    if (entity === 'Campaign Negative Keyword' || entity === 'Negative Keyword') {
      return EMPTY_11;
    }

    const ids = identifiers[entity];
    if (!ids) return EMPTY_11;

    // 生成匹配key
    const key = ids.map(col => row[col]).join('|');
    // 直接返回对应数据或空数组
    return dict60[key] || EMPTY_11;
  });

  // 先插入列，再写入数据
  sheet.insertColumnsAfter(46, 11);
  // 写入新增列数据
  sheet.getRange(1, 47, output.length, 11).setValues(output);
}

额外建议 #

• 如果数据集超大（比如10万行以上），可以考虑分批次处理：把数据分成若干块，每处理一块就写入一次，同时用SpreadsheetApp.flush()强制刷新，避免内存溢出。
• 启用脚本的新V8运行时（编辑器菜单→运行→启用新Apps Script运行时），V8引擎的JavaScript执行速度比旧引擎快很多。

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