Serverless from the ground up: Adding a user interface with Google Sheets (Part 2)

Welcome back to our “Serverless from the ground up” series. Previously, you’ll recall, we watched as Alice and Bob built a simple microservice using Google Cloud Functions to power a custom content management system for their employer Blueprint Mobile. Today, Bob drops by Alice’s desk to share some good news. Blueprint Mobile will start selling and repairing handsets from the popular brand Foobar Cellular. Not only is Foobar Cellular wildly popular with customers, but it’s also really good at documenting its products. Each handset comes with scores of documents. And it releases new handsets all the time.

Alice realizes that it’s time for her simple v0.1 solution to scale up. Adding a new doc to the getHandsetDocs() function every few days isn’t so bad, but adding hundreds of documents would make for a tedious afternoon. Besides, this service has become a core resource for the team, and Alice has been wanting to improve it, but just hasn’t found the time. Supporting Foobar Cellular products is the motivating factor she needs, so she clears her schedule for the rest of the day and gets to work.

This time, Alice wants any technician to be able to add docs to the system, regardless of their ability to write or deploy code. No team resource should ever depend on a single employee, after all. So she’ll need to create a friendly user interface from which other employees can view, add, update, and delete the docs. Alice considers writing a web app to enable this, but that would take days and she can’t ignore her other responsibilities for that long.

Since everyone at the company already has access to G Suite, Alice decides to use Google Sheets as the user interface for viewing, adding, updating and deleting docs. This has the advantage of being collaborative, and makes it easy to manage editing rights. Most importantly, Alice knows she can put this together in a single day.

First, Alice creates a spreadsheet and adds a few document records for testing:

In order for the Google Sheet to communicate with her existing cloud functions, Alice looks up the email address of the auto-generated service account in the Google Cloud Console for the project. She clicks the Share button in the spreadsheet and gives that email address “read-only” access to the spreadsheet.

Then she writes a new version of the getHandsetDocs() function. The previous version of that function contained a hard-coded list of all docs. The new version will read them from the spreadsheet instead.

Then she gets the spreadsheet ID from its URL:

Now Alice has everything she needs to code the function that reads from the spreadsheet. The first few lines of the function create an auth object for the spreadsheets scope. No tokens or account details are needed because Cloud Functions support Application Default Credentials. This means the code below will run with the access rights of the default service account in the project.

After getting the auth object, the code creates a handle to the Sheets API and calls values.get() to read from the sheet. The call takes the sheet’s ID and the region of the sheet to read as parameters. The result is a two-dimensional array of cell values. Alice’s code repackages this as a one-dimensional array of objects with the properties handset, name and url. Those are the properties that the clients to the handset microservice expect.

Now any technician can maintain the list of documents simply by updating a Google Sheet. Alice’s handsetdocs microservice returns data with the same structure as before, so Bob’s web app does not need to be updated. And best of all, Alice won’t have to drop what she is doing every time there’s a new doc to add to the repository! Alice proudly calls this version 0.2 of the app and sends an email to the team sharing the new Google Sheet.

Using Google Sheets as the user interface into Alice’s microservice is a big success, and departments across the company are clamoring to use the system. Next up, part 3, where Alice, Bob and Carol make the system more scalable by replacing the Sheets backend with a database.