Googlesheet editing permission of specific range to specific gmail id

Question

I want to allow editing of specific range to specific gmail id. For example A1 contains gmail id of Tom and I want to allow Tom to edit B1 to G1, and A2 contains gmail id of James and I want to allow James to edit B2 to G2. How Should I do that. There are 400 different gmail id of different persons I want to allow them to edit specific ranges according to the gmail id present in column A. How should I do that. Manually doing this is very time consuming.

Answer 1

How to protect specific cells on a spreadsheet

You can use the following sample code that protects the cells from A1 to B10.

// Protect range A1:B10, then remove all other users from the list of editors.

var ss = SpreadsheetApp.getActive();
var range = ss.getRange('A1:B10');
var protection = range.protect().setDescription('user name or ID just as a reference');

var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}

Since you will have the list of IDs on the same spreadsheet, you can modify the code and use a loop to grab the ID from each user and set the protected range and user according to your needs.

Something really important for this to work is that if you run this script you need to be the owner of the spreadsheet, otherwise you may get an error.

You will also need to have the users' email address along with the ID to set them as editors.