Fast, flexible and free: visualizing newborn health data with Firebase, NodeJS and Google Sheets
At Global Strategies, a small but mighty non-profit based in Northern California, we use data to improve healthcare for women and children in the most neglected areas of the world. For three years, we’ve helped distribute medical kits to women who have suffered sexual violence in remote parts of the Congo. Every day a local Global Strategies partner calls participating clinics, updates stock levels in Logistimo, and plans restocking trips accordingly. This simple data-gathering and tracking system has meant that for three years 100% of women eligible for the medical kits have received them, in a part of the world where medication stockouts are common.
We have also partnered with the Preterm Birth Initiative at UCSF for a study of clinical decision support software we developed, called NoviGuide, at a clinic in eastern Uganda. This tablet-based software walks neonatal nurses through internationally accepted protocols for newborn care and helps calculate drug doses and feeding quantities. As the nurses care for infants, they generate observational and treatment data that is integral to our study. To capture, analyze, and visualize it, we tried several products (including Heroku and the now-defunct Parse) and settled on a stack from Google. The way we integrated Firebase, Functions, and Google Sheets is the focus of the remainder of this article.
Our goal was to visualize the data coming in from Uganda easily and flexibly. We did this by building a one-way pipe from our database to a Google Sheet, so non-coders on the team could perform calculations and create graphs from the live data coming in every day. In other words, we wrangled a front end to our database fast, free, and built by Google.
Data from client devices goes to Google’s Firebase, so when Cloud Functions was announced, we leapt at the chance to use it to connect up to a Google Sheet. Functions (similar to AWS Lambda) can eliminate the need for a server by running NodeJS code in response to an HTTPS request or a database trigger.
The challenge had two main parts:
- Respond to incoming data via trigger Functions
- Connect to a Google Sheet for programmatic read/write
First, let’s look at a simple trigger Function. When a client writes to our /alerts/queue path, a trigger Function responds by sending off an SMS message via Twilio. Here’s a complete index.js to achieve this:
Now let’s get into the nitty-gritty of connecting Functions to Google Sheets. The first step is to visit the Google APIs API Manager and create Oauth 2.0 credentials. Click the Create Credentials button, then Oauth client ID, then Web Application (Google’s directions are here). In authorized redirect URIs, you’ll need to enter {YOUR-FUNCTIONS-SUBDOMAIN}.cloudfunctions.net/Oauthcallback.
Which leads us to the code you’ll need to obtain and record (in your Firebase database) your Oauth tokens:
Once we have tokens stored in the database, we can use them for programmatic access to the Google API:
And that’s the basic idea! Real applications will be more complicated. One difficulty we encountered is that since we receive data in bursts, we wanted to allow it all to come in before writing to the Sheet. Our approach was to copy each new record to a /bursts/unprocessed path via a trigger Function. A lock object in the database is deleted (obviously only on the first copy), in response to which another trigger Function waits a few seconds for all records to come in before processing everything it finds in /bursts/unprocessed. The lock object is then restored.
Using Functions to build a live link between your Firebase data and a Google Sheet isn’t too hard — and it gives you all the amazing ways to analyze and visualize your data that are built into Google Sheets. Try it out!
Thanks to Josh, Sloane, and Jean, my Global Strategies colleagues, for ideas and feedback. Thanks to Gabriel from the Firebase Support team for kind assistance getting past roadblocks.
