如何简化Google Apps Script批量数据修改脚本并优化性能？

核心优化思路 #

替代原脚本中逐列调用getRange的低效写法，改用批量获取/写入数据的方式，大幅减少Google Apps Script API调用次数，提升执行性能，同时保留原有匹配更新逻辑。

优化后的完整脚本 #

function updateData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formSheet = ss.getSheetByName("Data Entry Form");
  
  // 一次性获取匹配条件（医院、年份）和全部64列待更新数据
  // 注意：根据实际表单列范围调整，示例中为B7到BQ7（共64列）
  const formRowData = formSheet.getRange("B7:BQ7").getValues()[0];
  const hospital = formRowData[0]; // B7对应数组索引0
  const year = formRowData[2];     // D7对应数组索引2（B是第0位，C第1，D第2）
  const updateValues = formRowData.slice(0, 64); // 提取前64列数据
  
  // 校验必填项
  if (!hospital || !year) {
    SpreadsheetApp.getUi().alert("请填写医院和年份信息");
    return;
  }

  // 更新Billing Masterlist
  const masterSheet = ss.getSheetByName("Billing Masterlist");
  const masterAllData = masterSheet.getDataRange().getValues();
  let masterTargetRow = -1;

  // 遍历查找匹配医院+年份的行（跳过表头，假设表头在第1行）
  for (let i = 1; i < masterAllData.length; i++) {
    // 假设医院在B列（数组索引1）、年份在D列（数组索引3），根据实际调整
    if (masterAllData[i][1] === hospital && masterAllData[i][3] === year) {
      masterTargetRow = i + 1; // 转换为工作表行号（从1开始）
      break;
    }
  }

  if (masterTargetRow !== -1) {
    // 批量写入64列数据到目标行（从B列开始，对应列索引2）
    masterSheet.getRange(masterTargetRow, 2, 1, 64).setValues([updateValues]);
  }

  // 更新指定医院工作表
  const hospitalSheet = ss.getSheetByName(hospital);
  if (!hospitalSheet) {
    SpreadsheetApp.getUi().alert(`未找到「${hospital}」对应的工作表`);
    return;
  }

  const hospitalAllData = hospitalSheet.getDataRange().getValues();
  let hospitalTargetRow = -1;

  // 遍历查找匹配年份的行（跳过表头）
  for (let i = 1; i < hospitalAllData.length; i++) {
    // 假设年份在D列（数组索引3），根据实际调整
    if (hospitalAllData[i][3] === year) {
      hospitalTargetRow = i + 1;
      break;
    }
  }

  if (hospitalTargetRow !== -1) {
    // 批量写入64列数据到目标行
    hospitalSheet.getRange(hospitalTargetRow, 2, 1, 64).setValues([updateValues]);
  }

  SpreadsheetApp.getUi().alert("数据更新完成");
}

关键优化点说明 #

1. 批量获取数据：通过一次getRange("B7:BQ7").getValues()获取所有需要的表单数据，替代原脚本64次单独的getRange调用，直接将API请求次数从60+降到个位数。
2. 内存中遍历匹配：一次性读取目标工作表的全部数据到内存数组中进行匹配，避免反复读取工作表，提升查找效率。
3. 批量写入数据：用setValues([updateValues])一次性写入64列数据，替代多次单独写入操作。

适配调整提示 #

• 列范围调整：如果表单中64列数据的位置不是B7:BQ7，修改getRange的参数（比如从C7开始的话，改为C7:BR7）。
• 列索引调整：代码中假设医院在B列、年份在D列，若实际位置不同，修改循环中的数组索引（比如医院在C列则改为masterAllData[i][2]）。
• 表头行数调整：如果目标工作表的表头不是1行，修改循环的起始索引i（比如表头2行则从i=2开始）。

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