10

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

Graham Polley
  • 14,393
  • 4
  • 44
  • 80

1 Answers1

12

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.
Graham Polley
  • 14,393
  • 4
  • 44
  • 80
  • 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). – Mark Fisher Nov 22 '16 at 10:52
  • Whats the source for the 3rd point? What's exactly `fingerprint-hash`? Is there any documentation? – Rutrus Mar 06 '21 at 08:27