Google Sheets自动删除过期班次残留#REF保护规则的解决需求

问题背景 #

我制作了一个自定义Google Sheets班次替班表，用户可发布需替班的班次，其他人能承接。配套了两个Google Script：

• 自动保护脚本：用户输入内容的单元格自动添加保护，仅所有者和原编辑者可修改；清空单元格时自动移除对应保护。
• 自动删除脚本：每日自动删除已过期的班次，保持表格整洁。

两个脚本单独运行正常，但同时使用时，自动删除脚本执行后，被删除单元格的保护规则会残留为#REF状态，长期积累可能引发问题。需要实现两种解决思路之一：

1. 修改自动删除脚本，在删除单元格前先移除对应保护规则
2. 编写独立脚本，清理所有#REF状态的无效保护规则

方案一：修改自动删除脚本（删除前移除保护） #

在删除过期行之前，先遍历并移除该行对应的所有范围保护，避免残留无效规则。修改后的代码如下：

function deleterow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3;
  var numRows = sheet.getLastRow() - 1;
  var dataRange = sheet.getRange(startRow, 2, numRows);
  var data = dataRange.getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);
  
  // 获取当前工作表所有范围保护
  var allProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  for (i = data.length - 1; i > -1; i--) {
    var row = data[i];
    var sheetDate = new Date(row);
    sheetDate.setHours(0, 0, 0, 0);
    
    if (today > sheetDate) {
      var targetRow = i + 3;
      var range = sheet.getRange(targetRow, 2, 1, 15);
      
      // 遍历所有保护，移除当前行对应的保护
      allProtections.forEach(function(protection) {
        var protRange = protection.getRange();
        // 检查保护范围是否包含当前要删除的行
        if (protRange.getRow() <= targetRow && protRange.getLastRow() >= targetRow) {
          protection.remove();
        }
      });
      
      // 执行行删除操作
      range.deleteCells(SpreadsheetApp.Dimension.ROWS);
    }
  }
}

关键修改点 #

• 提前获取工作表所有范围保护，避免循环内重复调用提升效率
• 在删除行前，检查每个保护的范围是否覆盖当前要删除的行，若覆盖则移除该保护
• 确保删除操作前完成保护清理，从根源避免#REF残留

方案二：独立清理无效保护脚本 #

如果已经存在大量#REF状态的无效保护，可以运行以下脚本一次性清理：

function cleanupInvalidProtections() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var allProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  
  allProtections.forEach(function(protection) {
    try {
      // 尝试获取保护范围的A1标识，无效保护会抛出错误
      protection.getRange().getA1Notation();
    } catch (e) {
      // 捕获到错误说明保护已失效（如#REF），直接移除
      protection.remove();
    }
  });
}

逻辑说明 #

• 遍历工作表所有范围保护
• 尝试获取保护范围的A1表示，无效保护（如指向已删除单元格的#REF保护）会触发异常
• 捕获异常后立即移除该无效保护

原脚本参考 #

自动保护脚本 #

function onEdit(e){
  if (e.value == null){
    let prot = SpreadsheetApp.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (let i in prot){
      if (prot[i].getRange().getA1Notation() == e.range.getA1Notation())
        prot[i].remove();
    }
  } else {
    let protection = e.range.protect();
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit())
      protection.setDomainEdit(false);
  }
}

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