Charlotte Towell

Posted on Oct 13

Google Sheets x BigQuery Sync using Google Apps Script

#googlecloud #bigquery #automation #productivity

If you haven't discovered Google Apps Script yet (ahem, me a week ago) then you are seriously missing out!

This free service included in the Google Suite can be extremely powerful when wielded with the right knowledge. I recommend checking out Benson King'ori's dev.to article for a super in-depth explanation & intro of apps script.

Here's how I used it to set up a user-trigger Google Sheets -> BigQuery sync

tldr; save a JSON service account key in PropertiesService to access any GCP API. You're welcome :)

Prerequisities

You'll need to have a Google Cloud Project & a Google Workspace to work in.

Optional, but I'd also suggest installing clasp - a CLI for working with apps script locally.

Credentials

Perhaps the most obvious hurdle to tackle here is how to ensure secure authentication with our GCP project when initiating a sync from a user in Google Sheets. This is where we'll use a json service account key and the script's PropertiesService to save it.

This is the terraform snippet I used to create my service account in GCP, granting only the necessary permissions for BigQuery sync:

resource "google_service_account" "google_apps_script_sa" {
  account_id   = "google-apps-script-sa"
  display_name = "Google Apps Script Service Account"
  description  = "Service account for Google Apps Script"
}

resource "google_project_iam_member" "act_as_google_apps_script_sa" {
  for_each = toset([
    "roles/bigquery.dataEditor",
    "roles/bigquery.jobUser",
    "roles/bigquery.readSessionUser",
    "roles/bigquery.user"
  ])

  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.google_apps_script_sa.email}"

  depends_on = [google_service_account.google_apps_script_sa]
}

Once created, you an generate a JSON service account key and then store it (joined as a single line) in the PropertiesService of the script.

User Interaction

I created my script as "Bound" to a certain Google Sheets file, and within it, I wanted the user to select which Sheet to sync. Therefore, we can create a .html file in our project to define the popup dialog, hooking up a 'Sync' button to a google.script.run block as follows:

$('submitBtn').addEventListener('click', function (e) {
  // ...

    google.script.run
      .syncToBigQuery(...);
  });

We can then include this dialog as a custom menu option in Sheets by defining a createMenu item that shows the popup as a modal dialog.

// Menu.js
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Data Tools')
    .addItem('Sync to BigQuery', 'showSheetSelector')
    .addToUi();
}
=
function showSheetSelector() {
  const t = HtmlService.createTemplateFromFile('Dialog');

  const html = t.evaluate()
    .setWidth(420)
    .setHeight(260);

  SpreadsheetApp.getUi().showModalDialog(html, 'Sync Sheet to BigQuery');
}

Authentication

Now for where the real magic happens, to make use of our service account key, we use the following function to mint an oauth2 token that will allow us to interact with BigQuery.

function getServiceAccountToken_(scopes) {
  const raw = PropertiesService.getScriptProperties().getProperty('SA_KEY_JSON');
  if (!raw) throw new Error('Missing SA_KEY_JSON in Script Properties');
  const key = JSON.parse(raw);

  const service = OAuth2.createService('sa-jwt')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(key.private_key)
    .setIssuer(key.client_email)
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setScope(scopes.join(' '));

  const accessToken = service.getAccessToken();
  if (!accessToken) throw new Error('Failed to obtain access token: ' + service.getLastError());
  return { accessToken };
}

Using it is as simple as:

 const { accessToken } = getServiceAccountToken_([
    'https://www.googleapis.com/auth/bigquery'
  ]);

The possibilities are endless

It's clear how easy this is to extend to any other GCP service! All you have to do now is cross-reference the docs and form your Rest API calls to BigQuery's API. The only limitation to what you can do here is your imagination...

...and of course:

The API's available to us for GCP Services
And the associated runtime limits with Google Apps Script

Nonetheless, this proved a simple & highly suitable method to my use case to allow non-technical users who occasionally update a lookup spreadsheet to keep data in sync with our BigQuery warehouse. A full-fledged scheduled sync would've been overkill for a job so small and infrequent. Having a custom menu in Google Sheets was the perfect solution of meeting the user where they're at.

DEV Community