0

I am trying to remove all protections on a range. I created a script that adds a protection when a user pushes a button. If the user pushes the button twice, it adds the protection twice. I came up with this script, mostly through recording macros, to remove the protection, but it only removes one protection. If a user pushes the button twice, this button only removes one protection and leaves the other protection (thus, leaving the range protected when it shouldn't be). Any advice on how I can remove ALL protections on the range?

var allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  var matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'C4:F30';
  });
  var protection = matchingProtections[0];
  protections.remove();
1

You want to delete all the protections, so you must be looking for a process that repeat itself, as long as the condition is matched. In your case as long there is a protection on C4:F30.

Intuitively:

You want to specify in which sheet you want to work on:

var ss = SpreadsheetApp.getActive()

Then you want to get all protections available in this sheet:

var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);

Finally you want to start the process of checking if they match your condition, and if so the protection should be deleted, then the process should be repeated until there is no more protections matching your condition:

for (var i = 0; i < protections.length; i++) {
    if (protections[i].getRange().getA1Notation() == 'C4:F30') {
        protections[i].remove();
    }
}

FULL CODE:

function RemoveProtections() {

    var ss = SpreadsheetApp.getActive();
    var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protections.length; i++) {
        if (protections[i].getRange().getA1Notation() == 'C4:F30') {
            protections[i].remove();
        }
    }}
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.