Google spreadsheet script authorisation to BigQuery

Question

I have a Google spreadsheet with a script that connects to BigQuery (using this tutorial - https://developers.google.com/apps-script/advanced/bigquery?hl=ar-AE). It adds an extra menu option and users can run the script that executes a query to BigQuery.

It works fine for me and I want to share this spreadsheet with other users (who don't have access to BigQuery itself) and allow them to run my script. When I do it, first they get script authorisation dialog and are able to run the script after that. But BigQuery returns error:

Exception: Access Denied: Job XXX:job_NaMBWMRfbMHygS1n10AQXXXX: The user does not have permission to run a query in project XXXXX

I tried deploying the script as a web app and set it to execute under my account but it didn't change anything.

Is there any solution or workaround?

Answer 1

Unfortunately you can only allow access to scripts running as 'you' if it is running as a web app. The only way to run it as a webapp is if the doGet()/doPost() function is called by the browser. Running the doGet() as a function runs it as a normal script.

You can give access to run queries using the project access controls in developers console. Check out:
https://cloud.google.com/bigquery/access-control

The other option is to use the Oauth2 library and store your personal token in the scripts properties. You then can rewrite your script to use the BigQuery REST interface. There is a template for this at: https://github.com/googlesamples/apps-script-templates/tree/master/sheets-import

Answer 2

The best way that I found is to simply change the configuration of the file appsscript.json. You can find by acessing View > Show project manifest. There you will find an option to change the option executeAs and if you change to USER_DEPLOYING the web app always will run as the person who deployed and most likely have the access to the BigQuery project. You can find the source of the documentation here: https://developers.google.com/apps-script/manifest