如何保护Google工作表中每个特定用户的范围? [英] How to protect ranges per specific users in google sheet?

问题描述

我正在尝试使用Google脚本针对其工作簿中的每个工作表对所有工作表和特定范围应用保护(并且以后可以通过触发器将其应用于新添加的工作表)

I am trying to apply protection on all sheets and certain ranges using a google script, for every sheet in its workbook (and can be applied on new added sheets via a trigger later)

问题是，在保护整个工作表免受任何人攻击后，我想为不同的用户分配不同的范围，但是人们可以对其范围进行编辑！

我似乎不知道为什么它不起作用..相反，它起作用，允许所有用户编辑除受保护范围之外的所有范围.

I can't seem to know why it doesn't work.. It works oppositely, allowing all users to edit all ranges but the protected ones.

我希望第一个编辑器能够编辑他的QC_Range，第二位能够编辑其PLN_Range的编辑器并限制所有其他单元格和整个工作表的编辑访问权限(仅使其成为视图).

function Sheet_Ranges_Protection(){

  var Veranda_Test = SpreadsheetApp.openById("Sheet ID");
  //var workbookB = SpreadsheetApp.getActiveSpreadsheet();

  var Veranda_Sheets = Veranda_Test.getSheets();
 for(var SheetNumb = 0; SheetNumb < Veranda_Sheets.length; SheetNumb++) 
  {          
    var Shprotection = Veranda_Sheets[SheetNumb].getProtections(SpreadsheetApp.ProtectionType.SHEET);

    var QC_Range         = Veranda_Sheets[SheetNumb].getRange("E1:G5");
    var PLN_Range        = Veranda_Sheets[SheetNumb].getRange("A1:C5");

    if (Shprotection == true)
      SheetNumb++;

    else if (Shprotection == false)
    {
      var Shprotection = Veranda_Sheets[SheetNumb].protect().setDescription('Sheet Protection');    

      var Rangesprotection = Veranda_Sheets[SheetNumb].getProtections(SpreadsheetApp.ProtectionType.RANGE);

      if (Rangesprotection == false)
      { 
        var QCprotection = QC_Range.protect().setDescription('QC Protection');  
        var me = Session.getEffectiveUser(); 
        QCprotection.addEditor(me); 
        QCprotection.removeEditors(QCprotection.getEditors()); 

        if (QCprotection.canDomainEdit())
        { 
          QCprotection.setDomainEdit(false); 
          QCprotection.addEditors(['[e-mail]']); 
        }

        var PLNprotection = PLN_Range.protect().setDescription('PLN Protection');
        var me = Session.getEffectiveUser();  
        PLNprotection.addEditor(me); 
        PLNprotection.removeEditors(PLNprotection.getEditors()); 

        if (PLNprotection.canDomainEdit()) 
        { 
          PLNprotection.setDomainEdit(false);
          PLNprotection.addEditors(['[e-mail]']);
        }

      }
      else
        SheetNumb++;

    //  Shprotection = true;
    //  Rangesprotection = true;

      // 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.

    }
  }
}

推荐答案

我重新编写了您的代码，我认为它可以完成您现在所假装的工作.在这种情况下，我不会进行纸张保护，而是分别保护所有范围:

I've reworked your code and I think it does what you pretend now. In this case, I don't make a sheet protection but protect all the ranges separately:

function Sheet_Ranges_Protection() {
  var Veranda_Test = SpreadsheetApp.openById("Sheet ID");
  var Veranda_Sheets = Veranda_Test.getSheets();

  for(var SheetNumb = 0; SheetNumb < Veranda_Sheets.length; SheetNumb++) {

    var me = Session.getEffectiveUser();

    // Define ranges that will be protected for everyone
    var range1 = Veranda_Sheets[SheetNumb].getRange(6, 1, Veranda_Sheets[SheetNumb].getMaxRows(), Veranda_Sheets[SheetNumb].getMaxColumns());
    var range2 = Veranda_Sheets[SheetNumb].getRange(1, 8, 5, Veranda_Sheets[SheetNumb].getMaxColumns());
    var range3 = Veranda_Sheets[SheetNumb].getRange(1, 4, 5);
    var ranges = [range1, range2, range3];

    // Set protection for all the sheet minus QC/PLN ranges
    for(var i = 0; i < ranges.length; i++) {
      var rangeProtection = ranges[i].protect().setDescription('Range protection');
      rangeProtection.addEditor(me);
      rangeProtection.removeEditors(rangeProtection.getEditors());
      if (rangeProtection.canDomainEdit()) {
        rangeProtection.setDomainEdit(false);
      }
    }

    var QC_Range         = Veranda_Sheets[SheetNumb].getRange("E1:G5");
    var PLN_Range        = Veranda_Sheets[SheetNumb].getRange("A1:C5");

    // Set protection for QC range
    var QC_protection = QC_Range.protect().setDescription('QC protection');
    QC_protection.removeEditors(QC_protection.getEditors());
    QC_protection.addEditor('[e-mail]');
    if (QC_protection.canDomainEdit()) {
      QC_protection.setDomainEdit(false);
    }

    // Set protection for PLN range
    var PLN_protection = PLN_Range.protect().setDescription('PLN protection');
    PLN_protection.removeEditors(PLN_protection.getEditors());
    PLN_protection.addEditor('[e-mail]');
    if (PLN_protection.canDomainEdit()) {
      PLN_protection.setDomainEdit(false);
    }    
  }
}

我希望这对您有用！

这篇关于如何保护Google工作表中每个特定用户的范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！