Query data from Google Sheets-based table in BigQuery via API using service account

Question

I can fetch data from native BigQuery tables using a service account.

However, I encounter an error when attempting to select from a Google Sheets-based table in BigQuery using the same service account.

from google.cloud import bigquery

client = bigquery.Client.from_service_account_json(
    json_credentials_path='creds.json',
    project='xxx',
)

# this works fine
print('test basic query: select 1')
job = client.run_sync_query('select 1')
job.run()
print('results:', list(job.fetch_data()))
print('-'*50)

# this breaks
print('attempting to fetch from sheets-based BQ table')
job2 = client.run_sync_query('select * from testing.asdf')
job2.run()

The output:

⚡  ~/Desktop ⚡  python3 bq_test.py
test basic query: select 1
results: [(1,)]
--------------------------------------------------
attempting to fetch from sheets-based BQ table
Traceback (most recent call last):
  File "bq_test.py", line 16, in <module>
    job2.run()
  File "/usr/local/lib/python3.6/site-packages/google/cloud/bigquery/query.py", line 381, in run
    method='POST', path=path, data=self._build_resource())
  File "/usr/local/lib/python3.6/site-packages/google/cloud/_http.py", line 293, in api_request
    raise exceptions.from_http_response(response)
google.cloud.exceptions.Forbidden: 403 POST https://www.googleapis.com/bigquery/v2/projects/warby-parker-1348/queries: Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

I've attempted to use oauth2client.service_account.ServiceAccountCredentials for explicitly defining scopes, including a scope for drive, but I get the following error when attempting to do so:

ValueError: This library only supports credentials from google-auth-library-python. See https://google-cloud-python.readthedocs.io/en/latest/core/auth.html for help on authentication with this library.

My understanding is that auth is handled via IAM now, but I don't see any roles to apply to this service account that have anything to do with drive.

How can I select from a sheets-backed table using the BigQuery python client?

Eduardo Trevisani Eduardo Trevisani · Accepted Answer · 2018-11-09T20:07:16

I've ran into the same issue and figured out how to solve it.

When exploring google.cloud.bigquery.Client class, there is a global variable tuple SCOPE that is not being updated by any arguments nor by any Credentials object, persisting its default value to the classes that follows its use.

To solve this, you can simply add a new scope URL to the google.cloud.bigquery.Client.SCOPE tuple.

In the following code I add the Google Drive scope to it:

from google.cloud import bigquery

#Add any scopes needed onto this scopes tuple.
scopes = (
     'https://www.googleapis.com/auth/drive'
)

bigquery.Client.SCOPE+=scopes

client = bigquery.Client.from_service_account_json(
    json_credentials_path='/path/to/your/credentials.json',
    project='your_project_name',
)

With the code above you'll be able to query data from Sheets-based tables in BigQuery.

Hope it helps!

Query data from Google Sheets-based table in BigQuery via API using service account

2 Answers