Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials

Looker Tips & Resources Technical Tips & Tricks
1

Knowledge Drop

Last tested: Jan 20, 2021

If you’re seeing this error, check to see if the table that is being queried is a google sheet that’s stored in Drive.

If that’s the case, make sure that the Service Account for BQ that Looker is using has access to the Drive.

4 Likes
2

How to give this access,

i am facing the same issue “Permission denied while getting Drive credentials.”

my both spreadsheet and bigquery is in same account but i dont know how to provide access

2 Likes
3

Did You find the answer? I am having the same issue.

4

I am facing the same issue here

5

I fix this issue by creating a new tabel and query the old tabel and insert the results in to my new tabel and use the new tabel.

1 Like
6

can you please explain how did you do that?

1 Like
7

you can run a scheduled query daily/hourly etc. and use something with the following structure

create table database.new_table as select * from database.old_table

then just schedule that query to run however often you want the data updated and then just create a view from your new_table

2 Likes
8

instead making a new table, you can try this: service_account.Credentials.from_service_account_info(json_file,scopes = [‘https://www.googleapis.com/auth/cloud-platform’,
“https://www.googleapis.com/auth/drive”,
“https://www.googleapis.com/auth/bigquery”,])

2 Likes
9

Hi, @n0viandi , could you please elaborate how/where have you enabled drive authorizations? In a step by step (aka dummy friendly) way. Thank you!

1 Like
10

google_cred_json = { “YOUR GCP CREDENTIAL”
}

google_cred_json = json.dumps(google_cred_json)
google_cred_json = json.loads(google_cred_json)

credentials = service_account.Credentials.from_service_account_info(google_cred_json,scopes=[‘https://www.googleapis.com/auth/cloud-platform’,
“https://www.googleapis.com/auth/drive”,
“https://www.googleapis.com/auth/bigquery”,])

bigquery_client= bigquery.Client(credentials=credentials,project=“YOUR PROJECT NAME”)

query = “”"
YOUR QUERY
“”"

df = bigquery_client.query(query).to_dataframe()

hope this answer your question @Dovile . sorry for bad variable

1 Like