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

[trushasahu]

I am trying to read data from the external bigquery table (i.e. linked to google sheet) in apache beam pipeline as below and later loading into bigquery table only. But the code getting failed with below error during reading data,

Error: "Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials."

with beam.Pipeline(options=google_cloud_options) as p:
            # Step 1: Get URL list from BigQuery table
            urls = (
                p | 'Get URLs from BQ Table' >> beam.io.ReadFromBigQuery(
                    query="""
                        SELECT cast(id as INT64) AS site_id, url
                        FROM `my-project.my_dashboard.MAP_URLS_GSHEET`
                    """,
                    use_standard_sql=True
                )
            )

I am using service account which have access to the google sheet as viewer and also to BQ database.

Note: The same code is working in Dataflow while using native BQ table instead of external table.

Could you please help here , how to solve this issue i.e. specially when running through dataflow?

[Aris_O]

Hi Trushasahu,

Does your service account have the following roles aside from viewer access?

• roles/dataflow.admin
• roles/dataflow.worker
  
  These roles are needed in order for the worker service account to be able to create, run, and examine a job.
  
  Kindly see the link for reference: https://cloud.google.com/dataflow/docs/concepts/security-and-permissions#worker-service-account

[trushasahu]

Hello Aris,

Still getting the same error 'Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.' 

The problem is here that the bigquery table is linked to the google sheet. So getting access denied for this only even though the service account has the view access to this google sheet. 

[vbhardwaj]

Could you resolve the issue ?

[Aris_O]

Here are some possible checking you may try to perform:

• Verify the Google Sheet's permissions to ensure that the service account has at least "Viewer" access. Open the Google Sheet, then click "Share" in the top-right corner to accomplish this. Add the service account's email address that has "Viewer" access after that.
• Check to see if the service account has been granted access to Google Drive: Make sure the service account has been granted access to Google Drive. Go to the Google Cloud Console, choose your project, and click "IAM & Admin" in the left-hand menu to accomplish this. Next, locate the problematic service account and confirm that the "Drive" API is turned on.

• Double-check that the BigQuery table is linked to the appropriate Google Sheet by checking the link between the two. To do this, open the BigQuery console, locate the needed table, and select "Details" from the drop-down menu in the top-right corner. After that, make sure the "Source" field refers to the proper Google Sheet by checking it.

• In the event that all other attempts to link the BigQuery table to the Google Sheet are unsuccessful, try linking the BigQuery table again. To do this, open the BigQuery console, locate the needed table, and select "Details" from the drop-down menu in the top-right corner. In order to remove the table from the Google Sheet, click "Unlink" next. After that, click "Link" and adhere to the instructions to re-link the table to Google Sheet.
  
  Hope this helps.

[arunkumarjca]

Hi Aris_O/Team,

While access from VM using individual ID/User define service account ,we are able to access the data from BQ (has external table with Gsheet) . When trying with default service account getting theBigQuery: Permission denied while getting Drive credentials.

Any lead please

[Harold-Anderson]

Unlink is no longer an option.

[jplattus]

---

@Aris_O wrote:

Verify the Google Sheet's permissions to ensure that the service account has at least "Viewer" access. Open the Google Sheet, then click "Share" in the top-right corner to accomplish this. Add the service account's email address that has "Viewer" access after that.

---

With de service email as a viewer worked as a charm. Thanks

[arunkumarjca]

Hi 

Team,

While access from VM using individual ID/User define service account ,we are able to access the data from BQ (has external table with Gsheet) . When trying with default service account /service account attached with VM (by give full Cloud api scope) getting theBigQuery: Permission denied while getting Drive credentials.

Any lead please

[danka32]

Hi All,

I think what you need to do is to set the scope of the VM with the service account as in the following example. 

gcloud beta compute instances stop google-drive-test --zone=europe-west1-c

gcloud beta compute instances set-scopes google-drive-test \
    --scopes=default,cloud-platform,https://www.googleapis.com/auth/drive \
    --zone=europe-west1-c \
    --service-account=0123456789012-compute@developer.gserviceaccount.com

gcloud beta compute instances start google-drive-test --zone=europe-west1-c

[Tobi_Williams]

I was running through the same issue and it was fixed when I added the service account to the list of users with access to the Google Sheets. I hope that helps.

[Daks]

Refer to this stack overflow. It worked for me. You have to explicitly define scopes though you are querying BigQuery
https://stackoverflow.com/users/29967373/user29967373

[qq]

This links to a user.