A Dead-Simple CMS with Google Sheets and Firebase

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.

The Idea

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.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Publish Website')
      .addItem('Publish this sheet', 'publishSheet')
      .addItem('Publish all sheets', 'publishAllSheets')
      .addToUi();
}

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.

var dataCleaners = {
  "email": function(email) {
    return email.toLowerCase();
  },
  "phoneNumber": function(phoneNumber) {
    // Strip everything except numbers and the + sign.
    return phoneNumber.toString().replace(/[^+\d]*/g, "");
  },
}

The last step was to write some basic JavaScript to load and render the Firebase data on my static site. Because I didn't need real time updating or any complex rendering, I implemented this as very basic JavaScript:

function _renderEvent(event) {
  return (
    '<li class="event">' +
      // ...
    '</li>'
  );
}

function loadEvents() {
  var database = firebase.database();

  firebase.database().ref('/eventsDatabase/').orderByChild('date').once('value').then(function(snapshot) {
    var events = [];
    snapshot.forEach(function(j) { events.push(j.val()) });
    var upcomingEvents = events.filter(function(e) { return e.isUpcoming});
    var upcomingHTML = upcomingEvents.map(_renderEvent).join('');
    document.getElementById('upcoming-events').innerHTML = upcomingHTML;
  });
}