Google Apps Script 数组循环遍历性能优化方案咨询

问题背景 #

• 需求逻辑：遍历「List」工作表存储唯一商品ID的列，若某商品ID在「Data」工作表的商品ID列存在匹配项，提取Data表中该ID对应的相邻属性值，填充至List表对应行的指定位置
• 现存问题：初始版本脚本仅适配小数据量场景，处理1000行以上大规模数据集时运行耗时长达30-60分钟，效率极低
• 优化思路：通过getValues()批量读、setValues()批量写、内存数组运算的方式，减少高频表格API调用，大幅提升运行速度

初始低效版本代码 #

function example() {
var list = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data")
var listendRow = list.getLastRow();
var dataendRow = data.getLastRow();
var dataid = data.getDataRange().getValue();

for (var i = 2; i <= listendRow; i++) {
for (var j = 2; j <= dataendRow; j++){
var idnum = [list.getRange(i, 2,listendRow).getValue()];
var id = data.getRange(j, 3).getValue();
var name = data.getRange(j, 4).getValue();
var weight = data.getRange(j, 5).getValue();

if (idnum == id){
list.getRange(i, 3).setValue(name);
list.getRange(i, 4).setValue(weight);

} 
}
}
} 

性能瓶颈原因 #

• 核心问题：循环内反复调用getRange()、getValue()、setValue()操作单元格，Google Apps Script中这类Spreadsheet服务的单次调用延迟极高，是性能损耗的核心来源
• 算法低效：双层嵌套循环的时间复杂度为O(n*m)，1000行级数据场景下会产生上百万次API调用，直接导致运行耗时过长
• 逻辑冗余：错误使用单值读取方法getValue()读取整列范围，存在无效运算

优化后高性能实现 #

优化逻辑 #

• 全量数据仅通过2次getValues()调用一次性读入内存数组，全程无循环内读操作
• 先将Data表的商品ID与对应属性构造为键值对映射对象，匹配时直接通过键查找，时间复杂度降至O(n+m)
• 所有待写入结果在内存中组装完成后，仅通过1次setValues()调用批量写回表格，无循环内写操作

优化后代码 #

function optimizedGoodsMatch() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = ss.getSheetByName("List");
  const dataSheet = ss.getSheetByName("Data");

  // 批量读取两个工作表的全部有效数据到内存
  const listAllRows = listSheet.getDataRange().getValues();
  const dataAllRows = dataSheet.getDataRange().getValues();

  // 构造Data表ID映射：key为商品ID，value为[商品名称, 商品重量]数组
  const dataIdMap = {};
  // 跳过表头行，从第2行（数组索引1）开始遍历Data表
  for (let j = 1; j < dataAllRows.length; j++) {
    const row = dataAllRows[j];
    const goodsId = row[2]; // Data表第3列（C列，数组索引2）存储商品ID
    const goodsName = row[3]; // 第4列（D列，索引3）存储商品名称
    const goodsWeight = row[4]; // 第5列（E列，索引4）存储商品重量
    dataIdMap[goodsId] = [goodsName, goodsWeight];
  }

  // 内存中完成List表的ID匹配，组装待写入数组
  const resultArr = [];
  // 跳过表头行，从第2行（数组索引1）开始遍历List表
  for (let i = 1; i < listAllRows.length; i++) {
    const currentId = listAllRows[i][1]; // List表第2列（B列，数组索引1）存储商品ID
    resultArr.push(dataIdMap[currentId] || ["", ""]);
  }

  // 批量将结果写入List表C、D列，从第2行开始
  if (resultArr.length > 0) {
    listSheet.getRange(2, 3, resultArr.length, 2).setValues(resultArr);
  }
}

性能表现 #

1000行级数据场景下，脚本运行耗时可从原版本的30-60分钟压缩至2秒以内。

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