How to remove a range protection on a googlesheet for a user who can't edit?

0
votes

I need your help. I have a spreadsheet with multiple sheets. One sheet has a range protected which is only editable by me. My problem is that I want users to run a script which edit cells in this range. I don't want them to edit it manually, only automatically.

I tried to add as editor the user and then remove the user from the editor list at the end of the script. I also tried to remove the protection.

However, when I run the script not as the owner it always gives an error message "You do not have the necessary permissions to edit the range"

Do you have any idea of how shoul I proceed?

Victor

2
google-apps-scriptprotection
Are the users whom you want to edit the sheet users of your domain? – ziganotschka
Yes, users and I are on the same domain. – Victor_exo
Can you share the code you used to add editor and remove protection? – Aaron Dunigan AtLee
How is the user running your code? From a menu, or via some trigger? – Aaron Dunigan AtLee

2 Answers

2
votes

You can use a Service account

  • share your spreadsheet only with the service account instead of the users
  • Write your script in such a way that it runs as the service account
  • Provided that your service account has domain-wide delegation, every user can write the script and make changes to the file through the service account
  • See here how to implement a service account in Google Apps Script
  • Have a look at this case which is similar to your
2
votes

If the script runs under the user's account, there is no way for the script to remove protections or give the user access to the protected range, because this is equivalent to the user granting herself edit access to a protected range that she does not have permission to edit. If you don't want to give the user edit access beforehand, the only way for the script to edit the protected range is by running the script under your account.

According to the documentation, you can run the script under your account by executing it from an installable trigger you created (such as an edit or change trigger), or from a web app set to "Execute as me."