BigQuery Credential Problems when Accessing Google Sheets Federated Table

Question

I defined a table as a federated table which refers to a google sheet. It works perfectly well when I query it from the google bigquery web UI. However, if I try to query it using the Tableau Software BigQuery driver, I get the following error message: No suitable credentials found to access Google Drive. Contact the table owner for assistance.

A few facts about my setup:

1. When I use the BigQuery driver in Tableau, I log in using the same user id that I use when I go into the BigQuery web UI.
2. Queries from Tableau to a native BigQuery table work fine.
3. I enabled the google drive api in my cloud account.
4. The google sheet and google big query account are under the same google user.
5. I have no problem saving a bigquery query as a google sheet under that account.
6. I tried sharing the sheet "to anyone that has the URL" and it does not work.

Wingding

Yes, it's definitely broken now. I hope BQ developers will help us soon, this is the same problem, but you description is more clear. #37381786 – Metheglin

Answer 1

This is something Tableau should fix. The problem is that when they send request to BigQuery, they only pass BigQuery API scope. However they should also request GoogleDrive scope.

Valrievalry

I tried this for my personal add-on and it didn't help. – Metheglin

Did you activate the Drive API for the project ID associated with your API key before you refreshed your OAuth creds with the new scope? (I think that will silently succeed, but you will still be denied API access) – Goudy

+1 - I had the exact same issue and I had to enable both the Drive API for the project (in addition to BigQuery API), as well as use the BigQuery+Drive scopes. I also had to then manually permission the sheets to allow access to the [email protected] account my project used to access the Google Sheets with. – Scaffolding

matt1, I understand you implied this worked for you. Could you please give us a more detailed walkthrough on how to do this? – Wingding

Answer 2

I am not used to Tableau but had a similar issue with the Bigquery API in my Rails app using a Google services account.

There were two issues:

• the scope of Bigquery, by default it only has access to the bigquery API, but you may add other scopes, like drive and spreadsheets. See here for Tableau https://help.tableau.com/current/pro/desktop/en-us/examples_googlebigquery.htm under the section Manually embed attributes in the XML of the workbook or data source file, the first example contains a SCOPE attribute. Each scope is separated by an empty space.

• the access right to the document, in my case I had to ask the owner of the spreadsheet to give permission to my services account, using the email of the same services account, even so services account was created by a super admin and spreadsheet was created by a team member of the same organization.

I hope that helps.

Kalle