We frequently use federated (external) tables within BigQuery, that look over a google sheet. Looker is capable of querying from these tables when we set up the connection via a service account - we simply have to share the sheet with the service account.
However, we have recently come across a requirement where we must use an OAuth connection. Any queries that involve a federated table result in the following error: Query execution failed: - Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.
Typically this is because the correct scopes were not added to the OAuth consent. But even when we add them to the consent page config, Looker does not appear to request these. Note how on the left, the consent screen is set up to require many drive scopes (even a Fitness API scope), but on the right Looker actually requests BigQuery scopes instead.
We have followed the oauth_for_bigquery_connections documentation.
Any advice appreciated! Thank you