1

I have a bigquery table whose data source is a google spreadsheet. I'm trying to use python to access this table but I'm encountering the following error google.api_core.exceptions.BadRequest: 400 Error while reading table: datset.table, error message: Failed to read the spreadsheet. Errors: com.google.apps.framework.request.ForbiddenException: Permission denied [S]#RITZ#369137407806#topTeRwnQcmrdMeVQ98ZkGA

My app is authenticated with a ServiceAccount JSON token with BigQuery Admin Roles, and here is how my client is created:

from google.oauth2.service_account import Credentials

import os

scopes = [
    'https://www.googleapis.com/auth/bigquery',
    'https://www.googleapis.com/auth/cloud-platform',
    'https://www.googleapis.com/auth/drive',
]

credentials = Credentials.from_service_account_file(os.environ['GOOGLE_APPLICATION_CREDENTIALS'], scopes=scopes)

client = bigquery.Client(credentials=credentials)

I have no problem with strict BigQuery tables, but I fail to understand what I'm doing wrong here. Thanks for your help.

Bobby Wan
  • 11
  • 2

2 Answers2

0
# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
   bigquery.SchemaField("name", "STRING"),
   bigquery.SchemaField("post_abbr", "STRING"),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"

load_job = client.load_table_from_uri(
             uri,
             dataset_ref.table("us_states"),
             location="US",  # Location must match that of the destination dataset.
             job_config=job_config,
            )  # API request
print("Starting job {}".format(load_job.job_id))

load_job.result()  # Waits for table load to complete.
print("Job finished.")

destination_table = client.get_table(dataset_ref.table("us_states"))
print("Loaded {} rows.".format(destination_table.num_rows))

The above code I got from Google it self works fine with me. They do have codes in other languages check this https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json

  • Thanks, but I'm trying to query a table whose datasource is a spreasheet, not loading data to a table. – Bobby Wan Oct 31 '19 at 14:59
  • Have you done these three steps ? https://stackoverflow.com/questions/40731823/encountered-an-error-while-globbing-file-pattern-error-when-using-bigquery-api –  Oct 31 '19 at 15:03
  • Yes I have. Drive API is enabled, ServiceAccount email has Read access to spreadsheet, and drive scope is requested at client creation as shown in above code. – Bobby Wan Oct 31 '19 at 15:15
0

Ok, I finally got it. I thought I had given access to the service account email, but it seems that it didn't work because I wasn't owner of this file. Using advanced access request didn't send a request to the owner either.

So I asked the owner to send an invite to the service account email and now it's working fine.

Bobby Wan
  • 11
  • 2