Enable Google Sheet Protection via Google Script

Question

I have been searching for a solution to a Sheet Protection issue, I have a Sheet that includes automated scripts to write data into the Sheet below from some fields at the top. I want the user to be able to provide the data in the fields at the top and then to run a script that adds the data below. The script to add the data works fine for the owner of the sheet if Protection is enabled, but fails for any user that has Edit rights as the Protection cannot be cleared by script for the data to be written to the bottom of the sheet.

https://developers.googleblog.com/2015/02/control-protected-ranges-and-sheets-in.html

I found this code from a Google blog post a few years ago and have tried variations without success....is this type of approach just not possible with Google Sheets and Google Script ?

Answer 1

Unfortunately, that is not currently possible to run a bound script as the owner without using a workaround. Scripts can only be run as the owner when they use Triggers or when you make a standalone Script Web App. You can see this for more information.

This should bypass fooling around with scripting protection ranges!

---

The Workaround!

There is a way you can get around this by creating a web app so that your bound script talks to the web app which runs on the spreadsheet. See this answer for more information.

So the process looks like this: User clicks a button that runs a function on a bound script. This function makes a web call to a web app that can run a function as the person who created the script.

I would also recommend you pay attention to a comment by Augustine C:

...you may also find it helpful to have a shared secret key saved in your spreadsheet and then verify it using the backend webapp script, or to perhaps verify that the recipient of the email is, in fact, also an editor of your Google Sheet.