"Failed to read the spreadsheet. Error code: PERMISSION_DENIED" - Tableau custom query

Question

I am working on Tableau 2020.2. I have a BigQuery table (TableA), fetching live data from Google sheet (Sheet1). When I am trying to connect the Google sheet directly in Tableau Data-source, it is getting connected. Also, when I am dragging and dropping the table(TableA) directly on the 'Drag tables here' in the data source, it is working fine. But when I am trying to run a custom query on the TableA, it is throwing the following error:

Bad Connection: Tableau could not connect to the data source.
Error Code: 015CFBE6
The Google BigQuery service was unable to process this request.
Error while reading table: TableA, error message: Failed to read the spreadsheet. Error code: PERMISSION_DENIED

Lit

This might be a dumb suggestion, but are you using a service account to run the custom query? If so, make sure you give it direct access via the "Share" button on the sheet. I've found that the service account needs access to both the BigQuery table and the original sheet; of course, I don't know how you are authenticating. – Samal

Answer 1

I had a similar permission problem using BigQuery, Sheets and Metabase: when Metabase was about to query a bigquery external table (a table from google sheets), it would return permission denied.

To avoid that, the best solution we got was to create a scheduled query at bigquery that would simply copy the external table to a internal table inside bigquery every hour. After that, Metabase would query the internal table (and not the external one). Once google sheets doesn't support big amounts of data, you should be fine in terms of costs. It is not the most beautiful solution, but it works nicely.

As far as I understand, this problem happens because google sheets has one type of permission and GCP another. So Tableau, Metabase, etc have a permission to query on BigQuery, but they also need a permission to query on a specific google sheet table through bigquery. This adds a weird complexity.

Mistrial

I had the same issue and solved it the exact same way – Vedette

Answer 2

Your gcp account need two permissions to query data from a BigQuery external table (tableA) linking to Google Sheet (Sheet1).

• read/viewer access to tableA
• read/viewer access to Sheet1

Could you check and confirm whether the GCP account you used to authenticate your Tableau BigQuery connection has both permissions?

Boden