Published on August 01, 2019.
When building a marketing website, I almost always get away with building completely static content. But sometimes, a mostly static site needs just a few pieces of dynamic data. In these cases, a full React app might be overkill, as would implementing and hosting a proper webapp. This was the case for me when building The Next 50's website, thenext50.us. It's a marketing website, with an events page that updates every week or so. In my first version, which was completely static, I found myself needing to manually update a JSON file and redeploy my statically generated site; clearly a pain. I wanted my non-technical friends to be able to easily new events and update the website without my help.
Google Sheets as a backend was a natural first choice. It's where our event data lived in the first place, it's easy to use for a non-developer, and it's free. The problem was, Google Sheets doesn't have a REST API designed for this use case; it's not a CMS. There are apps that get close, like Glide, but have limits on what you can build. All I wanted was simple REST API to call from an otherwise static site to load The Next 50's events. We could have hosted the data in a proper SAAS like Contentful, but that would have changed the workflow and introduced a new tool to event planners. I wanted something free at small scale, and had a great API.
What if Google Sheets could publish to Firebase, and Firebase could serve as a read-only database and API? As former G Suite PM and former Firebase PM, I should say, it's a little unorthodox. Google Sheets is not designed to be a production database, and Firebase isn't meant to be just a middleman for data. But hell with all that, could it work?
Of course. It turns out, with a little Googling, I wasn't the first person to try this. I considered Edwin Lee's approach but decided against it, because he chooses to sync automatically on every cell; this approach meant that incomplete data could be sent to Firebase while a user fills out a new row in the spreadsheet. Borrowing from Martin Hawksey's sheet to JSON code and adding some tweaks to make things more user friendly, I built a prototype. My script associates each tab of the spreadsheet with a top-level node in the Realtime Database, and each column with a key. I created a new dropdown in the sheets UI called "Publish Website" that gives users an option to publish all sheets, or just one.
My primary goal was to ensure that the spreadsheet was easy to use for nontechnical people, so I made sure that there weren't any significant naming requirements for column or tab names. To achieve this, I added a bunch of data cleaning logic, including additional cleaning functions for specific fields. This means that users of the sheet input whatever phone number format they want, but the database has a consistent model.
And with that, I was done! You can see this in action on The Next 50's events page, or check out the code below.View Gist