Monday, 8 April 2019

Protect a named range in a Google sheet

The following Google Apps Script code is from some recent learning I have been doing when asked about locking-down certain areas of a sheet. I knew of named ranges and protecting cells but not quite that it could be done with apps script.

The user was looking towards restricting access to only certain rows or sheets that particular individuals needed to edit, in a file shared with multiple people. Over the next couple of blog posts I have dabbled with creating/finding/removing certain aspects of named range protections. The below code is designed to protect a named range in a sheet and log the name of the protected range.

Once we perform the usual getting of the spreadsheet and range we want to manage, we can use the protect feature of apps script:
var protection = range.protect().setDescription('Sample protected range');
The above line protects our range of cells by just mirroring the permissions set on the spreadsheet file itself in Google drive, as well as creating a description for us. So in effect this is not a great bit of protection but it at least shows us the initial concept before we delve deeper.

The following line creates a named range for our cells - studentDetails - so they are easier to call upon in future, and then associates it with the above protection:
ss.setNamedRange('studentDetails', range);
So there we have it a simple piece of code to create a protected named range in a Google sheet.

Protect a named range in a Google sheet.xlsx

No comments:

Post a comment