如何提速用于保护和格式化工作表特定区域的代码？

问题背景 #

我是编程新手，现有一段Google Apps Script用于处理含千余行数据的工作表，根据行内容保护对应行，但运行耗时长达一小时。已知每5行的第6列（RNG）值会重复，希望通过批量处理（而非循环）改写脚本提升速度。原代码如下：

function protect_and_color(){
  var target_file  = SpreadsheetApp.openById("xxxxxxx"); 
  var sht = target_file.getSheetByName("FY22");

  
    for  (var x = 916;  x < (1064) ; x++)  // (must add one to the last row or will finish short one row)will need to updated this for each different template depending on number of rows of data.  X = is where to start x < is number of rows + start
    {     /// x is var (row number), column start, number of rows to impact, # of columns to color
          var rng  = sht.getRange(x,6,1,1);
          var rng2 = sht.getRange(x,6,1,14);   
          //var rng3 = sht.getRange(x,6,1,19); //need to update each month gray increment last argument
          //var rng4 = sht.getRange(x,7,1,13) //need to update each month green increment second argument and decrease the last argument by 1
          var rng5 = sht.getRange(x,1,1,18); //full row protections
          var rng6 = sht.getRange(x,19,1,1);// last column gray
          var rng7 = sht.getRange(x,1,1,6); //update protection, increment last argument - range that aes fill out
   
           if (rng.getValue() == "Bookings Last Year (FY21)")
           { //rng2.setBackground("#f9cb9c"); 
            //rng5.setBorder(true,true,null,true,null,null,"black",SpreadsheetApp.BorderStyle.SOLID_THICK);
            var protection = rng5.protect();
                protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                protection.setDomainEdit(false);}
           
           }
           else if (rng.getValue() == "Machine Learning Forecast")
           {//rng2.setBackground("#cfe2f3");
            //rng5.setBorder(null,true,null,true,null,null,"black",SpreadsheetApp.BorderStyle.SOLID_THICK);
            var protection = rng5.protect();
                protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                protection.setDomainEdit(false);}
           } 
           else if (rng.getValue() == "Bookings Actual (YTD)")
           {//rng2.setBackground("#d9d9d9");
            //rng5.setBorder(null,true,null,true,null,null,"black",SpreadsheetApp.BorderStyle.SOLID_THICK);
            var protection = rng5.protect(); 
                           protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                protection.setDomainEdit(false);}
           }   
           else if (rng.getValue() == "Bookings Forecast (rest of year)")
           //{rng3.setBackground("#d9d9d9");
            //rng4.setBackground("#d9d9d9");
           //rng5.setBorder(null,true,true,true,null,null,"black",SpreadsheetApp.BorderStyle.SOLID_THICK);
           var protection = rng7.protect();         
                protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                protection.setDomainEdit(false);}
           //var protection = rng6.protect();  --This part removed to make the whole data sheet run faster. No concern if the totals are messed up by aes because it's not used in fcst data import (deleted).
            //          protection.removeEditors(protection.getEditors());
            //    if (protection.canDomainEdit()) {
            //    protection.setDomainEdit(false);}
         }
       
           //if (rng.getValue() == "UoM Forecast")//"Bookings Forecast (rest of year)")
           //{rng4.setBackground("#d9ead3");         
           //}
         //}
}

优化方案 #

核心思路是批量读取数据+批量创建保护，减少Spreadsheet API的调用次数（原代码逐行调用API是速度慢的主要原因）：

• 一次性读取目标范围的第6列所有值，避免逐行getRange
• 利用每5行值重复的规律，按值分组批量处理整组行的保护
• 对相同类型的行，一次性创建一个保护覆盖所有对应行，而非逐行创建

优化后代码 #

function batchProtectAndColor() {
  const SPREADSHEET_ID = "xxxxxxx"; // 替换为你的表格ID
  const SHEET_NAME = "FY22";
  const START_ROW = 916;
  const END_ROW = 1063; // 原代码x<1064，对应最后一行是1063
  const VALUE_COL = 6; // 用于判断的第6列

  // 获取表格和目标范围数据
  const targetFile = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = targetFile.getSheetByName(SHEET_NAME);
  // 一次性读取第6列的所有值，转为一维数组方便处理
  const valueRange = sheet.getRange(START_ROW, VALUE_COL, END_ROW - START_ROW + 1, 1);
  const values = valueRange.getValues().flat();

  // 定义每种值对应的保护范围规则
  const protectionRules = {
    "Bookings Last Year (FY21)": { cols: [1, 18] }, // 保护第1-18列
    "Machine Learning Forecast": { cols: [1, 18] },
    "Bookings Actual (YTD)": { cols: [1, 18] },
    "Bookings Forecast (rest of year)": { cols: [1, 6] } // 保护第1-6列
  };

  // 按值分组收集对应行号
  const rowGroups = {};
  values.forEach((value, index) => {
    const rowNum = START_ROW + index;
    if (protectionRules[value]) {
      rowGroups[value] = rowGroups[value] || [];
      rowGroups[value].push(rowNum);
    }
  });

  // 批量处理每个分组的保护
  Object.keys(rowGroups).forEach(value => {
    const rows = rowGroups[value];
    const rule = protectionRules[value];
    // 创建覆盖所有目标行的连续范围
    const protectRange = sheet.getRange(rows[0], rule.cols[0], rows.length, rule.cols[1] - rule.cols[0] + 1);
    // 设置保护权限
    const protection = protectRange.protect();
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  });
}

关键优化点说明 #

1. 批量读取数据：用getRange(...).getValues()一次性获取所有判断值，替代原代码逐行getValue()，减少数百次API调用
2. 分组处理行：把相同值的行归为一组，一次性创建一个保护覆盖整组行，而非逐行创建保护，大幅减少保护创建的API调用
3. 规则化配置：把每种值对应的保护范围整理成规则对象，代码更清晰易维护
4. 明确范围边界：直接定义START_ROW和END_ROW，避免原代码循环条件的歧义

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