The jankiest, cheapest API™ I could make

tl;dr: Use Google Sheets as a backend for simple MVP type apps, you get all the spreadsheet UI over the database 'for free'

Setting the scene

So, imagine you’re building an app for a friend, MVP type thing. You’ve cut it down to the bare minimum functionality and you’re going to need:

1. Some public data that anyone can access
2. Some private user data that only the user can access
3. Some way for business folks to see/update/delete any data
4. And I don’t want to on the (DevOps) hook for when services go down.

Bonus features: ideally quick to build and secure.

A solution:

For the data storage parts there are a dozens of solutions for this (#teamPostgres), but the requirement that business folk need to be able to see/modify the data was going to be the real ‘time sink’ for the project. For an actual proper app you could build some kind of web UI/auth and all that but seemed like for simple apps there migth be a simpler way.

I’d heard stories that levels.fyi were running their backend off basically Google Sheets, and used Google’s provided ‘Sheets API’ to programatically interact with it. I’d seen an demo of a similar from an old collage (mentor really) R0ml and so I gave it a try and used the Google Sheet as the backend.

Spoiler: This turned out to mostly work, but with some fun edge cases. The ‘big win’ that once the data is in the spreadsheet you get leverage from the already built ‘Spreadsheets UI’ in Sheets and then business folks can use the UI already in sheets to do their buesiness tasks. This has been a real win.

Alas thou, the Google Sheets API wasn’t a perfect fit for exposing to our end customers and we needed an extra layer of API/abstraction to provide the right kind of interface to our end users. This could have just been a EC2/some App Server service but because I don’t want an ongoing ops burden of keeping servers up 24/7, if I could get a Serverless service in here then there I can push more of the ops to AWS and sleep easier at night. To glue between these the Google Sheets API and the API we wanted was really just a few small transformations, and for this Lambda seemed the perfect choice

So the basic architecture turned out to be:

Overview

The core idea is simple enough, the app makes API calls to a lambda, the lambda handles Google Sheets auth, calling Google Sheets and then transforms the data to the format the app expects and returns a response.

Going over the use cases:

> Public data that anyone can access

The simplest case, app calls here you can call the Google Sheets API, get back a bunch of rows and then just convert them to JSON and output. All this data is readable for the lambda using the Google Sheets API so no problems.

(In terms of wireformat I made everything JSON, mostly to ease parsing on the client side).

> Private User Data

This was more tricky, we did need username/password here for auth. So sent email/password over wire, hashed the password, stored uuid/email/hash in the sheet and then generated a token and stored that with the user UUID

When we needed to read/write private data, simple send the token along the request and then we can check the token for validity and then read/write accordingly

For the data itself, most of the work here was going from JSON over the wire to row based format (that Google Sheets is expecting)

> Business Folk seeing the data

This was a/The real win, once I’d got the data into Google Sheets, I could just share the sheet and they could see the data come in real time. I had to do a little explaining but for the most part it just worked

> DevOps stuff

So far no compliants. Wrote a Makefile to test and deploy the lambda, takes ~2 secs to test and 20 seconds to deploy.

I think the real hassle has been the Dev Experience of it. Lambda can be a little urghhh to test, and the Google Sheets API was a unknown to me. In the end I sperated these out and had a a sort of “Lambda Harness”, that would handle wire I/O but then isolated the core functionality into a pure-ish function and I could test that easily enough

Gotchas / Random Advice

>Auth

This was one of the bigger hassles to understand, and there are a couple of different types of auth floating around (and well as with all things auth is a faf).

You need some sort of user/role to connect to the Sheet and you can do that as authing as the end user but then they have to do the Google OAuth flow and I wasn’t super buzzing on that, so in the end I auth’d all the access to the Google Sheet via my personal account, and then pulled all access logic into the application layer (basically the same as three stage architecture in Enterprise apps). I don’t super love this and wish more applications would do Row Level Security but well here we

A rough outline of what to:

1. Create a sheet in Google Sheets, using the best URL: http://sheets.new
2. Allow access to the sheet to “anyone with the link”
3. Pull out the Sheet ID, i.e the ID "1YtQ6OvOmj2PEeaL7OfmAXCHe_RNvQ597fRXNLv0Yv6I" from the URL: https://docs.google.com/spreadsheets/d/1YtQ6OvOmj2PEeaL7OfmAXCHe_RNvQ597fRXNLv0Yv6I/edit and save it somewhere
4. Allow API access to your account: https://console.cloud.google.com/flows/enableapi?apiid=sheets.googleapis.com
5. Great a “Project” in Google API, this is a higher level object where all APIs for a given project live under
6. Create OAuth client ID,
   • https://console.cloud.google.com/apis/credentials
   • You need to press “+ Create Credentials” -> “OAuth Client ID”, then “Application Type” -> “Web Application”
   • Call it whatever you want
   • For “Authorized redirect URIs” use http://localhost:59042/. This is used later
   • Press “Create” and then “Download JSON”, which will download a file. Save in the repo as credentials.json

7. Run make get-token, this will open an OAuth flow, and write a token.json file that you can embed in the Lambda.

8. Take that token and make it readable in the lambda and use that as authentication for the Google Sheet API calls

>Libraries and version:

I found there to be a good amount of different libraries for accessign the different versions of Google Sheets, with at least a couple of different API versions and a couple of different interactions models. All doable, but also seemingly lacking a little in focus.

>Lambda Structure:

When you have a few different business functions you want to acheive with lambda, the two big ways you can do it are have a single lambda and then invoke that with different params and use the logic in the lambda to invoke different functions [^1]., or you can have different lambdas for each business function and then route the API calls at the client level.

I’m not an expert but generally speaking I think the current pratice is to do the many lambdas approach and then you get all the benefits of the platform (concurrency limiting, access controls etc) but well I didn’t really care about this for the jankiest,cheapest API™ possible, and one reason was….

>Cold starts

In reality I find cold start isn’t really a problem these days, start times have got a lot quicker (p95 @ 200ms?) and I think people just like to complain about them really. That said, this is for an iOS app and I include a /ping on the first app screen and

>Gogole Sheets API

I didn’t love using the Google Sheets API, it worked fine thou, but felt a little like I was making it do things it didn’t really want to do. If anyone at Google is reading this my dream would be you could just run SQL against the sheets.

Overall thoughts

Overall it really mostly worked for what we needed, there are certainly bits where the APIs are a bit clunky but it worked, would do again.

Feedback totally welcomed on anything and everything!