Google Sheets AddOn - AppScript & BigQuery Integration via Service Account

Question

I have a Google Sheets workspace addon and recently did some work to integrate BigQuery. Essentially BigQuery hold a record of books each of which has an author, title etc and my Addon allows people to pull the books that they have read into their sheet. The first column in the sheet allows people to choose from all the authors in the DB, based on that selection the second column is populated with data from BigQuery with all books by that author etc etc. There is no need for my AddOn to access a user's BigQuery, they only access 'my' BgQuery.

This all works fine, but when I submitted my addon for approval I was told

Unfortunately, we cannot approve your request for the use of the following scopes 

https://www.googleapis.com/auth/bigquery

We recommend using service accounts for this type of information exchange.

This seems fair and reading up on Service Accounts it seems a much better fit for my use case. I've gone through the process of creating the service accounts and downloaded my security details json file, however I just can't figure out how to actually query BigQuery from AppScript.

In my non-service account method I have the BigQuery Library installed in AppScript and basically run

var queryResults = BigQuery.Jobs.query(request, projectId);

I've been trying to work from an example at https://developers.google.com/datastudio/solution/blocks/using-service-accounts

function getOauthService() {
var serviceAccountKey = getServiceAccountCreds('SERVICE_ACCOUNT_KEY');// from private_key not private_key_id of JSON file
var serviceAccountEmail = getServiceAccountCreds('SERVICE_ACCOUNT_EMAIL');
return OAuth2.createService('RowLevelSecurity')
.setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
.setTokenUrl('https://accounts.google.com/o/oauth2/token')
.setPrivateKey(serviceAccountKey)
.setIssuer(serviceAccountEmail)
.setPropertyStore(scriptProperties)
.setCache(CacheService.getScriptCache())
.setScope(['https://www.googleapis.com/auth/bigquery.readonly']);
}
function getData(request) {
var accessToken = getOauthService().getAccessToken();
var billingProjectId = getServiceAccountCreds('BILLING_PROJECT_ID');
// var email = Session.getEffectiveUser().getEmail();
// return cc
// .newBigQueryConfig()
// .setAccessToken(accessToken)
// .setBillingProjectId(billingProjectId)
// .setUseStandardSql(true)
// .setQuery(BASE_SQL)
// .addQueryParameter('email', bqTypes.STRING, email)
// .build();
}

I've commented out the code in the above which relates to

var cc = DataStudioApp.createCommunityConnector();

in the above tutorial since I'm not using DataStudio but I'm really not sure what to replace it with so I can query BigQuery with AppScript via a Service Account. Can anyone offer any advice?

You should be able to use the code you commented out. Service accounts have their own email, so try using that instead. You can find your service account email under the 'client_email' property in the json file you downloaded for your service account keys. — TheAddonDepot
Thanks for the reply. I am pulling in the client email in the getOauthService function var serviceAccountEmail = getServiceAccountCreds('SERVICE_ACCOUNT_EMAIL');. However the code I commented out relates to the object returned from var cc = DataStudioApp.createCommunityConnector(); - I'm not using DataStudioApp so should I use something else? — Stuart Brown
Oh. Since you're using a Service Accout, it looks like you'll need to leverage the BigQuery REST API directly using UrlFetchApp. — TheAddonDepot
Many thanks, will take a look. Do you have a link to any guides / examples? — Stuart Brown

Stuart Brown Stuart Brown · Accepted Answer · 2021-04-12T08:25:52

Based on the advice from @TheAddonDepot in the comments above my revised code now looks like:

function getBigQueryService() {
  return (
    OAuth2.createService('BigQuery')
      // Set the endpoint URL.
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')

      // Set the private key and issuer.
      .setPrivateKey(JSON_CREDS.private_key) // from the json file downloaded when you create service account
      .setIssuer(JSON_CREDS.client_email). // from the json file downloaded when you create service account

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

      // Caching
      .setCache(CacheService.getUserCache())

      // Locking
      .setLock(LockService.getUserLock())

      // Set the scopes.
      .setScope(['https://www.googleapis.com/auth/bigquery.readonly'])
     // .setScope('https://www.googleapis.com/auth/bigquery')
  )
}


 function queryData(){
  const bigQueryService = getBigQueryService()
  if (!bigQueryService.hasAccess()) {
    Logger.log("BQ ERROR IS "+ bigQueryService.getLastError())
  }

  //const projectId = bigqueryCredentials.project_id
  var projectId = "<yourprojectid>"

  let url = 'https://bigquery.googleapis.com/bigquery/v2/projects/<yourprojectid>/queries'; //projectID is taken from the security json file for the service account, although it doesn't seem to matter if you use the project code
  
  const headers = {
    Authorization: `Bearer ${bigQueryService.getAccessToken()}`,
    'Content-Type': 'application/json',
  }


 var data = {query:"<your query>",useLegacySql:false};
  const options = {
    method: 'post',
    headers,
    //contentType: 'application/json',
    payload: JSON.stringify(data),
   muteHttpExceptions: true // on for debugging
  }

  try {
    const response = UrlFetchApp.fetch(url, options)
    const result = JSON.parse(response.getContentText())
    Logger.log("here is result "+ JSON.stringify(result))

  } catch (err) {
    console.error(err)
  }
}

Google Sheets AddOn - AppScript & BigQuery Integration via Service Account

1 Answers