BigQuery Simba ODBC Connection - Google Drive OAuth Error

1
votes

I am trying to export the results of a BigQuery view to Excel using the Simba ODBC Connection. The view I am querying references tables which are linked to Google Sheets, however I can't get the permissions to pass through to the Google Sheet, getting the error:

[Simba][BigQuery] {20} Query execution failed: Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

The Google Sheet was created using the same Google account that has access to BigQuery and ODBC connection has the "Request Google Drive scope access" setting ticked.

Does anyone have any ideas about how I can resolve this?

Alan

1
google-sheetsodbcgoogle-bigquerygoogle-oauthsimba
please include your authentication code. – DaImTo
My confirmation code is: 4/DTNpOIBpd9PeSpz0BYz49z8HCeGpLqAldBbgFU4YxqU – MVData
It is most possible that the 'googleapis.com/auth/drive.readonly' scope is missing in the credentials of your request, so please take a look at the post at stackoverflow.com/questions/37381286/… to see if you've properly done the setup. – JL-HaiNan
Thanks JL-HN I have enabled the Google Drive API and given the [email protected] account access to the underlying Google Sheets, but am still not getting any luck. As I am using my personal user account, rather than a service account, do I still need to provide the 'googleapis.com/auth/drive.readonly'; scope somehow? If so, how? – MVData
Since you are using your personal account, I recommend that you use “gcloud auth login --enable-gdrive-access” [1] command to enable Google Drive access. I hope this works for you? [1] cloud.google.com/sdk/gcloud/reference/auth/login – Katayoon

1 Answers

0
votes

Have found this exact error message trying to select from a GoogleSheet via BigQuery External Datasource table using Windows ODBC DSN connection from Excel 2016 and 2010.

Issue I have tracked down to the version of the ODBC driver available to install from google cloud platform download page.

  • 2.1.10 Doesn't work
  • 2.1.11 Does work
  • 2.1.20 Doesn't work

All three times created a User DSN - Data Source, using the "Simba ODBC Driver for Google BigQuery" and configured using:

  • O Auth Mechanism "User Authentication"
  • Ticking "Request Google Drive scope access"
  • Clicking Sign In ...
  • Copy code from Google sign in page, and paste into Confirmation Code to generate the Token.

The versions that don't work basically only request Google Cloud Platform permission for the BigQuery Tools and Don't request the additional Google Drive scope access even though the Request box is ticked.