如何提升处理Google Sheet用户数据的Google Apps Script运行效率？

性能瓶颈说明 #

• 你当前代码的最大问题是每行都单独调用表格读写接口，getRange()、getValues()、setValues()都是跨进程的I/O操作，单次调用就有几十到几百毫秒延迟，1400行累计下来就会达到数十分钟。
• 你有VBA开发背景的话，这个逻辑和VBA里逐单元格读写的性能问题完全一致，Google Sheets的接口调用开销远高于VBA，所以这类优化的收益更明显。

优化后代码 #

function checkRealUsers() {
  const patterns = ["notreal","demo","notauser","test","debug"];
  const realAcctCol = 9;
  const emailCol = 3;
  const lastNameCol = 2;
  const startRow = 2;

  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Registered Customers");
  // 一次性获取所有有效行的最大行号
  const lastRow = sheet.getLastRow();
  if (lastRow < startRow) return;

  // 单次批量读取所有需要的列：姓氏列（2）、邮箱列（3），从第2行到最后一行
  const minCol = Math.min(lastNameCol, emailCol);
  const maxCol = Math.max(lastNameCol, emailCol);
  const dataRange = sheet.getRange(startRow, minCol, lastRow - startRow + 1, maxCol - minCol + 1);
  const dataValues = dataRange.getValues();
  // 预先生成匹配正则，忽略大小写，不需要单独转小写
  const matchRegex = new RegExp(patterns.join('|'), 'i');
  // 内存中生成结果数组
  const resultArr = [];

  for (let i = 0; i < dataValues.length; i++) {
    const lastName = dataValues[i][lastNameCol - minCol] || '';
    const email = dataValues[i][emailCol - minCol] || '';
    // 若需要对齐原逻辑：遇到空邮箱即终止处理后续行，可取消下一行注释
    // if (!email) break;
    // 单次正则匹配即可判断是否命中任意模式
    resultArr.push([matchRegex.test(lastName) || matchRegex.test(email) ? 'No' : 'Yes']);
  }

  // 单次批量写入所有结果到目标列
  sheet.getRange(startRow, realAcctCol, resultArr.length, 1).setValues(resultArr);
}

优化效果说明 #

• 表格读写操作从数千次压缩到2次（1次读、1次写），1400行数据处理耗时会从20分钟缩短到1-2秒
• 正则匹配替代逐模式循环，进一步降低了内存中的计算开销

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