Bar Tool -Google Sheets as a back-end for master data

6 min readDec 30, 2021

I like to make tiny SaaS apps to solve my own problems, and in the hope that it helps other people with the same problem. Lately that has meant building apps to help with my hobbies, and the latest one of my hobbies to help out is mixology.

For this project I used my usual toolkit of Node.js front and back with Material UI and React at the front, Firebase for auth and storage, and a Google Sheet for my master data behind a Google Cloud Function to serve up an API.

A couple of years ago, after spending a couple of weeks ordering everything on the cocktail menu at a resort in the Philippines, I wanted to recapture the cocktail magic at home. Like a number of my obsessions, this led to working through a list — in this case the International Bartenders Association 77 official cocktails. Since I was going to the trouble of buying ingredients and making the drinks, I figured I would post them to Instagram as well. A couple of years later I have a well-stocked bar, and have posted over 200 cocktails.

I’ve already posted on the challenge of unit conversion for the amateur bartender, but here I’ll talk about my stack and tool-kit for an app to help me and others with the mixology obsession.

This is the “finished” (it’s never really finished) product:

The “business” problem

Rattling up a drink tends to take one of the following forms:

Making a recipe that I’ve found somewhere
Starting with a recipe and then riffing on it
Creating something from scratch based on what is in my collection (or someone else’s collection if I’m out visiting)

I’ve built a tool that provides:

Adding together quantities of ingredients (and optionally brands and products)
Unit conversion between standard metric and US bar measures, and other common units like teaspoon, tablespoon, and bar spoon
A glassware database with an indication as to which glassware the cocktail will fit into
Multiples for the number of “serves” — this takes care of the right measurements when making drinks for more than one
A catalog of ingredients and products, including the rums of the Smuggler’s Cove tiki cocktails book
A catalog of the classic recipes according to the International Bartending Association (IBA)— and easy riffing on these
A handy copy-to-clipboard in the usual recipe format for Instagram posts

Master data

According to the Wikipedia entry:

Master data represents “data about the business entities that provide context for business transactions”

For this application, I have a few key entities:

Ingredient (e.g. gin, rum, raspberry liqueur)
Product (e.g. Bombay Sapphire, Appleton Signature Blend, Chambord)
Recipe (e.g. Old Fashioned, Dry Martini, Piña Colada)

For the solo developer, I find that master data maintenance can be annoying. There’s a lot of work to do to provide end users with quality experiences, and having to create a whole suite of admin experiences for master data maintenance is frustratingly time consuming. I wanted to focus on creating a tool that makes riffing on a classic IBA recipe easy, not a tool that makes maintaining a list of ingredients, products, and classic recipes easy. There is good news though: spreadsheets are excellent user interfaces for maintaining master data. A spreadsheet has copy and paste, formulas, fill down/right, and a whole lot of ways to make bulk editing much easier. Google Sheets also provides this as a realtime, multi-user experience with version history!

An API over Sheets

I’ve written about the Google Cloud Functions + Google Sheets pattern before, but I’ll go through the particular approach I took this time.

Access to master data is all pretty much the same, so I went with a single Google Cloud Function endpoint on this one, which I unimaginatively called “data”.

Setting up a Cloud Function is trivial. To create a “data” endpoint I like to create a subdirectory called functions in my project repo, and then:

Create an index.js in the directory that exports a function called data with request and response parameters (ExpressJS style)
Create a GCP project (or use an existing one)
Run gcloud init in the directory to select the GCP project and authenticate (using the GCP CLI)
Run npm install — save-dev @google-cloud/functions-framework to set up the local testing environment
Deploy withgcloud functions deploy data — trigger-http — runtime=nodejs14

I have a spreadsheet with a tab for each of my master data entities in my Drive in Google Workspaces (formerly known as GSuite).

To make it accessible to my Cloud Function I just share the sheet to the email address of the default service account for my GCP project. The email address is usually the project ID at the GCP app domain. E.g. if the project ID is foobar then the address is foobar@appspot.gserviceaccount.com. At the time of writing, the service account details, including the email, are in the credentials tab in the GCP project settings.

Once the sheet is shared with the service account, it’s only a few lines to pull the data:

range is a string in the usual sheets format, e.g. “A1:F200”

SPREADSHEET_ID is the long alphanumeric code that appears as part of the URL for the Google Sheet. E.g. in a sheet with this URL:

https://docs.google.com/spreadsheets/d/1YPcXuXSnhia4PVVtf5szWxev8shLehF49sssAbrqpsc/edit#gid=0

The ID is: 1YPcXuXSnhia4PVVtf5szWxev8shLehF49sssAbrqpsc

Local development environment

The automagical authentication is great in the production service, but the deploy cycle takes a couple of minutes, so it’s useful to be able to test the Google API function locally. To reproduce the way the function accesses the sheet with the service account locally, it’s just a matter of downloading a JSON file with the keys in it, and setting the location of this in a Google-defined environment variable.

The credentials tab has the service accounts and clicking on the default service account takes us to the account details which include a “keys” tab. The “Add Key” function creates a new key with a one time download of a JSON configuration file.

Creating the JSON keys file for the service account for local dev access

I put the JSON file in my project directory and add it to .gitignore so I don’t accidentally add it to my repo. The location of the file needs to be exported to the GOOGLE_APPLICATION_CREDENTIALS environment variable before running the function in the local environment. Running a function locally is a one-liner with the GCP functions framework: it opens a port (8080 by default) where the function can be called as if it’s running in the cloud.

I find it easiest to do this with an NPM script target in the package.json in my functions subdirectory, alongside a target for doing a manual deploy to GCP e.g. for my function called data:

CI/CD

I’ve covered how I use Github Actions for CI/CD for Google Cloud Functions in an earlier story, and it’s no different here. The community action google-github-actions/deploy-cloud-functions that Google provides works beautifully and the only extra configuration required is to store the same JSON file that I use for local development as a Github secret.

Summary

Building user interfaces for master data maintenance is time-consuming and unnecessary when Google Sheets makes such a handy place to keep and maintain master data. With a simple API built in Google Cloud functions and the automagical authentication via service accounts, you never need make a master data maintenance UI again!