Google Apps Script单元格编辑权限控制问题求助

实现Google Apps Script逻辑：

• 单元格为空时，所有人可编辑
• 单元格被编辑后，仅该编辑者与表格所有者可编辑
  所有用户均为匿名状态，无法获取邮箱，目标是避免用户互相修改他人单元格，让首个编辑者拥有该单元格控制权。

现有问题 #

• 功能仅部分生效：所有者的单元格无法被他人编辑，但普通用户仍可覆盖他人已编辑的单元格
• 非所有者用户触发脚本时会报错：

Error   Exception: You can't remove yourself as an editor.
    at onEdit(Code:25:14)

原修改代码 #

// Test it with colors
// var edittedBackgroundColor = "RED"; // makes the change visible, for test purposes
// var availableBackgroundColor = "LIGHTGREEN"; //  makes the change visible, for test purposes

function onEdit(e) {
  Logger.log(JSON.stringify(e));
  var alphabet = "abcdefghijklmnopqrstuvwxyz".toUpperCase().split("");
  var columnStart = e.range.columnStart;
  var rowStart = e.range.rowStart;
  var columnEnd = e.range.columnEnd;
  var rowEnd = e.range.rowEnd;
  var startA1Notation = alphabet[columnStart-1] + rowStart;
  var endA1Notation = alphabet[columnEnd-1] + rowEnd;
  var range = SpreadsheetApp.getActive().getRange(startA1Notation + ":" + endA1Notation);

  if(range.getValue() === "") {
    Logger.log("Cases in which the entry is empty.");
    if(typeof availableBackgroundColor !== 'undefined' && availableBackgroundColor) 
      range.setBackground(availableBackgroundColor)
    removeEmptyProtections();
    return;
  }

  // Session.getActiveUser() is not accesible in the onEdit trigger
  // The user's email address is not available in any context that allows a script to run without that user's authorization, like a simple onOpen(e) or onEdit(e) trigger
  // Source: https://developers.google.com/apps-script/reference/base/session#getActiveUser()

  var protection = range.protect().setDescription('Cell ' + startA1Notation + ' is protected');
  if(typeof edittedBackgroundColor !== 'undefined' && edittedBackgroundColor)
    range.setBackground(edittedBackgroundColor);

  // Though neither the owner of the spreadsheet nor the current user can be removed
  // The next line results in only the owner and current user being able to edit

  protection.removeEditors(protection.getEditors());
  Logger.log("These people can edit now: " + protection.getEditors());

  // Doublecheck for empty protections (if for any reason this was missed before)

  removeEmptyProtections();
}

function removeEmptyProtections() {
  var ss = SpreadsheetApp.getActive();
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    if(! protection.getRange().getValue()) {
      Logger.log("Removes protection from empty field " + protection.getRange().getA1Notation());
      protection.remove();
    }
  }
  return;
}

function isEmptyObject(obj) {
    for(var prop in obj) {
        if(obj.hasOwnProperty(prop))
            return false;
    }
    return JSON.stringify(obj) === JSON.stringify({});
}

测试场景 #

• PERSON1编辑空单元格：单元格被保护，但触发上述报错
• PERSON2删除该单元格内容：保护被移除，单元格恢复为空，脚本无报错

解决方案 #

问题根源 #

1. protection.removeEditors(protection.getEditors())会尝试移除所有编辑者，包括当前用户，而普通用户无法移除自己，导致报错
2. 未检查单元格是否已有保护，重复创建保护会导致逻辑混乱，进而出现用户可覆盖他人单元格的情况

修改后完整代码 #

// 测试用颜色（取消注释即可生效）
// var edittedBackgroundColor = "RED"; // 标记已编辑的单元格
// var availableBackgroundColor = "LIGHTGREEN"; // 标记可编辑的空单元格

function onEdit(e) {
  Logger.log(JSON.stringify(e));
  var range = e.range;
  var startA1Notation = range.getA1Notation();

  if(range.getValue() === "") {
    Logger.log("处理空单元格场景");
    if(typeof availableBackgroundColor !== 'undefined' && availableBackgroundColor) {
      range.setBackground(availableBackgroundColor);
    }
    removeEmptyProtections();
    return;
  }

  // 检查当前单元格是否已有保护，避免重复设置
  var existingProtections = range.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  if(existingProtections.length > 0) {
    Logger.log("单元格" + startA1Notation + "已有保护，跳过设置");
    return;
  }

  // 创建保护并设置描述
  var protection = range.protect().setDescription('Cell ' + startA1Notation + ' is protected');
  if(typeof edittedBackgroundColor !== 'undefined' && edittedBackgroundColor) {
    range.setBackground(edittedBackgroundColor);
  }

  // 获取所有者邮箱与当前用户邮箱（匿名用户邮箱为空）
  var ownerEmail = SpreadsheetApp.getActiveSpreadsheet().getOwner().getEmail();
  var currentUserEmail = Session.getActiveUser().getEmail();
  
  // 筛选出需要移除的编辑者：排除所有者和当前用户
  var editorsToRemove = protection.getEditors().filter(editor => {
    var editorEmail = editor.getEmail();
    return editorEmail !== ownerEmail && editorEmail !== currentUserEmail;
  });

  // 仅移除非所有者、非当前用户的编辑者
  if(editorsToRemove.length > 0) {
    protection.removeEditors(editorsToRemove);
  }

  Logger.log("当前可编辑者：" + protection.getEditors().map(e => e.getEmail()).join(","));

  removeEmptyProtections();
}

function removeEmptyProtections() {
  var ss = SpreadsheetApp.getActive();
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    if(!protection.getRange().getValue()) {
      Logger.log("移除空单元格" + protection.getRange().getA1Notation() + "的保护");
      protection.remove();
    }
  }
}

function isEmptyObject(obj) {
    for(var prop in obj) {
        if(obj.hasOwnProperty(prop))
            return false;
    }
    return JSON.stringify(obj) === JSON.stringify({});
}

修改说明 #

1. 简化范围获取：直接使用e.range替代手动拼接A1符号，减少代码冗余
2. 新增保护检查：设置保护前先判断单元格是否已有保护，避免重复操作和逻辑冲突
3. 优化编辑者移除逻辑：
   • 获取所有者和当前用户邮箱，排除这两类用户后再移除其他编辑者，避免触发"无法移除自己"的报错
   • 匿名用户的邮箱为空字符串，会自动被排除在移除列表外，确保当前编辑者（匿名）仍能编辑该单元格
4. 保持原有核心功能：保留空单元格自动移除保护、颜色标记等功能

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