Protect ranges with google apps script

8
votes

I have a number of sheets which I need to protect except for certain ranges.

Is it possible to do this with a script, as I have several sheets, and many ranges within the sheet which I need to be unprotected so staff can edit the sheets.

The ranges I need to leave unprotected are repetitive, so I'm hoping it's doable. I will fill the ranges I want to remain UNPROTECTED with YELLOW on the example sheet I'll give you.

An example of one of the sheets can be viewed here.

As mentioned, I need to protect the whole sheet, apart from these ranges...

N4:V26,N30:V52,N56:V78 etc etc. Basically the rest of the sheet needs to be protected. The unprotected ranges, the columns stay the same, but each unprotected range is separated by three rows which are protected.

If anyone can help with this script I'd be very grateful as it will save me hours of time manually protecting these ranges over many sheets.

Regards Matt

1
google-apps-scriptgoogle-sheets

1 Answers

7
votes

Yes, you can accomplish this using the Protection class. You would first protect the whole sheet using var protection = sheet.protect(), then unprotect the ranges you want people to be able to edit using protection.setUnprotectedRanges([ranges]), where [ranges] is an array of range objects. You can read more about it in the Google Apps Script Class Protection documentation.