title: Correct way to grant Bigquery Client Tools access for user for table backed by sheets, but API still fails
body: <p>In our organisation we have a standard user who created all the Bigquery projects, tables, and some Google Sheets that they point to.</p>
  
  <p>We created another user in the organisation, granted them <code>Bigquery Data Viewer</code> and <code>Bigquery Data User</code> 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 <code>Encountered an error while globbing file pattern.</code></p>
  
  <p>Granting that new user <code>Bigquery Editor</code> permission, they can create their own tables backed by sheets, but they get this dialog box:</p>
  
  <p><a href="https://i.stack.imgur.com/R1gTE.png"><img src="https://i.stack.imgur.com/R1gTE.png" alt="Bigquery Client Tools Would like to Allow"></a> </p>
  
  <p>After granting permissions, we can remove the <code>Bigquery Editor</code> permission and the user continues to have access to the all the existing Bigquery tables including those backed by sheets.</p>
  
  <p>Looking in the new user's Google Account <a href="https://security.google.com/settings/security/permissions">https://security.google.com/settings/security/permissions</a>, we see they've added the Bigquery Client Tools:</p>
  
  <p><a href="https://i.stack.imgur.com/0QnDR.png"><img src="https://i.stack.imgur.com/0QnDR.png" alt="Google Account shows Bigquery Client Tools"></a></p>
  
  <p><strong>Question 1:</strong> What is the correct way to grant this permission for new users? We've found two work-arounds:</p>
  
  <ul>
  <li>Grab the URL from the pop-up and edit it for new users, without the need to grant them Editor permission.</li>
  <li>Get the users to save a query out to Google Sheets, which triggers the pop-up.</li>
  </ul>
  
  <p>But we've found no programatic way to grant the users this permission.</p>
  
  <p><strong>Further...</strong></p>
  
  <p>However, the API still seems to have problems. We ran:</p>
  
  <pre><code>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]"
  </code></pre>
  
  <p>and our log shows either <code>"An internal error occurred and the request could not be completed."</code> or <code>"Encountered an error while globbing file pattern."</code>; for example:</p>
  
  <pre><code>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."
     }
    ]
   },
   [...]
  }
  </code></pre>
  
  <p><strong>Question 2:</strong> What further do we need to do to grant data access through the API/cli?</p>