Using the BigQuery API on APP Scritps with a Service Account

Question

I'm trying execute a job on BigQuery on a VPC project using App Scripts.

My goal is store the result in an array to create a dynamic prompt for DataStudio using community connectors

Using the following code:

function runQuery() {
  
  var sql =  "SELECT Distinct ss_cd FROM `vf-pt-ngbi-dev-gen-03.AEAD_DataSet_test.d_customer` WHERE end_dttm IS NOT NULL";
  var queryResults;
  var projectNumber = 'projectNumber'
  
    // Inserts a Query Job
  try {
    var queryRequest = BigQuery.newQueryRequest();
    queryRequest.setQuery(sql).setTimeoutMs(100000);
    queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);
  }
  catch (err) {
    Logger.log(err);
    return;
  }

Since this is a VPC project I need to use a Service Account to perform this request?

However, I would like to know how to add this authorization?

Or exists another approach to execute a BigQuery job on a VPC project and store the results in an array?

Were you able to check the solution below? – NightEye Jul 20 '21 at 20:18 — NightEye, Jul 20 '21 at 20:18

score 1 · Answer 1 · answered Jul 19 '21 at 19:14

You can get the service account token in apps script (see reference) then use that token for the REST API via UrlFetchApp.

Sample:

function runQuery() {
  // ...
  var service = getService();
  if (service.hasAccess()) {
    sendQuery(service);
  }
  // ...
}

function sendQuery(service){
  var projectId = 'projectID';
  var url = 'https://bigquery.googleapis.com/bigquery/v2/projects/' + projectId + '/queries';
 
  // see request body for reference
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#QueryRequest
  var body = {
    // ...
  }
      
  var options = {
   "method": "post",
   "headers": {
      "Authorization": "Bearer " + service.getAccessToken()
     },
   "contentType": "application/json", 
   "payload": JSON.stringify(body)
   };
   var response = UrlFetchApp.fetch(url, options);    
}
// direclty copied from https://github.com/googleworkspace/apps-script-oauth2/blob/master/samples/GoogleServiceAccount.gs
function getService() {
  return OAuth2.createService('BigQuery:' + USER_EMAIL)
      // Set the endpoint URL.
      .setTokenUrl('https://oauth2.googleapis.com/token')

      // Set the private key and issuer.
      .setPrivateKey(PRIVATE_KEY)
      .setIssuer(CLIENT_EMAIL)

      // Set the name of the user to impersonate. This will only work for
      // Google Apps for Work/EDU accounts whose admin has setup domain-wide
      // delegation:
      // https://developers.google.com/identity/protocols/OAuth2ServiceAccount#delegatingauthority
      .setSubject(USER_EMAIL)

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getScriptProperties())

      // Set the scope. This must match one of the scopes configured during the
      // setup of domain-wide delegation.
      .setScope('https://www.googleapis.com/auth/bigquery');
}

References:

Note:

See a question containing a code what yours should look like.

Using the BigQuery API on APP Scritps with a Service Account

1 Answers1

Sample:

References:

Note: