修复Google Sheets批量保护非指定字符串单元格的API请求报错

我编写了一个宏，用于为指定范围内不包含特定字符串的所有单元格添加保护，但该宏因执行超时无法完成全部单元格的保护操作，估算需提升约100倍效率才能在允许时间内完成。尝试改用Sheets API的batchUpdate方法优化性能，却请求失败：最初提示GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: The service is currently unavailable.，即使缩小到少量单元格测试，仍报错GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid value at 'requests[0].add_protected_range.protected_range.range.sheet_id' (TYPE_INT32)。不确定是请求内容有误还是发送方式不当，希望修复batchUpdate请求，或找到其他大幅提升添加单元格保护性能的方法。

需要处理的表格范围包括J2:W2793（约4万单元格）和AF2:EJ2793（30万+单元格），需锁定这些范围内不符合字符串条件的单元格。

附原宏脚本：

function addProtectionsToCells() {
  // Replace 'YOUR_SPREADSHEET_ID', 'YOUR_SHEET_NAME', and 'A1:C10' with your actual values
  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetName = 'example';
  var rangeToProtect = 'J2:W2';
  var stRow = range.getRow()
  var enRow = stRow +1
  var stCol = range.getColumn()
  var enCol = stCol + 1

  // Replace with your specific strings
  var stringsToProtect = ['a', 'e', 'i', 'o', 'u', 'y', 'æ', 'c', 'g', 'ā', 'ē', 'ī', 'ō', 'ū', 'ȳ', 'ǣ', 'ċ', 'ġ'];

  // Get the current sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange(rangeToProtect);
  var values = range.getValues();

  // Initialize the batchUpdate request
  var requests = [];

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (!stringsToProtect.includes(values[i][j])) {
        // Create protection request
        var protectionRequest = {
          addProtectedRange: {
            protectedRange: {
              range: {
                sheetId: spreadsheetId,
                startRowIndex: i + stRow,
                endRowIndex: i + enRow,
                startColumnIndex: j + stCol,
                endColumnIndex: j + enCol,
              },
              warningOnly: false,
              editors: {
                domainUsersCanEdit: false,
                users: []
              }
            }
          }
        };

        // Add protection request to the batchUpdate requests
        requests.push(protectionRequest);
      }
    }
  }

  // Execute the batchUpdate request
  Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId);
}

一、修复batchUpdate请求的核心错误 #

• sheetId参数错误：原代码将表格ID（字符串类型）传给了range.sheetId，但该字段需要的是工作表的数字ID，而非整个表格的ID。正确获取方式：sheet.getSheetId()。
• 行列索引偏移错误：Sheets API的行列索引从0开始，而SpreadsheetApp的getRow()/getColumn()从1开始。计算索引时需做减1处理：
  • startRowIndex = i + stRow - 1，endRowIndex = startRowIndex + 1
  • startColumnIndex = j + stCol - 1，endColumnIndex = startColumnIndex + 1
• 变量初始化顺序错误：原代码在获取range之前就调用range.getRow()，会导致变量未定义错误，需将stRow等变量的定义移到var range = sheet.getRange(rangeToProtect);之后。

二、性能优化关键措施 #

• 用哈希表提升判断效率：将stringsToProtect转为Set，!protectSet.has(values[i][j])的判断速度远快于数组includes，尤其适合元素较多的场景。
• 分批执行请求：Sheets API的batchUpdate一次最多支持1000个请求，处理大规模单元格时需拆分请求数组，每1000个请求执行一次，避免请求过大被拒绝。
• 合并连续单元格请求：如果同一行/列有连续符合条件的单元格，合并为一个范围请求，大幅减少总请求数量（比如整行连续单元格合并为一个保护范围）。

修正后的完整脚本 #

function addProtectionsToCells() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId();
  var sheetName = 'example';
  var rangeToProtect = 'J2:W2793'; // 可替换为AF2:EJ2793
  var stringsToProtect = ['a', 'e', 'i', 'o', 'u', 'y', 'æ', 'c', 'g', 'ā', 'ē', 'ī', 'ō', 'ū', 'ȳ', 'ǣ', 'ċ', 'ġ'];
  // 转Set提升字符串判断效率
  var protectSet = new Set(stringsToProtect);

  var sheet = spreadsheet.getSheetByName(sheetName);
  var range = sheet.getRange(rangeToProtect);
  var values = range.getValues();
  var stRow = range.getRow();
  var stCol = range.getColumn();
  var sheetId = sheet.getSheetId(); // 获取工作表数字ID

  var requests = [];
  const MAX_REQUESTS_PER_BATCH = 1000; // 单批次请求上限

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (!protectSet.has(values[i][j])) {
        // 转换为Sheets API需要的0-based索引
        var startRow = i + stRow - 1;
        var endRow = startRow + 1;
        var startCol = j + stCol - 1;
        var endCol = startCol + 1;

        var protectionRequest = {
          addProtectedRange: {
            protectedRange: {
              range: {
                sheetId: sheetId,
                startRowIndex: startRow,
                endRowIndex: endRow,
                startColumnIndex: startCol,
                endColumnIndex: endCol
              },
              warningOnly: false,
              editors: {
                domainUsersCanEdit: false,
                users: []
              }
            }
          }
        };

        requests.push(protectionRequest);

        // 达到批次上限时执行请求并重置数组
        if (requests.length >= MAX_REQUESTS_PER_BATCH) {
          Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId);
          requests = [];
          // 可选：添加短暂延迟避免API限流
          Utilities.sleep(100);
        }
      }
    }
  }

  // 处理剩余未执行的请求
  if (requests.length > 0) {
    Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId);
  }
}

额外注意事项 #

• 确保已在Google Apps Script编辑器中启用Sheets API：点击「资源」→「高级Google服务」，找到「Google Sheets API」并开启。
• 针对30万+单元格的超大规模范围，可进一步优化：先识别连续的行/列保护块，合并请求，减少总请求数，比如同一行内连续符合条件的单元格合并为一个保护范围，而非每个单元格单独请求。

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