How to search for and delete named protected range in Google Sheets?

Question

I am am writing a script in Google Sheets that selectively locks a large block of cells, and each day will need to include one more row of cells into that same protected range.

The script is working so far, in terms of locating the rows that need protection and creating a new protected range each time it is run -- however every time it is run (which I want to be onOpen) it creates another protected range, and another, and another. I've named the protected range "History" but I cannot figure out a way to, upon the next run, unprotect just that range and then protect a new range.

There are other protected cells in the sheet, so I cannot simply remove all protections and then proceed - I need to be able to delete just this one, specifically, and go from there. Since the range of cells being locked is going to keep changing, I need to be able to have the script look for this one range specifically.

Any ideas are appreciated!

Effort? Yes - I've been trying to figure it out, but since this is my first ever time doing anything with sheets or google script, I'm finding it difficult. I thought this was a place for help... — David Centeno

Stephen Pepe Stephen Pepe · Accepted Answer · 2017-02-21T15:50:09

To update an existing protection, you first have to "get" it. I can't find an easy way to do this directly, but you can get all of the protections on the sheet (or in the entire spreadsheet) and loop through them to find the one you want.

var protections = ws.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var p in protections) {
  if (protections[p].getDescription() == 'History') {
    var protection = protections[p];
    break;
  }
}

Now you can use the protection.setRange method to update its range without creating a new protection:

var rangeNew = ws.getRange('A1:C3');
protection.setRange(rangeNew);

Here's the documentation for the sheet getProtectionsType and protection setRange methods. Note that it could be even better to do all this with named ranges, which you can get and update in the same way as protections.

How to search for and delete named protected range in Google Sheets?

1 Answers