0

I need help for a code to protect Google sheet and giving editing access to multiple users the ability to edit the protected sheet. I have tried the code below but I was able to allow myself to edit but I was not able to add in other email addresses to permit them to edit the sheet.

function protectRange() {
// Protect the active sheet except 1:1 & A:A, then remove all other users from the list of editors.
var sheet = SpreadsheetApp.getActiveSheet();
var protection = sheet.protect().setDescription('Protected sheet');
protection.setUnprotectedRanges([sheet.getRange('1:1'),sheet.getRange('A:A')]);

// Ensure the current user is an editor before removing others. Otherwise, if the user's edit
// permission comes from a group, the script throws an exception upon removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}
}

I have tried to change the line for protection.addEditor(me); and add an array protection.addEditors([me,'abc@gmail.com','xyz@gmail.com']);

but it does not work. What can I do?

Thank you so much. Cheers

TheMaster
  • 45,448
  • 6
  • 62
  • 85
LM Ong
  • 1
  • 1

1 Answers1

0

Althogh I'm not sure whether I could correctly understand your situation, about I have tried to change the line for protection.addEditor(me); and add an array protection.addEditors([me,'abc@gmail.com','xyz@gmail.com']);, when I saw your script, even when you use protection.addEditors([me,'abc@gmail.com','xyz@gmail.com']) instead of protection.addEditor(me), by protection.removeEditors(protection.getEditors()), editors are removed. I thought that this might be the reason of your issue.

In this case, how about the following modification?

From:

var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());

To:

var me = Session.getEffectiveUser();
protection.removeEditors(protection.getEditors());
protection.addEditors([me.getEmail(), 'abc@gmail.com', 'xyz@gmail.com']);
Tanaike
  • 181,128
  • 11
  • 97
  • 165