如何优化多单元格读写的Google Apps Script提升执行效率？

原脚本的核心性能问题 #

原脚本耗时的根源在于多次单独调用Spreadsheet API：

• 逐个单元格调用getRange().getDisplayValue()，每次都是一次独立API请求
• 多次分区域调用setValues()写入数据，增加了API交互开销

下面是优化后的代码，同时用map简化了重复逻辑：

function savetoPOVN() {
  // 初始化工作表
  const sourceSS = SpreadsheetApp.getActive();
  const wsformPO = sourceSS.getSheetByName("formPO");
  const targetSS = SpreadsheetApp.openById("SheetID1");
  const wsPOV = targetSS.getSheetByName("data_pov");
  const wsPON = targetSS.getSheetByName("data_pon");

  // 一次性读取所有需要的单元格数据（仅1次API调用）
  const dataRange = wsformPO.getRange("C22:J77");
  const allData = dataRange.getDisplayValues();
  
  // 封装取值函数，从内存数组中快速取数
  const getVal = (rowOffset, colOffset) => allData[rowOffset][colOffset];

  // 提取公共数据
  const docType = getVal(0, 5); // H22
  const commonData = {
    h23: getVal(1, 5), // H23
    h24: getVal(2, 5), // H24
    c27e27: getVal(5, 0) + getVal(5, 1) + getVal(5, 2), // C27:E27合并值
    c28: getVal(6, 0), // C28
    poData1: [
      getVal(7, 0), getVal(5, 5), getVal(6, 5), getVal(5, 7), getVal(6, 7)
    ], // C29、H27、H28、J27、J28
    poData2: [
      getVal(47,7), getVal(48,7), getVal(49,7), getVal(51,7),
      getVal(52,7), getVal(53,7), getVal(54,7), getVal(55,7)
    ], // J69-J77
    note: [getVal(47,7), getVal(48,7), getVal(49,7), getVal(50,7)] // 对应原note_V，需根据实际调整
  };

  // 定义item配置数组，用map批量生成数据
  const itemsConfig = [
    { rowOffset: 11, itemNo: "1", amount: getVal(11, 3) }, // item1：C33行偏移11（33-22）
    { rowOffset: 23, itemNo: "2", amount: getVal(23, 3) }, // item2：C45行偏移23（45-22）
    { rowOffset: 35, itemNo: "3", amount: getVal(35, 3) }  // item3：C57行偏移35（57-22）
  ];

  // 筛选有数据的item
  const validItems = itemsConfig.filter(item => item.amount !== "");

  // 用map构建完整行数据
  const rowsToWrite = validItems.map(item => {
    const row = new Array(63).fill(""); // 目标表共63列，先填充空值
    // 填充item基础数据（列2-9）
    row[1] = commonData.h23;
    row[2] = commonData.h24;
    row[3] = commonData.c27e27;
    row[4] = commonData.c28;
    row[5] = item.itemNo;
    row[6] = getVal(item.rowOffset, 0) + getVal(item.rowOffset, 1); // Cx:Dx合并
    row[7] = getVal(item.rowOffset, 3); // Fx
    row[8] = "USE";
    // 填充po_data1（列11-15）
    commonData.poData1.forEach((val, idx) => row[10 + idx] = val);
    // 填充item附加数据（列17-19）
    row[16] = getVal(item.rowOffset, 5); // Hx
    row[17] = getVal(item.rowOffset, 6); // Ix
    row[18] = getVal(item.rowOffset, 7); // Jx
    // 填充po_data2（列20-27）
    commonData.poData2.forEach((val, idx) => row[19 + idx] = val);
    // 填充note（列60-63）
    commonData.note.forEach((val, idx) => row[59 + idx] = val);
    return row;
  });

  if (rowsToWrite.length === 0) return;

  // 选择目标工作表并一次性写入所有行
  const targetWS = docType === "V" ? wsPOV : wsPON;
  const lastRow = targetWS.getLastRow();
  targetWS.getRange(lastRow + 1, 1, rowsToWrite.length, rowsToWrite[0].length).setValues(rowsToWrite);

  // 排序
  targetWS.getRange(6, 2, targetWS.getLastRow() - 5, 62).sort({ column: 2, ascending: true });
}

优化效果说明 #

1. API调用次数骤减：原脚本至少30+次API请求，优化后仅需3-4次（读取1次，写入1次，获取最后行1次，排序1次），直接解决耗时问题
2. 用.map简化重复逻辑：通过配置数组+map遍历，消除了item1/2/3的重复代码，后续新增item只需在配置数组中加一行
3. 内存操作替代API请求：所有数据读取后都在内存数组中操作，避免了频繁的网络交互

注意事项 #

• 代码中的行/列偏移量需要根据实际单元格位置调整（数组索引从0开始，对应原工作表行=起始行+偏移量，列=起始列+偏移量）
• 原脚本中未定义的item2_amount_V等变量，已通过配置数组中的amount字段实现，需根据实际数据位置确认取值逻辑
• 如果目标工作表列数不是63，需修改new Array(63).fill("")中的数字

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