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

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.

This content is subject to limited support.                

Comments
Nikhil_Gupta
New Member

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

shabir
New Member

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

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

joeyo
New Member

I am facing the same issue here

shabir
New Member

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. 

abdalrazak
New Member

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. 

can you please explain how did you do that?

j_witty
Participant I

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. 

can you please explain how did you do that?

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

n0viandi
New Member

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",])

Dovile
Participant I

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",])

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

n0viandi
New Member

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

Version history
Last update:
‎05-07-2021 09:09 AM
Updated by: