如何查看Google表格/范围保护状态? [英] How to check on google sheet/ranges protection status?

问题描述

我使用@OMila辅助工具编写了一个代码，以限制某些用户的某些范围，同时保护工作表中整个剩余范围免受其编辑..如果要循环迭代，我想检查工作表/范围的保护状态，如果它是受保护的==>迭代++(请检查下一页)，如果不受保护，请运行脚本并保护范围.目的是，当某些人制作新工作表时，我希望脚本通过触发器自动运行，但是当工作表数增加时，每个电子表格的执行时间将增加，并且可能会达到Google报价的限制，因此我需要通过放置if条件来检查工作表保护状态并按照上述说明进行操作的一种优化的执行脚本的方法.这是代码:

I made a code with @OMila aid to restrict some ranges for certain users while protecting the whole remaining ranges in sheet from their editing.. I want to check on the protection status of a sheet/range per for loop iteration, if it is protected ==> iteration++ (check next sheet), if not protected, run the script and protect ranges. The purpose is, when certain people make new sheets, I want the script to run automatically via a trigger, but when the number of sheets increase the execution time will increase per spreadsheet and will probably hit google quotations limit, so i need to make an optimized way to execute the script by putting an if condition to check the sheet protection status and do as described before. this is the code:

  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);
    }    
    }
    }

推荐答案

在创建新保护之前，可以使用getProtections()函数检查哪些保护已经到位.

You could use the getProtections() function to check which kinds of protections are already in place before creating new ones.

由于要专门使用范围创建保护，因此可以使用getProtections(SpreadsheetApp.ProtectionType.RANGE)仅获取脚本创建的保护(它们都是范围，而不是工作表范围的保护).

Since you are creating your protection with ranges exclusively, you could use getProtections(SpreadsheetApp.ProtectionType.RANGE) to fetch only the protections created by your script (all of them are Ranges, not sheets-wide protections).

您可以假设，如果创建了第一个保护，则已经创建了所有保护.所以代码看起来像这样:

You could assume that, if you have created the first protection, you have created all of them. So the code would look like this:

var protections = Veranda_Sheets[SheetNumb].getProtections(SpreadsheetApp.ProtectionType.RANGE);
if (protections.length==0) {
   //add protections here
}

如果这不是一个安全的假设，则可以验证缺少哪些保护，然后像下面这样创建它们:

If that's not a safe assumption, you could verify which protections are lacking, and create them later like this:

var protections = Veranda_Sheets[SheetNumb].getProtections(SpreadsheetApp.ProtectionType.RANGE);
var protectionNames = [];
for (var i=0; i<protections.length; i++) {
    protectionNames.push(protections[i].getDescription());
}

if (!protectionNames.includes('<name of protection i am about to create>') {
    //create protection '<name of protection i am about to create>'
} //else skip;

希望这会有所帮助！

这篇关于如何查看Google表格/范围保护状态?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！