Cloud Run for Google Sheets — Part 2

I swear I write articles just to make the intro artwork

🎊 We made it to part 2!

This post assumes you’ve walked through the steps outlined in part 1.

Cloud Run for Google Sheets — Part 1

A GitHub repository with all of the code can be found here.

https://github.com/SamGallagher95/cloud-run-with-google-sheets

Part 1 is a bit of a slog with all of the required setup to get the Cloud Run Service deployed in a repeatable manner, but now we can do the fun stuff.

In this (hopefully final) part of this series, we will cover the following:

1. Authenticating from our Cloud Run Service to a given Google Sheets spreadsheet.
2. Create a custom UI with some buttons for this spreadsheet.
3. Connect this UI to our Cloud Run Service

Let’s hit it.

To start, create a new Google Sheets spreadsheet. We’ll use it as our example.

Cloud Run to Google Sheets

This is the easiest step of them all.

First — grab the Service Account email for the Service Account we created with Terraform. This is the identity the Cloud Run application assumes when running.

The quickest way to do this is from the Service Account page of the Google Cloud Console. Below is a heavily redacted version of that screen from my project.

Ignore the black bars 😆

The format will be <service-account-name>@<project-name>.iam.gserviceaccount.com . Got it? Great.

Share the spreadsheet with that email address using the “Share” button in Google Sheets. It’s so simple I don’t know why I didn’t think of it earlier.

Editor is probably an acceptable role

And just like that, your Cloud Run application can now read and write to that spreadsheet.

Apps Script Time

Our next objective is to create a custom menu and some buttons for this spreadsheet. This is accomplished by writing some Apps Script code. This code will be directly entered through the Apps Script editor, but I will include it in my final repo in the apps-script directory.

To enable Apps Script and open the editor, click “Extensions” and then “Apps Script”. This will take you to a blank Apps Script editor window. Feel free to change the name from “Untitled Project” to something more creative, like “My Awesome Apps Script Project”.

Here is the code required to construct a custom UI.

code.gs

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var ui = SpreadsheetApp.getUi()

var SHEET_ID = spreadsheet.getId()

function onOpen(e) {
  ui.createMenu("Custom Menu")
    .addItem("Example Button", "exampleButton")
    .addToUi()
}

First, we need access to the SpreadsheetApp to getActiveSpreadsheet which happens to be the spreadsheet this script is attached to, and then we getUi so we can add our custom menu.

The onOpen function is special, and we will link it to the opening of the spreadsheet in the next step. We ignore the e parameter, but that is the event data that triggered the function. We then use theui to create the custom menu.

Adding the Trigger

In the Apps Script console, navigate to the Triggers menu on the left sidebar. Select Add Trigger on the bottom right and configure it like the image below.

This will execute our onOpen function whenever our spreadsheet is opened. Make sure the code.gs file is saved, head back to your spreadsheet, and refresh the page.

Custom menu!

You should see the custom menu and example button on your spreadsheet now! With the button in place, I think it’s time to

Connect to Cloud Run

We will need the URL for the Cloud Run service we deployed in the previous step. You can find this on the Cloud Run page in the GCP Cloud Console.

My example service URL

We are going to tie that Example Button we created to the /example-sheets-write endpoint in our Cloud Run service.

Here is the new code for our Apps Script project.

code.gs

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var ui = SpreadsheetApp.getUi()

var SHEET_ID = spreadsheet.getId()
var CLOUD_RUN_URL = "<YOUR_CLOUD_RUN_URL>"

function apiRequest(path, body) {
  var options = {
    "method": "post",
    "payload": JSON.stringify({
      sheetId: SHEET_ID,
      ...body
    }),
    "contentType": "application/json"
  }
  UrlFetchApp.fetch(CLOUD_RUN_URL + path, options)
}

function exampleButton() {
  var sheetName = "Sheet1"
  apiRequest("/example-sheets-write", {
    "sheetName": sheetName
  })
}

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu("Custom Menu")
    .addItem("Example Button", "exampleButton")
    .addToUi()
}

Make sure to update <YOUR_CLOUD_RUN_URL> with your own Cloud Run service, including the https:// but without a trailing / character.

The apiRequest is a general function for any API requests we want to make against our Cloud Run service, and it automatically injects the sheetId attribute into all body payloads.

The exampleButton function simply executes the apiRequest function for the endpoint we want, along with the sheet name.

We bind the exampleButton function to the UI button with the line .addItem("Example Button", "exampleButton") where the first parameter is the text on the button and the second is the function we want it to execute.

When you click the Example Button for the first time from the spreadsheet it will bring you through an authentication dialog, like those shown below.

Initial Auth screen, click “Advanced”

Click the “Advanced” button at the bottom of the page, and then “Continue to <App Name>”. This will then prompt you through the standard Google Auth. This allows the spreadsheet to execute code that calls your external API.

Once the authentication stuff is taken care of, click the Example Button again. You should see new data populated in the sheet when it finishes!

Values courtesy of our Cloud Run service

If you are getting an error use the Cloud Run logs to diagnose the issue.

That’s all!

We’ve done it! We now have a Cloud Run service that can write data to our spreadsheet for us. Build off of this platform to do whatever you need done in your spreadsheets!

The full repository for this code can be found in the repository below.

https://github.com/SamGallagher95/cloud-run-with-google-sheets

Feel free to leave any comments or questions you have on this post, I will do my best to answer them.