google-apps-scriptgoogle-sheetsspreadsheet-protection

How to ignore already protected sheets with script?

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?

Solution

  • 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:

    Reference: