"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)?

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.