Run Google script without authorization

Question

I remember finding a solution a while back, but can't remember where/how to add a button that runs a script but doesn't require authorization. The reason for this is specific to a current client's Sheet, as I have to write a lot of code to get all of the functions they want, but these are people that do not understand scripting, web apps, Sheets, anything really, so I want to allow them to click buttons to run the code but NOT ask them for authorization, even once. Is this still possible?

I figure I could deploy my script as a web app, but if I call it in a script with urlFetch, I assume it will still ask the user for authorization. I have read through a lot of articles, including this one, but I can't quite figure out how to do this. Can anyone point me in the right direction?

To be clear, the bottom line is that I have some code, in a function, for example, called "clearData", and it is tied to a "Clear" button. But as it is, every new user has to first authorize the script through those dubious Google pop-ups, and my client(s) just won't understand that. So instead, I'd like to deploy my script or whatever is necessary to tie it to the Sheet, and allow people to run it without warnings.

I have a lot of time on this, but I really need to figure out a way to do it and have come up empty to far through Google and Stack Overflow, so any and all help is greatly appreciated!

Answer 1

As this question has been viewed a lot, I wanted to add the workaround I have just recently started doing. This is not perfect for all situations, but it works for anything that can wait 60 seconds to be updated.

The trick is to keep the scripts in a backend spreadsheet/project and have it write to the protected sheet(s). I usually put a checkbox on the protected sheet, and on the backend sheet, the script runs every minute and opens the protected sheet (openById) and looks for a checked check-box. It then runs the update they are asking for and clears the checkbox. In this way, all authorizations are on my private backend Sheets, and the codes just modify my clients'/users' Sheets. As mentioned, the big limitation is that it can take up to 60 seconds for the update to trigger, since the most frequent I can trigger the script is every 60 seconds.

To lessen the time a little, when needed, I add a script to call the main script onEdit on the backend, and have the backend importRange the checkbox. Using =if(now()=0,,... in front of the importRange, and setting now, rand, etc. to update every minute and on change, it usually means the target Sheets update within 30 seconds, since there are a number of ways the main script can be triggered.

Answer 2

If you script has a single statement that requires authorization to run, calling any function from a custom menu or from a function assigned to an image will require the the user to authorize the script, so in order to be able to achieve what your looking for it's very likely that you will need to have two scripts:

1. A bounded script having the function to be assigned to the image. Please make sure that the script doesn't include any oauth scope that requires authorization to run.

2. A separate script having the code that require authorization to run. This script should have a doPost function and should be published as a web app set to run as you and allowing that anyone even anonymous be able to execute it.

On the first script you should use a the URL Fetch service to do the http post request.

Related

• Working with Protected Sheets and Ranges "View Only" mode