[SOLVED] Using Google Workflow to query a Google Sheet through Bigquery

Using Google Workflow to query a Google Sheet through Bigquery

I am using a cloud workflow orchestration to query a Bigquery table, which is connected to a Google Sheet, on google drive as an external table.

So cloud workflow uses service account <SA_ID>@<ProjectName>.iam.gserviceaccount.com to query the said bigquery table. I have provided Editor access to the SA in the google sheet, and Admin access to the SA in Bigquery. While the workflow is working with a native bigquery table, when querying a table conencted to google sheet I get following error Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials. Is a service account not supposed to access a google sheet?

Solution

Posting this as a community wiki so that others can benefit from it.

As mentioned by @guillaume blaquiere:

Pretty similar to this question and for now, you can't achieve this. Connection to Google Drive is not managed by external connection, and therefore there isn't a dedicated service account to achieve the connection. Permission denied when querying a GCP BigQuery Table that is connected to Google Sheets with NodeJS

You may file a feature request in this link.

Note: The Product team will investigate and check if this is a valid feature request. There is no ETA for it, though. Once you create a feature request you may also track the updates of your request on that same link.