Control protected ranges and sheets in Google Sheets with Apps Script
Engineer, Google Sheets
Try Google Workspace at No Cost
Get a business email, all the storage you need, video conferencing, and more.SIGN UP
A few weeks ago, the Google Sheets team introduced improved control over protected sheets and ranges. Developers have told us they're looking for the same power in Google Apps Script — after all, ever since we added data validation to the Spreadsheet service, programmatic control over protected ranges has been the most popular request on the Apps Script issue tracker.
Today, we are excited to give you that granular control.
With the new
Protection class in the Spreadsheet service, your scripts can touch every aspect of range or sheet protection, just like in the new UI. (The older
PageProtectionclass, which had more limited features, will be deprecated, but will stick around in case you need to work with older spreadsheets. The new Protection class only applies to the newer version of Sheets.)
So let's see the new stuff in action. Let's say you want to prohibit anyone other than yourself from editing cells A1:B10:
Or maybe you want to remove all range protections in the whole spreadsheet:
Or perhaps you want to protect an entire sheet, but carve out a small hole in it — an unprotected range within a protected sheet — that others can still edit: