"Encountered an error while globbing file pattern" error when using BigQuery API w/ Google Sheets

Question

When trying the access a federated source (Google sheets) from BigQuery API, the following error is thrown:

[..]
 "errorResult" : {
      "location" : "/gdrive/id/<removed_file_id>",
      "message" : "Encountered an error while globbing file pattern.",
      "reason" : "invalid"
    }
[..]

The table in BigQuery is set up to point to this file. It works via the Web UI. It is only when trying to query the table through the API does it then choke with the error above.

I'm guessing it has something to do with permissions. What needs to be done to allow accessing a BigQuery table from the API which is a federated source (pointing to Google Sheets)?

Reeducate

Answer 1

There are 3 steps to follow when allowing the API to query a federated table in BigQuery - which is pointing to a file in Drive i.e. Google Sheets.

Two of the steps are documented here (I was missing the 2nd one - adding the Drive scope). The last one is the add the associated service account email that is being used to access the API to the file itself.

1. Use the Google Cloud Platform Console to enable the Google Drive API for the project making the API call.
2. Request the OAuth scope for Google Drive in addition to the scope for BigQuery.
3. Add the service account email that you are using to the file in Drive. It looks something like <project-id>-<fingerprint-hash>@developer.gserviceaccount.com. "View" permission is enough.

Reeducate

Good timing. Also had this problem recently and was missing the 3rd step. The account to give permissions to is the same one the API code uses the appropriate 'Key ID' for (listed on the iam-admin page in the Service Accounts section), of which you can create many against a project for different reasons (and thus give different permissions to different users against different google sheets). – Wilbourn

Whats the source for the 3rd point? What's exactly fingerprint-hash? Is there any documentation? – Kosiur

Answer 2

def create_big_query_client():

    credentials, project = google.auth.default(
        scopes=[
            "https://www.googleapis.com/auth/cloud-platform",
            "https://www.googleapis.com/auth/drive",
            "https://www.googleapis.com/auth/bigquery",
        ]
    )

    bq_client = bigquery.Client(
        credentials=credentials,
        project=GCP_PROJECT_ID
    )

    return bq_client

client_with_drive_scopes = create_big_query_client()

query = """
SELECT *
FROM TABLE
"""

client_with_drive_scopes.query(query).result()

Note:

I discovered that I was unable to set the correct scopes using GCP workflows Big Query Connector. So In order to use a query that was connected to google sheets in my workflow, I created a cloud function, and put a bq client with correct scopes inside.

Olaolaf