如何优化Google Apps Script以提升谷歌表格批量处理速度？

核心优化思路 #

Google Apps Script的性能瓶颈大多来自频繁的Google Sheets API调用，每次getRange()、setValue()、setBackground()都会触发一次服务器通信，累积起来就会变慢。优化的关键是把多次零散调用合并成批量操作。

优化后的完整代码 #

// 验证用户输入
function validateGnteEntry(shUserForm) {
  const ui = SpreadsheetApp.getUi();
  
  // 批量设置V3:V24的背景色
  const bgColors = Array(22).fill('#EDE3FF');
  bgColors[17] = '#FFFF00'; // V24对应索引17（V3是索引0，V3到V24共22个单元格）
  shUserForm.getRange("V3:V24").setBackgrounds([bgColors]);

  // 验证V18是否为空
  const formValues = shUserForm.getRange("V3:V24").getValues()[0];
  if (!formValues[15]) { // V18是V3开始的第15个索引（0-based）
    ui.alert("Alert: Please enter a valid parent GNTE into cell V18");
    shUserForm.getRange('V18').setBackground('#FFFF00');
    return false;
  }

  return true;
}

// 提交数据到数据库
function createGnte() {
  const myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  const shUserForm = myGoogleSheet.getSheetByName("GNTE Requester");
  const datasheet = myGoogleSheet.getSheetByName("GNTE Request Database");
  const ui = SpreadsheetApp.getUi();
  
  const response = ui.alert("Submit", "Do you want to submit the data?", ui.ButtonSet.YES_NO);
  if (response === ui.Button.NO) return;

  if (validateGnteEntry(shUserForm)) {
    const blankRow = datasheet.getLastRow() + 1;
    // 一次性读取表单所有需要的数值
    const formValues = shUserForm.getRange("V3:V24").getValues()[0];
    
    // 构造要写入数据库的一行数据（对应列1-26）
    const newRow = new Array(26).fill("");
    newRow[0] = formValues[0];    // V3 -> 列1
    newRow[1] = formValues[1];    // V4 -> 列2
    newRow[2] = formValues[2];    // V5 -> 列3
    newRow[3] = formValues[3];    // V6 -> 列4
    newRow[4] = formValues[4];    // V7 -> 列5
    newRow[5] = formValues[5];    // V8 -> 列6
    newRow[6] = formValues[6];    // V9 -> 列7
    newRow[7] = formValues[7];    // V10 -> 列8
    newRow[8] = formValues[14];   // V17 -> 列9
    newRow[9] = formValues[8];    // V11 -> 列10
    newRow[10] = formValues[9];   // V12 -> 列11
    newRow[11] = formValues[10];  // V13 -> 列12
    newRow[12] = formValues[12];  // V15 -> 列13
    newRow[13] = formValues[13];  // V16 -> 列14
    newRow[14] = new Date();      // 提交时间 -> 列15
    newRow[15] = Session.getActiveUser().getEmail(); // 提交人 -> 列16
    newRow[16] = "";              // 待处理人 -> 列17
    newRow[17] = "Pending";       // 状态 -> 列18
    newRow[18] = formValues[17];  // V20 -> 列19
    newRow[19] = formValues[18];  // V21 -> 列20
    newRow[20] = formValues[19];  // V22 -> 列21
    newRow[21] = formValues[20];  // V23 -> 列22
    newRow[22] = formValues[15];  // V18 -> 列23
    newRow[23] = formValues[16];  // V19 -> 列24
    newRow[24] = formValues[11];  // V14 -> 列25
    newRow[25] = formValues[21];  // V24 -> 列26

    // 批量写入一行数据到数据库
    datasheet.getRange(blankRow, 1, 1, 26).setValues([newRow]);
    // 设置提交时间的格式
    datasheet.getRange(blankRow, 15).setNumberFormat('mm/dd/YYYY');

    // 批量重置表单：先定义每个单元格的操作
    const resetRange = shUserForm.getRange("V3:V24");
    const resetFormulas = new Array(22).fill("");
    const resetBackgrounds = Array(22).fill('#EDE3FF');
    resetBackgrounds[17] = '#FFFF00'; // V24背景色

    // 填充需要恢复的公式
    resetFormulas[2] = "=iferror(VLOOKUP(V3&V4,'Upper Lower Database'!A:P,MATCH(U5,'Upper Lower Database'!1:1,0),FALSE),)"; // V5
    resetFormulas[3] = "=iferror(VLOOKUP(V3&V4,'Upper Lower Database'!A:P,MATCH(U6,'Upper Lower Database'!1:1,0),FALSE),)"; // V6
    resetFormulas[4] = "=iferror(VLOOKUP(V3&V4,'Upper Lower Database'!A:P,MATCH(U7,'Upper Lower Database'!1:1,0),FALSE),)"; // V7
    resetFormulas[6] = "=iferror(VLOOKUP(V3&V4,'Upper Lower Database'!A:P,MATCH(U9,'Upper Lower Database'!1:1,0),FALSE),)"; // V9
    resetFormulas[8] = "=iferror(VLOOKUP(V3&V4,'Upper Lower Database'!A:P,MATCH(U11,'Upper Lower Database'!1:1,0),FALSE),)"; // V11
    resetFormulas[11] = "=IFERROR(V13/V12,0)"; // V14
    resetFormulas[12] = "=IFERROR(V5/V13,0)"; // V15
    resetFormulas[17] = "=IFERROR(VLOOKUP($V$3,'2023 Summary'!$C:$AQ,MATCH($U20,'2023 Summary'!$C$5:$AQ$5,0),false),)"; // V20
    resetFormulas[18] = "=IFERROR(VLOOKUP($V$3,'2023 Summary'!$C:$AQ,MATCH($U21,'2023 Summary'!$C$5:$AQ$5,0),false),)"; // V21
    resetFormulas[19] = "=IFERROR(VLOOKUP($V20,Vlookups!$I:$J,2,false),)"; // V22
    resetFormulas[20] = "=IFERROR(VLOOKUP($V21,Vlookups!$I:$J,2,false),)"; // V23

    // 批量设置公式和背景色，先清空内容
    resetRange.clearContent();
    resetRange.setFormulas([resetFormulas]);
    resetRange.setBackgrounds([resetBackgrounds]);
  }
}

具体优化点说明 #

• 批量读取/写入数据：用getRange("V3:V24").getValues()一次性读取所有表单值，用setValues([newRow])批量写入数据库，把几十次API调用压缩到2次。
• 批量设置背景色：用setBackgrounds()一次性设置V3到V24的背景色，替代原脚本22次单独的setBackground()调用。
• 传递工作表引用：把shUserForm作为参数传给validateGnteEntry，避免重复调用getSheetByName()获取工作表。
• 批量重置表单：把清空、设置公式、设置背景色的操作合并，用clearContent()、setFormulas()、setBackgrounds()三个批量操作完成，替代原脚本几十次零散调用。
• 减少不必要的激活操作：移除了shUserForm.getRange('V18').activate()，因为设置背景色已经足够提示用户，激活单元格对性能有额外消耗。

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