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);
protection.setRangeName('studentDetails');
So there we have it a simple piece of code to create a protected named range in a Google sheet.
Download
Protect a named range in a Google sheet (please use 'File' > 'Make a copy' for your own version).
I am getting an error when I use this script. This is what I have thus far and the error when I run the script.
ReplyDelete1 function createProtectedRange() {
2
3 var ss = SpreadsheetApp.getActive();
4 var sheet1 = ss.getSheetByName('Sheet1');
5
6 var range = sheet1.getRange('A1:H29');
7
8 ss.setNamedRange('description.points', 'A7:H15');
9 protection.setRangeName('educational.background');
10 ss.setNamedRange('educational.background', 'A16:F17');
11 ss.setNamedRange('header', 'a1:h4');
12 protection.setRangeName('header');
13 ss.setNameRange('topics', 'a19:d25');
14 protection.setRangeName('topics', range);
15 Logger.log(protection.getRangeName());
16
17 }
Error: Exception: The parameters (String,String) don't match the method signature for SpreadsheetApp.Spreadsheet.setNamedRange.
createProtectedRange @ Code.gs:8
Note that I do not write script, but I do my best to follow script by others and make it work. I am a secretary that works for a school district with no coding or programming background background since high school RPG programming and that was over 30+ years ago. I am trying to limit sheet editing by everyone in the created sheet (including named editors) without turning their editing off on the Google sheet. If you need additional (such as sheet information), please let me know and I will make it possible for you to make a copy of such sheet. The reason I am looking for protection on this sheet is because it is being used for employee evaluation and at first the employee being evaluated will need to go in the sheet and add their name an select boxes (which I would like to have available for them for a set timeline and then stopped in the background) so I know I will have to search additional coding information for the sheet. If there is an easier way to do this, please let me know. I have been searching to find out if protection of a Google sheet must be set by the "owner" with no luck thus far.
Hi
DeleteIt looks like you are jumbling up a number of items and different ranges. I would recommend taking a copy of my file (link here: http://bit.ly/2EZWO3k) and just trying the one single range like I have, first.
Then try tweaking the 'range' variable in the Script Editor (line 11 in my code) to may be expand it and include a few more cells.
I have not used this code any further but it looks like you ultimately want to protect multiple ranges - which will require unique variable ('var') names.
Kind regards
Phil