Queries done in Sheets pulling from BigQuery (permissions help needed)

1
votes

I want to set up scripts in sheets creating several menu options for predefined queries. This way normal bob can get his queries, Tina can get hers, and Fredrick in accounting can get the information he needs without out writing his own queries. They can use a fun menu: screenshot

The data table is set up in BigQuery, and queries work fine using the 'compose query' option. The issue is getting queries written in the script to function in sheets. Here is a nice tutorial by Ido Green on how to go about it. https://greenido.wordpress.com/2013/12/16/big-query-and-google-spreadsheet-intergration/

My problem is getting the permissions to work. I believe I approved the project ID in BigQuery, but I am unsure of how to do this in Drive. This is new to me. Let me know if someone has the step by step. This is the error I get when I try to run the script:

"Exception: No suitable credentials found to access Google Drive. Contact the table owner for assistance."

Thank you for your help

2
google-apps-scriptgoogle-sheetsgoogle-bigquery
Some details you can found here stackoverflow.com/questions/37487101/… – Pavel Kulikov

2 Answers

2
votes

Got the same issue with my Google Apps script. Steps to fix

  1. Enable Google Drive API in Resources -> Advanced Google Services
  2. Enable Google Drive API in Google Developers Console for this project
  3. Run 'DriveApp.getRootFolder();' to get Drive Auth Scope .
1
votes

I think the problem is in the OAuth Scopes.

According to this SO question, If you are using the BigQuery web UI and have not explicitly granted access to Drive, it won't work. For example, the first time I tried to "Save to Google Sheets", the BigQuery UI popped up an OAuth prompt asking me to grant access to my Google Drive. After this it could save the results. Try doing this to make sure your credentials have the Drive scope and then "Save View" again.

If you are using your own code to do this, you should request scope 'https://www.googleapis.com/auth/drive' in addition to the 'https://www.googleapis.com/auth/bigquery' scope you are already using to talk to BigQuery.

Also check this SO question and this community page for more information.