如何从受保护的单元格中删除编辑器或永久保护Google表格中的单元格

3 回答

• 3

  为此，您可以编写脚本函数来设置保护范围并为工作表添加编辑器 .

  请检查示例应用程序脚本代码，以便为下表中的范围添加保护：

  function addProtection()
  {
  
  // Protect range A1:B10, then remove all other users from the list of editors.
   var ss = SpreadsheetApp.getActive();
   var range = ss.getRange('A1:B10');
   var protection = range.protect().setDescription('Sample protected range');
  
  // var me = Session.getEffectiveUser();
    // array of emails to add them as editors of the range
   protection.addEditors(['email1','email2']);
    // array of emails to remove the users from list of editors 
   protection.removeEditors(['email3','email4']);
  }
  

  希望有所帮助！

  回复于 2024-04-27T22:24:21+08:00
• 3

  添加@ KRR的答案 .

  我将脚本更改为动态 .

  function setProtection() {
    var allowed = ["example@gmail.com,exmaple2@gmail.com"];
    addProtection("Sheet1","A1:A10",allowed);
  }
  
  function editProtection(sheetname,range,allowed,restricted) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetname);
    var range = sheet.getRange(range);
  
    //Remove previous protection on this range
    var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0;i<protections.length;i++) {
      if (protections[i].getDescription() === sheetname + range){
        protections[i].remove();
      }
    }
  
    //Set new protection
    var protection = range.protect().setDescription(sheetname + range);
  
    // First remove all editors
    protection.removeEditors(protection.getEditors());
  
    // Add array of emails as editors of the range
    if (typeof(allowed) !== "undefined") {
      protection.addEditors(allowed.toString().split(","));
    }
  }
  

  您可以根据需要添加任意数量的选项，并使它们在onOpen上运行 . 设置变量并根据需要多次调用editProtection .

  您可以从电子表格编辑器动态获取电子邮件 .

  此外，您可能还想添加另一个脚本来保护整个工作表并将您设置为所有者 . 希望这可以帮助 .

  回复于 2024-04-27T22:24:21+08:00
• 0

  它 MUST 作为 SCRIPT 和 NOT 作为 add-on 运行 .

  如果您已经锁定了工作表并进行了例外处理，则可以轻松使用Google的示例代码 . 我们可以使用for循环来查找所有工作表和名称 . 然后在脚本中添加一个按钮以在开始时加载 .

  function FixPermissions() {
    // Protect the active sheet, then remove all other users from the list of editors. Get all sheets in the workbook into an array
   var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  //Use a for loop to go through each sheet and change permissions and label it according to the name of the sheet
    for (var i=0; i < sheets.length; i++) {
      var name = sheets[i].getSheetName()
      var protection = sheets[i].protect().setDescription(name);
      // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
      // permission comes from a group, the script will throw an exception upon removing the group.
      var me = Session.getEffectiveUser();
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    } 
  }
  
  
  //A special function that runs when the spreadsheet is open, used to add a custom menu to the spreadsheet.
  
  function onOpen() {
    var spreadsheet = SpreadsheetApp.getActive();
    var menuItems = [
      {name: 'Fix Permission', functionName: 'FixPermissions'}
    ];
    spreadsheet.addMenu('Permissions', menuItems);
  }
  

  现在，在菜单栏中，当您重新加载/加载标有 Permissions 的电子表格时，您会看到一个新项目

  回复于 2024-04-27T22:24:21+08:00