BQ cli not getting data from Google Sheets

Question

I'm trying to run a query with bq cli that joins data from a table that is connected to Google Sheets and another table on Bigquery. I'm running this on a instance on Google Compute Engine, but i'm getting this error:

Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

I took a look and found that I need to change the scopes, but my instance is already with the setting "Allow full access to all Cloud APIs".

Anyone knows how to solve this?

Thanks

Answer 1

The problem is that Drive is not part of the Cloud APIs. It belongs to the GSuite Activity API. See here. Thus, the scope for Drive (https://www.googleapis.com/auth/drive) is not enabled for the service account on your GCE instance.

Here's what you need to do:

1. Enable the Drive API in your project by hitting https://console.developers.google.com/apis/api/drive.googleapis.com/overview?project=[YOUR-PROJECT-NUMBER]
2. When creating your instance, use the CLI tool to add the Drive scope i.e. gcloud compute instances create [YOUR-PREFERRED-INSTANCE-NAME] --scopes=https://www.googleapis.com/auth/drive,https:/ /www.googleapis.com/auth/bigquery --zone=[YOUR-PREFERRED-ZONE]
3. Now run your query and it should work i.e. bq query "SELECT * FROM [grey-sort-challenge:sheets_test.test]".

Using a BigQuery table that is backed by a test sheet in Drive, I initially got the same error as you. However, after setting the scopes as above, it now works:

Now, in my test/example I created an instance from scratch (step 2). But, if you'd like to modify an existing instance you'll need to stop that instance, and then update the scopes using the gcloud beta compute instances set-scopes command. See here.

Note: when you set the scopes that way, you will clobber any existing scopes that have been set - so make sure to enter all the scopes that you need e.g.: gcloud beta compute instances set-scopes [INSTANCE-NAME] --scopes=https://www.googleapis.com/auth/drive,https:// www.googleapis.com/auth/bigquery,...