Unable to remove the effective user from editing rights of protected range using script - google sheets

1
votes

In google sheets I'm trying to use a script to make a protected range.

I'd like the script to make a protected range that ONLY the owner can edit, and no one else, however the script has to work when it runs for a user who is NOT the owner.

When I'm logged in as the owner of the spreadsheet and I run the code from script editor, it works fine - it creates a protected range G1:G10 that only the owner can edit.

However when I run the script while logged in as a user who is NOT the owner, the permissions of the protected range allows BOTH the user AND the owner ability to edit the range. I'm aware of this page and this page, on the google developers documentation, however I can't see anything that'll help me.

Here's my code: 

function setProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var owner = ss.getOwner();
  var effectiveUser = Session.getEffectiveUser();
  var range = ss.getRange('G1:G10');
  var protection = range.protect()
  var editors = protection.getEditors();
  protection.removeEditors(editors);
  protection.addEditor(owner);
}

Can anyone help?

1
javascriptgoogle-sheets

1 Answers

0
votes

removeEditors() does not allow the current user to be removed:

Removes the given array of users from the list of editors for the protected sheet or range. Note that if any of the users are members of a Google Group that has edit permission, or if all users in the domain have edit permission, those users will still be able to edit the protected area. Neither the owner of the spreadsheet nor the current user can be removed.

However, you can use an Installable Trigger created by the owner that will run your setProtections() method as the user that created the Trigger [owner] - even if it is triggered by another user.

Example: Installable Trigger onEdit() 

function installedOnEditTrigger (e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

  //Checks if the sheet edit happened in the 7th column "G"
  if (e.range.getColumn() == 7) {
    var range = ss.getRange('G1:G10');
    var protection = range.protect()
    var editors = protection.getEditors();
    //Removes all editors - owners can not be removed
    protection.removeEditors(editors);
  }
}