onEdit脚本中移除并重新添加保护以修复自动排序失效问题

当前情况 #

作为表格所有者，我创建了一个共享电子表格，所有获取链接的用户都拥有编辑权限。部分工作表设置了部分保护，仅少数列未受保护，允许编辑者修改对应单元格的值。我编写了onEdit函数，希望用户编辑单元格时，自动对工作表指定范围进行排序。

问题 #

由于工作表存在部分保护，自动排序功能无法生效。需要在脚本中添加逻辑，让onEdit触发时先解除当前工作表的保护，排序完成后重新添加保护（保留指定列未受保护），且不能让编辑者失去原有权限。

已尝试的无效脚本 #

function onEdit() {

if (e.range.columnStart == 3 && e.range.getValue() != '') {
var sheets = ["FASHION NL", "FASHION BE","KIDS & UNDERWEAR BNL" ,"NEW BUSINESS BNL" ,"SPORTS & SHOES BNL", "HD&E BNL"]; // Please set your expected sheet names.
var sheet = e.range.getSheet();
if (sheets.includes(sheet.getSheetName())) {
var range = sheet.getRange("A5:bY600");

var spreadsheet = SpreadsheetApp.getActive();
var allProtections = 
spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.SHEET);
var protection = allProtections[0];
protection.remove();

range.sort({ column: 11, ascending: true });
e.source.toast('Sort complete.');
    }
  }
var protection = spreadsheet.getActiveSheet().protect();
protection.setUnprotectedRanges([spreadsheet.getRange('AK:AK'), 
spreadsheet.getRange('BN:BN')])
var editors = SpreadSheet.getEditors();
for (var i = 0; i < editors.length; i++) {
SpreadSheet.removeEditor(editors[i])
     }
}

问题说明 #

脚本无报错信息，但无法完成指定数据范围的排序。触发条件为编辑第3列（C列），仅针对上述指定名称的工作表执行。

修正后的脚本及说明 #

原脚本存在几个核心问题：未传入事件对象e、变量拼写错误、保护重置逻辑位置错误、错误移除编辑者权限。以下是修正后的可用脚本：

function onEdit(e) {
  // 校验触发条件：编辑第3列且单元格非空
  if (!e || e.range.columnStart !== 3 || e.range.getValue() === '') return;

  const targetSheets = ["FASHION NL", "FASHION BE","KIDS & UNDERWEAR BNL" ,"NEW BUSINESS BNL" ,"SPORTS & SHOES BNL", "HD&E BNL"];
  const sheet = e.range.getSheet();
  
  // 校验当前工作表是否在目标列表中
  if (!targetSheets.includes(sheet.getSheetName())) return;

  const sortRange = sheet.getRange("A5:BY600");
  const unprotectedColumns = [sheet.getRange('AK:AK'), sheet.getRange('BN:BN')];
  
  // 获取当前工作表的保护规则
  let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
  let originalProtection = protections.length > 0 ? protections[0] : null;
  
  try {
    // 移除现有保护
    if (originalProtection) {
      originalProtection.remove();
    }
    
    // 执行排序操作
    sortRange.sort({ column: 11, ascending: true });
    e.source.toast('排序完成');
  } finally {
    // 重新添加保护（无论排序是否成功，确保工作表恢复保护状态）
    const newProtection = sheet.protect();
    // 设置未受保护的列
    newProtection.setUnprotectedRanges(unprotectedColumns);
    
    // 保留原"获取链接即可编辑"的权限
    newProtection.setDomainEdit(true);
    newProtection.removeEditors(newProtection.getEditors());
    newProtection.setWarningOnly(false);
  }
}

关键修正点 #

1. 事件对象传递：onEdit函数必须接收e参数，才能正确获取编辑上下文
2. 权限保留：通过setDomainEdit(true)和移除指定编辑者，确保原有的链接共享编辑权限不丢失
3. 安全逻辑：使用try...finally确保即使排序出错，工作表也会重新恢复保护状态
4. 逻辑位置修正：保护重置逻辑仅在目标工作表中执行，避免影响其他工作表
5. 拼写与格式修正：修正SpreadSheet拼写错误、排序范围的大小写问题

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