Google Data Studio error when trying to access BigQuery table sources from Google Drive Spreadsheet

Question

I created a BigQuery table from Google Sheet. When I am trying to query this table from BigQuery - everything is ok.

In the Data Studio, I've created a data source that calls that BigQuery Table, but when I'm attempting to use this data source in a report - I get an error.

Then I checked the Query history in BigQuery to see how Data Studio tries to access it, I saw this error message:

Error while reading table: dataset.Table_sheet, error message: Failed to read the spreadsheet. Error code: PERMISSION_DENIED

The same query when I run it directly in BigQuery works just fine, but not when I access it from Data Studio.

Answer 1

The solution to the problem - surprised me =). Dear stranger, you should go to the data studio and create a data source directly from the Google Spreadsheets - you will be prompted to authorize Data Studio to access your Google Drive. After granting access to this new data source - the one you created before will be able to query the table as expected. Seems like a workaround, but it works.