Errors: No OAuth token with Google Drive scope was found.

[Anonymous]

I get an error (see below) when connecting BI to a view in bigquery that uses a table where the source is a google sheet on google drive.  I appear to have permissons to the google drive and can access the google sheet and see the view that uses the google sheet as a source. Anyone else had this?

DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Error while reading table: <bigquery view>, error message: Failed to read the spreadsheet. Errors: No OAuth token with Google Drive scope was found.
Details:
DataSourceKind=GoogleBigQuery
DataSourcePath=GoogleBigQuery
OdbcErrors=[Table]

[v-frfei-msft]

Hi @Anonymous ,

Based on my research, accessing data hosted within Google Drive requires an additional OAuth scope. Have you enabled Google Drive access following the guide in this article?

[DavidPROVOST]

Hey DBrownlow. Same problem here. Did you get through this ?

Simiar problem occur when trying to read Google sheet directly from Power BI : Works fine with a public access GSheet, but fails if authentication is required. Funny, as I already have a Google OAuth Token with BigQuery. GSuite tokens differ from GCP Tokens ?

I'll read the provided article.

[andylai]

Do you found any solution? Because I am having the same issue also.

[Héctor]

Not much of a solution but as a quick workaround you can copy the content of the external Google Sheets table into a native BigQuery table with a scheduled query.

You can write the following query on the BigQuery UI:

     SELECT * FROM `project.dataset.external_table` 

Then schedule the query to run daily or hourly, and override the table `project.dataset.native_table`.

Finally, replace references to 'external_table' with 'native_table' in the view.