8

In our organisation we have a standard user who created all the Bigquery projects, tables, and some Google Sheets that they point to.

We created another user in the organisation, granted them Bigquery Data Viewer and Bigquery Data User permissions, and allowed them access to the Google Sheet. They are able to access the standard tables, but the not the ones backed by Google Sheets; they get the error Encountered an error while globbing file pattern.

Granting that new user Bigquery Editor permission, they can create their own tables backed by sheets, but they get this dialog box:

Bigquery Client Tools Would like to Allow

After granting permissions, we can remove the Bigquery Editor permission and the user continues to have access to the all the existing Bigquery tables including those backed by sheets.

Looking in the new user's Google Account https://security.google.com/settings/security/permissions, we see they've added the Bigquery Client Tools:

Google Account shows Bigquery Client Tools

Question 1: What is the correct way to grant this permission for new users? We've found two work-arounds:

  • Grab the URL from the pop-up and edit it for new users, without the need to grant them Editor permission.
  • Get the users to save a query out to Google Sheets, which triggers the pop-up.

But we've found no programatic way to grant the users this permission.

Further...

However, the API still seems to have problems. We ran:

gcloud auth login --enable-gdrive-access
# approve permissions in the web browser for the new user
bq query --apilog=apil.log "SELECT * FROM [warehouse:catalog.table]"

and our log shows either "An internal error occurred and the request could not be completed." or "Encountered an error while globbing file pattern."; for example:

INFO:root:{
 [...]
 "jobReference": {
  "projectId": [...]
  "jobId": "bqjob_r2410ded84270edc0_00000158fdea47bb_1"
 },
 "status": {
  "state": "DONE",
  "errorResult": {
   "reason": "internalError",
   "message": "An internal error occurred and the request could not be completed."
  },
  "errors": [
   {
    "reason": "internalError",
    "message": "An internal error occurred and the request could not be completed."
   }
  ]
 },
 [...]
}

Question 2: What further do we need to do to grant data access through the API/cli?

Piran
  • 7,180
  • 1
  • 24
  • 37
  • Have you done of 3 of these steps? http://stackoverflow.com/questions/40731823/encountered-an-error-while-globbing-file-pattern-error-when-using-bigquery-api – Graham Polley Jan 27 '17 at 12:01
  • Yes, we've run those steps for the second question; and they had no effect :-( The error perhaps indicates it's an internal error, rather than a permissions problem. – Piran Feb 23 '17 at 11:38
  • Odd. Maybe one of the Google engineers can have a look at the error internally using the job id. – Graham Polley Feb 23 '17 at 20:43
  • Could you confirm you've added the service account as an editor on the Google Sheet. – ethanenglish May 16 '19 at 18:43

1 Answers1

0

I recommend to have a separate dataset with views querying the original dataset. You can share dataset with views. Which means that views will have access to a dataset and then you can give the users access to the "view dataset".

Dataset_A (google sheets)

  • shared with views in the Dataset_views

Dataset_views

  • shared with the users (or group)
Nathan Nasser
  • 1,008
  • 7
  • 18