A Google Sheet combined with a bit of scripting makes the perfect backend for prototypes and small apps. Here’s what I’ve learned from three years of hacking.
In any introduction to application architecture you’ll encounter the affectionate acronym CRUD: create, read, update, delete. These are the four basic actions that app users can take on whatever data the application handles.
In the earlier days of the web it was all about the R. The other three were “admin” functions, handled upon request by The Webmaster. A shift towards creating has given rise to Facebook and selfies, but there are still a lot of cases where the majority of users receive the majority of the benefit based solely on a “read only” view of information.
At Ancestry, I help the designers on my team create prototypes with real (or at least realistic) data. When I’m not doing that, I’m throwing together simple tools, dashboards, and Slack integrations that are used across the broader Product division. In most of these cases, the focus is on the viewing of information; we need to get feedback from users on a new family tree display, or see at a glance what user tests are happening this week. The admin functions still need to be there, but in a purely utilitarian way accessible to only a handful of people. Time spent developing an interface for the CUD interactions is time wasted.
Between Heroku, AWS, myriad BaaS platforms, and framework scaffolding first made popular by Ruby on Rails, you can build and deploy a basic CRUD app pretty quickly. I’ve left a wide wake of unmaintained EC2 instances and lonely Heroku dynos.
Over the past three years I’ve become a fanboy of an unlikely solution that’s got much more functionality than most developers realize: the mighty Google Spreadsheet, backed by Google Apps Script.
Spreadsheets: The Ultimate Denormalizer
I’m not going to cover Google Apps Script in detail. Just know that a.) it’s essentially a superset of JavaScript with all kinds of built-in goodies, like sending emails with zero configuration, and b.) you can deploy a script as a web app. Connect that script to a spreadsheet, and you’ve got yourself a NoSQL solution running on Google’s cloud without the overhead of Google Cloud.
So why is this awesome?
Managers love them some spreadsheets
Excel is still everywhere. A product manager will send me an Excel file, I’ll import it into Google Sheets, apply my handy script template, and share it back with them. While I start building out the view, they can see and edit the data in a way that’s familiar to them. Beat that, scaffolding.
Security and user/permissions management
You know what’s better than an OAuth 2.0 helper library with clear code examples? Not implementing OAuth at all. Just share the spreadsheet with whomever should have admin rights, and separately share the deployed web app with a select few or the general public.
Suitable API with JSONP support
I’m not referring to the actual Google Sheets API with its weird response formats and authentication hassles. A Google Apps Script deployed as a web app gives you an HTTP endpoint, and you can optionally set it up to handle JSONP requests. This setup is my go-to for single page and static apps.
Triggers
Whether you want cron-like timed functions or a Webhook called every time somebody updates the spreadsheet, triggers are simple to create. They’ll even email you (at your desired frequency) with error reports.
Simple proxy
Google Apps Script can both make and receive HTTP requests, fetching data from various sources and passing it along. I recently built an app that receives texts via Nexmo, stores them in a Google Sheet, and updates a dashboard in realtime thanks to PubNub. The dashboard is just static HTML/JS/CSS. In another real-world example, see my post on sending Wufoo results to Google.
Next time you’re at a hackathon and somebody suggest firing up EC2 just to shuttle Twilio messages into a spreadsheet, stop them.
Basic version control
It’s no Github, but creating and deploying different versions is really straightforward. You can roll back to an old version of your code by selecting it from a dropdown and clicking “update.”
Chew your own CUD
Not every app I write leaves the creating and updating interactions to a spreadsheet, handy as that can be. Once I’ve proven that an app has a little staying power, I’ll start building out more functionality that allows users to manipulate the data without opening the spreadsheet.
Uptime and cost
Google’s uptime is pretty hard to beat, and it’s free.
The fine print
There are undesirable quirks about using Google Apps Script and Sheets as a backend. Here are a few of the reasons why I still write plenty of Node.js apps that get deployed to “real” hosts.
Latency
When you call the endpoint that Google gives you for your script, it redirects to a one-time (or time limited) URI. Between that and the fact that reading hundreds of rows from a spreadsheet isn’t tuned for Redis-like performance, you’re not going to see sub-second response times.
No CORS
JSONP is a fine hack for most of my use cases, but it has its flaws.
Ugly URLs
That CRUD API template I’ve created handles everything, but you have to use the endpoint that Google gives you, and it ain’t pretty. There are no human-friendly resource names. You can’t use a CNAME to make the domain look like your own. You get a single endpoint that you can GET or POST to, and distinguishing between different actions and resources all has to be handled via query string parameters, e.g.
?collection=cars&id=123&_method=DELETE
Quotas
Google has storage, compute, and transfer limits. They’re pretty high and I’ve never hit any of them, but don’t plan on building a Bittorrent service. Spreadsheets can have millions of cells and tons of separate worksheets, but if your app has a million rows of data you probably haven’t read this far anyway.
Finally, the template
Take a minute to read up on the basics of Google Apps Script. Then see a real-world example of this script in my short post on Serverless Slack Integrations. See also Wufoo responses to a Google Sheet. If you’d like to try writing your own app, feel free to start with my Spreadsheet + Script API Template, and follow the steps in the README tab. It’ll jumpstart your project with code already in place to handle all the CRUD actions, the paging of results, auto-generated IDs, and more. I apologize for the lack of proper attribution — many of the little helper functions in that script have been stolen and tweaked from various places over the years.
Enjoy.
