如何提升Google Apps Script从Salesforce取数写Sheet的效率？

问题背景 #

我有一个AppSheet应用，以Google Sheet为数据源，底层依赖Salesforce的大型sObject（660+列、数十万条记录）。由于只需要为每个用户同步极小范围的turf数据（100行以内，每行10列），官方的AppSheet/Salesforce集成效率太低，所以改用Google Apps Script通过GET请求拉取当前用户的Workers数据。

应用仅允许读取Workers记录用于更新关联表（关联表会写回Salesforce），Salesforce始终是Workers数据的权威来源，无需双向同步。但当前脚本处理100条记录耗时约30秒，其中Salesforce请求仅占3-5秒，剩余时间都耗在查重、删除旧数据、写入新数据环节——逐行遍历和单条API调用是主要性能瓶颈。

当前代码的性能瓶颈 #

1. 逐行删除旧数据：用forEach调用deleteRow()，每删一行都要发起一次Spreadsheet API请求，100条记录的删除操作耗时约10-11秒。
2. 逐行写入新数据：appendRow()同样是单条API调用，效率极低。
3. 查重效率低：用数组的includes()判断ID是否存在，数组查询时间复杂度为O(n)，且全局的contactIds不会随数据更新刷新。
4. 全量读取数据：每次调用setUserTurf都全量读取整个工作表数据，浪费资源。

优化方案 #

1. 批量删除非连续行 #

删除行时，先把行号从大到小排序，这样删除前面的行不会影响后面的行号；同时合并连续的行号范围，用deleteRows(startRow, numRows)批量删除，大幅减少API调用次数。

2. 批量写入数据 #

把所有要写入的行整理成二维数组，一次性调用setValues()写入，替代逐行的appendRow()。

3. 用Set优化查重逻辑 #

将Contact ID存储在Set中，Set.has()的查询时间复杂度为O(1)，远快于数组的includes()；并且每次处理前重新获取最新的ID集合，避免全局变量的过时问题。

4. 减少Spreadsheet API调用 #

所有数据处理先在内存中完成，再一次性发起读写请求，避免循环中频繁调用API。

优化后的代码示例 #

const fieldsArray = [ ... list of fields ... ];
const ss = SpreadsheetApp.openByUrl( ... url ...);
const workers = ss.getSheetByName( ... sheetName ...); 

async function loadUserTurf(employer) {
  let records;
  if (employer) {
    try {
      const qp = new QueryParameters();
      qp.setSelect(fieldsArray.toString());
      qp.setFrom("Contact");
      qp.setWhere(`Employer_Name_Text__c = \'${employer}\' AND Active_Worker__c = TRUE`);

      records = await get(qp);
      setUserTurf(employer, records);
    } catch (err) {
      logErrorFunctions('loadUserTurf', employer, records, err);
    }
  } else {
    console.log(`loadUserTurf > 24: no employer provided`);
  }
}

// 重新获取最新的Contact ID集合，用Set优化查重
function getContactIdSet() {
  const contactIds = workers.getRange("A2:A").getValues().flat().filter(Boolean);
  return new Set(contactIds);
}

// 批量写入数据
function batchAppendRows(data, sheet) {
  try {
    const contactIdSet = getContactIdSet();
    // 过滤出需要新增的行，并转换为二维数组
    const rowsToAppend = data
      .filter(obj => !contactIdSet.has(obj.Id))
      .map(obj => Object.values(obj).slice(1));
    
    if (rowsToAppend.length > 0) {
      const nextRow = sheet.getLastRow() + 1;
      sheet.getRange(nextRow, 1, rowsToAppend.length, rowsToAppend[0].length).setValues(rowsToAppend);
    }
  } catch (err) {
    logErrorFunctions('batchAppendRows', [data, sheet], '', err);
  }
}

// 批量处理旧数据删除
function batchDeleteTurfRows(employerName) {
  const allData = workers.getDataRange().getValues();
  // 收集所有需要删除的行号（注意行号是索引+1）
  const turfIndices = allData
    .map((row, index) => row[3] === employerName ? index + 1 : null)
    .filter(n => n);

  if (turfIndices.length === 0) return [];

  // 把行号从大到小排序，避免删除前面的行影响后面的行号
  turfIndices.sort((a, b) => b - a);

  // 合并连续的行号范围，批量删除
  let currentStart = turfIndices[0];
  let currentCount = 1;
  const deleteRanges = [];

  for (let i = 1; i < turfIndices.length; i++) {
    if (turfIndices[i] === currentStart - currentCount) {
      currentCount++;
    } else {
      deleteRanges.push({start: currentStart - currentCount + 1, count: currentCount});
      currentStart = turfIndices[i];
      currentCount = 1;
    }
  }
  deleteRanges.push({start: currentStart - currentCount + 1, count: currentCount});

  // 执行批量删除
  deleteRanges.forEach(range => {
    workers.deleteRows(range.start, range.count);
  });

  return turfIndices;
}

function setUserTurf(employerName, payload) {
  const deletedRows = batchDeleteTurfRows(employerName);
  
  if (deletedRows.length === 0) {
    batchAppendRows(payload, workers);
  } else {
    batchAppendRows(payload, workers);
  }
}

额外优化建议 #

• 启用V8运行时：Google Apps Script的V8引擎比旧引擎快很多，确保脚本设置中已启用。
• 避免全局变量频繁读取：ss和workers可以保留，但动态数据（如Contact ID）每次处理前重新获取。
• 错误处理中减少不必要的日志：如果日志操作耗时，可以适当简化。

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