How to check on google sheet/ranges protection status?

Question

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('[email protected]');
    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('[email protected]');
    if (PLN_protection.canDomainEdit()) {
      PLN_protection.setDomainEdit(false);
    }    
    }
    }

ZektorH ZektorH · Accepted Answer · 2019-10-02T10:32:52

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

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;

Hope this helps!

How to check on google sheet/ranges protection status?

1 Answers