如何加速谷歌表格中保护公式单元格的脚本？

问题背景 #

手上有个包含多工作表的Google Sheets，部分单元格是数值，部分用公式。需要给带公式的单元格添加编辑保护，仅允许指定的少量用户编辑这些单元格。自己写的脚本运行速度极慢，尝试过用命名范围或批量分配锁定范围，但代码报错无法正常运行。原脚本如下：

function myFunction2() {
const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
 
for(const sheeto of sheets) { //遍历所有工作表
 
 var ss1 = sheeto.getName();
 
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(ss1);
 var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE); 
 for (var i = 0; i < protections.length; i++) { //删除现有锁定
   var protection = protections[i];
   if (protection.canEdit()) {
     protection.remove();
   }
 }
 var arr2 = ss.getDataRange().getFormulas();
 var numRows = arr2.length-1;
 var numCols = arr2[0].length-1;
 for (var i = 0; i <= numCols; ++i) {
   for (var y = 0; y <= numRows; ++y) {
     if (arr2[y][i]!="") { //锁定所有带公式的单元格
       var range = ss.getRange(y+1,i+1);
       var protection = range.protect().setDescription('автозащита');
       var me = Session.getEffectiveUser();
       protection.addEditor(me);
       
       protection.addEditor('пользователь1');
       protection.addEditor('пользователь2');
       protection.addEditor('пользователь3');
       protection.removeEditors(protection.getEditors());
       if (protection.canDomainEdit()) {
         protection.setDomainEdit(false);

       }
     }
   }
 }
}
}

原代码慢的核心原因 #

1. 逐个单元格创建保护：每个带公式的单元格都单独生成一个保护对象，Google Apps Script的API调用次数直接拉满，这是速度慢的主要原因。
2. 冗余的工作表获取：已经拿到sheeto工作表对象，还要通过名字重新获取一遍，完全没必要。
3. 权限逻辑混乱：先添加编辑器，再调用removeEditors(protection.getEditors())把所有编辑器都删掉，等于白加，权限设置逻辑错误。
4. 嵌套循环遍历所有单元格：逐行逐列扫描整个数据范围，效率极低。

优化后的代码 #

function protectFormulaCells() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allowedEditors = [
    Session.getEffectiveUser(),
    'пользователь1',
    'пользователь2',
    'пользователь3'
  ];
  
  // 遍历所有工作表
  ss.getSheets().forEach(sheet => {
    // 删除当前工作表的所有范围保护
    const existingProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    existingProtections.forEach(protection => {
      if (protection.canEdit()) protection.remove();
    });
    
    // 获取所有带公式的单元格范围
    const formulaRanges = sheet.getDataRange().getFormulas()
      .map((row, rowIdx) => {
        return row.map((formula, colIdx) => {
          return formula ? sheet.getRange(rowIdx + 1, colIdx + 1) : null;
        }).filter(range => range !== null);
      })
      .flat();
    
    // 如果有需要保护的范围，批量处理
    if (formulaRanges.length > 0) {
      // 合并所有公式单元格为一个保护范围（离散单元格会自动拆分为子保护，但数量远少于逐个创建）
      const protection = sheet.getRangeList(formulaRanges).protect().setDescription('автозащита');
      
      // 设置权限：移除所有默认编辑器，添加指定用户，关闭域编辑
      protection.removeEditors(protection.getEditors());
      protection.addEditors(allowedEditors);
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  });
}

优化点说明 #

1. 批量合并保护范围：用getRangeList把所有公式单元格合并成一个保护对象，大幅减少API调用次数，这是提速的关键。
2. 简化工作表操作：直接遍历ss.getSheets()返回的工作表对象，避免重复获取的冗余操作。
3. 修正权限逻辑：先移除所有现有编辑器，再添加指定的允许用户，确保权限设置准确。
4. 高效筛选公式单元格：用数组方法map+filter+flat快速筛选出带公式的单元格范围，比嵌套循环更简洁高效。

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