How to ignore already protected sheets with script?

Question

Every day create 2-3 sheets but a minimum of one these ranges are protecting ["B3:U27", "W3:AP27", "B29:U33", "W29:AP33"]

I reduced the 42 ranges to these 4 ranges to make it faster but still in 1 minute it can protect about 8 files the problem is that in a few months it can grow more then 100 files which would take me up to the 6 minute timeout limit and that would interrupt the script.

This is the script I am currently using. I wonder if it could be modified in some way to ignore the already protected sheets?

function main(){ //Main function to run
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var disregard = ["List", "Data", "Template"]; //ADD SHEET NAMES HERE THAT YOU WANT TO BE DISREGARDED

  for(var x=0; x<sheets.length; x++){
    if(disregard.some(data => sheets[x].getName().includes(data))){ 
      //E.g. Disregard any sheet names added on the "disregard" array
    }else{
      unlockCertainRanges(sheets[x]);
    }
  }
}

function unlockCertainRanges(currentSheet){ //Function to unlock certain ranges on your spreadsheet
  var sheet = currentSheet;
  // Remove all range protections in the spreadsheet
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    protection.remove();
  }

  var protection = sheet.protect();
  //restrict editors to owner
  protection.getRange().getA1Notation();
  var eds = protection.getEditors();
  protection.removeEditors(eds);

  //set unprotected ranges
  var ranges = protection.getUnprotectedRanges();
  var data = ["B3:U27", "W3:AP27", "B29:U33", "W29:AP33"]; // ADD YOUR RANGES HERE
  data.forEach(res => { //LOOPS INTO EVERY ARRAY CONTAINING SPECIFIC RANGES
    ranges.push(sheet.getRange(res));
    protection.setUnprotectedRanges(ranges); //REMOVES THE PROTECTION ON THE RANGE
  });
}

Can it be something that has already been protected or have a padlock on them not to be touched?
I tried to find a way to retrieve the names of the already protected sheets.
I mean something like getSheetName() but for the protected ones.

Or maybe put it in the exceptions if there is already such protection on this description?

setDescription('Already protected');

I don't have much experience in coding; I found a very similar question but I didn't understand much of the code

Does anyone have an idea?

Answer 1

I believe what @MetaMan simply means is that, you need to check first if the sheet does contain a protected range. See code below.

Code:

function main() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  // Get list of sheets protected
  var protections = SpreadsheetApp.getActiveSpreadsheet().getProtections(SpreadsheetApp.ProtectionType.SHEET);
  var protectedSheets;
  // If protections isn't set, initialize as empty array
  if (protections)
    protectedSheets = protections.map(protection => protection.getDescription());
  else
    protectedSheets = [];

  var disregard = ["List", "Data", "Template"]; //ADD SHEET NAMES HERE THAT YOU WANT TO BE DISREGARDED

  for (var x = 0; x < sheets.length; x++) {
    if (disregard.some(data => sheets[x].getName().includes(data))) {
      //E.g. Disregard any sheet names added on the "disregard" array
    } else {
      // If protectedSheets doesn't include the name, process the sheet
      if (!protectedSheets.includes(sheets[x].getName()))
        unlockCertainRanges(sheets[x]);
    }
  }
}

function unlockCertainRanges(currentSheet) {
  Logger.log("\"" + currentSheet.getName() + "\" is being processed");
  var sheet = currentSheet;
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    protection.remove();
  }

  // set names of sheets as description for future checks
  var protection = sheet.protect().setDescription(currentSheet.getName());
  //restrict editors to owner
  protection.getRange().getA1Notation();
  var eds = protection.getEditors();
  protection.removeEditors(eds);

  //set unprotected ranges
  var ranges = protection.getUnprotectedRanges();
  var data = ["B3:U27", "W3:AP27", "B29:U33", "W29:AP33"]; // ADD YOUR RANGES HERE
  data.forEach(res => { //LOOPS INTO EVERY ARRAY CONTAINING SPECIFIC RANGES
    ranges.push(sheet.getRange(res));
    protection.setUnprotectedRanges(ranges); //REMOVES THE PROTECTION ON THE RANGE
  });
}

// function to delete all existing protections
function deleteAllProtections() {
  var protections = SpreadsheetApp.getActiveSpreadsheet().getProtections(SpreadsheetApp.ProtectionType.SHEET);
  protections.forEach(protection => protection.remove());
}

Note:

• Note that the first run will need to run deleteAllProtections() so all sheets will have no protections for the first time. Succeeding runs will now skip those sheet with protections.

Reference:

• getProtections