How to do o-auth in script and solve bigquery "login required" error

0
votes

I am trying to list column names of a bigquery table via app script.

The code is something like:

var options = {'method': 'get', 'headers' : {'Content-Type':'application/json'}};     
Logger.log(options); 

var url = 'https://www.googleapis.com/bigquery/v2/projects/some_project_id/datasets/'+datasetId+'/tables/'+tableId     
var response = UrlFetchApp.fetch(url, options).getContentText();

However it produces this error;

Request failed for https://www.googleapis.com/bigquery/v2/projects/##### returned code 401. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "required", "message": "Login Required", "locationType": "header", ... (use muteHttpExceptions option to examine full response) (line 70, file "Code")

I do not know how to use o-auth to access the bigquery from script. I also need to deploy the app as an add-on so that users can access their tables via the add-on for which they access in bigquery.

1
google-apps-scriptoauth-2.0google-bigquerygoogle-oauth

1 Answers

1
votes

You should use the AppsScript BigQuery service: https://developers.google.com/apps-script/advanced/bigquery

It takes care of the whole OAuth2-process.

  1. In the script editor, select Resources > Advanced Google services....
  2. In the dialog that appears, click the on/off switch next to the service you want to use.
  3. At the bottom of the dialog, click the link for the Google API Console.
  4. In the console, click into the filter box and type BigQuery then click the name once you see it.
  5. On the next screen, click "Enable API".
  6. Close the API Console and return to the script editor. Click OK in the dialog.

The advanced service you enabled is now available in autocomplete.