how to make a correct HTTP request to BigQuery from google script

Question

I am working in google script API trying to get a schema of a table from BiqQuery... not sure why it is so troublesome.

I am sending a request like this :

let url =  'https://bigquery.googleapis.com/bigquery/v2/projects/'+ projectId +'/datasets/'+ datasetId +'/tables/' +tableId; 

 var response = UrlFetchApp.fetch(url)

I am getting this response:

Exception: Request failed for https://bigquery.googleapis.com returned code 401. Truncated server response: { "error": { "code": 401, "message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie ... (use muteHttpExceptions option to examine full response) (line 68, file "bigQuery")

I have been able to load data to bigQuery alright... not sure why this does not work. I have looked at the OAuth fields in manifest and the script does have access to bigQuery...

no success also when adding this to the options field of the UrlFetch request

var authHeader = 'Basic ' + Utilities.base64Encode(USERNAME + ':' + PASSWORD);
    var options = {
      headers: {Authorization: authHeader}
    }

Basic authentication does not contain OAuth scopes info, try with bearer token. 1. get token via ScriptApp.getOAuthToken() and send it as Bearer <token here>. — Oleg Valter
@Oleg Valter - thank you! it worked - perfect. What is the reason that my other functions addressing bigQuery work and this HTTP request needed this extra token? I thus successfully got the table schema from BigQuery. Is this the cleanest way to achieve that? — makmak
makmak, well, for Google Apps Script environment, there is an Advanced Service that wraps around the BigQuery REST API specifically for GAS: developers.google.com/apps-script/advanced/bigquery, so you don't have to worry about such things as auth or parsing the response. I personally don't like advanced services too much, but they act as sort of useful libraries. P.s. Glad you I could help — Oleg Valter
As for the other calls to the API - I am not sure, my best guess is that they do not require authentication. If you provide more info about the endpoints which allowed you to request them without the bearer token, I can say for sure and / or test — Oleg Valter
@Oleg Valter I have been able to load BigQuery.Jobs.insert(loadJob, projectId, blob); And I suppose all proper API calls will work as well. For finding the table schema though I could not find anything except the HTTP request. Or a "bq show" call for command prompt (which supposedly just makes a REST Api request though I could not reconstruct one) — makmak

Oleg Valter Oleg Valter · Accepted Answer · 2020-08-22T19:45:52

Use bearer tokens

The reason why the BigQuery API rejects your requests is that the endpoint requires one of the following scopes to be provided with the access token to work, and it is missing from the request:

https://www.googleapis.com/auth/bigquery
https://www.googleapis.com/auth/cloud-platform
https://www.googleapis.com/auth/bigquery.readonly
https://www.googleapis.com/auth/cloud-platform.read-only

The actual issue here is that the basic authorization scheme lacks info about any claims, only sending over correct credentials. Since you are requesting the endpoint directly with UrlFetch service, despite correctly specifying the scopes in the manifest, they will not be sent over.

ScriptApp service now provides an easy method to get a valid bearer token without using an OAuth 2.0 library or building the flow from scratch: getOAuthToken. Pass it to an Authorization header as bearer token, and you should be all set:

const token = ScriptApp.getOAuthToken();

const options = {
  headers : {
    Authorization : `Bearer ${token}`
  }
};

Use Advanced Service

As an alternative, there is an official advanced service as a wrapper around BigQuery REST API that will manage authentication and response parsing for you.

You must enable the BigQuery advanced service before using it

Also, note that the advanced service identifier is configurable, so you have to reference the identifier you chose.

In your case, the service can be used as follows (assuming you used the default BigQuery identifier). There is also the 4th argument of type object that contains optional arguments (not shown here):

Bigquery.Tables.get("projectId","datasetId", "tableId");

The method chain above corresponds to tables.get method of the BigQuery API.

how to make a correct HTTP request to BigQuery from google script

1 Answers