google.api_core.exceptions.Forbidden: 403 Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

[Stefn93]

Hi all,

I recently experienced an API error that is misleading and makes it difficult to understand the real problem.
I created a BQ table on a GSHEET drive file and I kept getting this error:
google.api_core.exceptions.Forbidden: 403 Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

In reality, all permissions were correctly set and there was no reason for a permission denied.

I troubleshooted the error and I understood that the real problem was not having provided the scope:
https://www.googleapis.com/auth/drive
while instantiating the BigQuery Client.

I believe that the error shown is misleading and should be:
insufficient scopes

Environment details

• OS type and version: ubuntu 20.04
• Python version: 3.8
• pip version: 22.x
• google-cloud-bigquery version: 3.1.0

Steps to reproduce

1. Create a Bigquery external table on a GSHEET file
2. Provide to google default credentials access for both the GSHEET file and BigQuery table
3. Run this code while querying the table:
   from google.cloud import bigquery
client = bigquery.Client()
query_job = client.query(query)
df = query_job.to_dataframe()

Stack trace

[2022-10-28 09:29:24,569] {pod_launcher.py:156} INFO - b'  File "/usr/local/lib/python3.8/site-packages/google/cloud/bigquery/job/query.py", line 1683, in to_dataframe\n'
[2022-10-28 09:29:24,569] {pod_launcher.py:156} INFO - b'    query_result = wait_for_query(self, progress_bar_type, max_results=max_results)\n'
[2022-10-28 09:29:24,570] {pod_launcher.py:156} INFO - b'  File "/usr/local/lib/python3.8/site-packages/google/cloud/bigquery/_tqdm_helpers.py", line 88, in wait_for_query\n'
[2022-10-28 09:29:24,570] {pod_launcher.py:156} INFO - b'    return query_job.result(max_results=max_results)\n'
[2022-10-28 09:29:24,570] {pod_launcher.py:156} INFO - b'  File "/usr/local/lib/python3.8/site-packages/google/cloud/bigquery/job/query.py", line 1499, in result\n'
[2022-10-28 09:29:24,570] {pod_launcher.py:156} INFO - b'    do_get_result()\n'
[2022-10-28 09:29:24,570] {pod_launcher.py:156} INFO - b'  File "/usr/local/lib/python3.8/site-packages/google/api_core/retry.py", line 283, in retry_wrapped_func\n'
[2022-10-28 09:29:24,570] {pod_launcher.py:156} INFO - b'    return retry_target(\n'
[2022-10-28 09:29:24,571] {pod_launcher.py:156} INFO - b'  File "/usr/local/lib/python3.8/site-packages/google/api_core/retry.py", line 190, in retry_target\n'
[2022-10-28 09:29:24,571] {pod_launcher.py:156} INFO - b'    return target()\n'
[2022-10-28 09:29:24,571] {pod_launcher.py:156} INFO - b'  File "/usr/local/lib/python3.8/site-packages/google/cloud/bigquery/job/query.py", line 1489, in do_get_result\n'
[2022-10-28 09:29:24,571] {pod_launcher.py:156} INFO - b'    super(QueryJob, self).result(retry=retry, timeout=timeout)\n'
[2022-10-28 09:29:24,571] {pod_launcher.py:156} INFO - b'  File "/usr/local/lib/python3.8/site-packages/google/cloud/bigquery/job/base.py", line 728, in result\n'
[2022-10-28 09:29:24,571] {pod_launcher.py:156} INFO - b'    return super(_AsyncJob, self).result(timeout=timeout, **kwargs)\n'
[2022-10-28 09:29:24,572] {pod_launcher.py:156} INFO - b'  File "/usr/local/lib/python3.8/site-packages/google/api_core/future/polling.py", line 137, in result\n'
[2022-10-28 09:29:24,572] {pod_launcher.py:156} INFO - b'    raise self._exception\n'
[2022-10-28 09:29:24,572] {pod_launcher.py:156} INFO - b'google.api_core.exceptions.Forbidden: 403 Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.\n'
[2022-10-28 09:29:24,572] {pod_launcher.py:156} INFO - b'\n'
[2022-10-28 09:29:24,572] {pod_launcher.py:156} INFO - b'Location: EU\n'
[2022-10-28 09:29:24,572] {pod_launcher.py:156} INFO - b'Job ID: d09cdce0-7897-4de5-ae73-f5e27ca9d6ef\n'
[2022-10-28 09:29:24,573] {pod_launcher.py:156} INFO - b'\n'

Thank you,
Stefano

[iamdevnitesh]

Hi @Stefn93 I am facing the same issue. Did you find any solution to this problem ?

[Stefn93]

Hi @Stefn93 I am facing the same issue. Did you find any solution to this problem ?

Hi @iamdevnitesh I solved the issue by providing scope:
"https://www.googleapis.com/auth/drive"
while instantiating BigQuery Client:
client = bigquery.Client(scopes=["https://www.googleapis.com/auth/drive"]) should work if your issue is the same

[iamdevnitesh]

@Stefn93 Here's my code after applying the scopes but somehow getting the same result

import os
from google.cloud import bigquery
from google.oauth2 import service_account

key_path = os.path.join(os.path.dirname(__file__), 'credentials.json')
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = key_path

credentials = service_account.Credentials.from_service_account_file(
    key_path,
    scopes=["https://www.googleapis.com/auth/drive",
            "https://www.googleapis.com/auth/cloud-platform"
            ]
)

query = """
SELECT * FROM `project-name.dataset-name.table-name`;
"""

client = bigquery.Client()
# even tried with client = bigquery.Client(scopes)

query_job = client.query(query)

results = query_job.result()

[Stefn93]

Have you also shared the Gsheet you're trying to read with the service account email in use?

[iamdevnitesh]

Have you also shared the Gsheet you're trying to read with the service account email in use?

Gsheets sharing settings are public & it is also shared with the service account

[Stefn93]

In the code you wrote above, I see you're not providing credentials to the bigquery client. try:
client = bigquery.Client(credentials=credentials)

[iamdevnitesh]

@Stefn93 Thanks a lot that solved the issue.

[meredithslota]

Hi all — this is documented in a code sample here: https://cloud.google.com/bigquery/docs/samples/bigquery-auth-drive-scope and integrated with our documentation here: https://cloud.google.com/bigquery/docs/external-data-drive