Jump to Content
Product Announcements

Control protected ranges and sheets in Google Sheets with Apps Script

February 19, 2015
Sam Berlin

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.)

Code samples

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:

Loading...

Or maybe you want to remove all range protections in the whole spreadsheet:

Loading...

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:

Loading...

Bam! Easy. Hope you find this useful, and happy scripting!


Posted in