Capturing User Emails in Google Sheets for PrimaryCast
A technical exploration into saving contact form emails into Google Sheets using AWS serverless tools
In working with the team at Grassroots Analytics, Graphicacy’s engineering team tackled a particular challenge: how to ensure minimum costs to a client while storing changing data and providing updates on fast-changing predictions. Our goal was to maximize the impact of PrimaryCast, a data visualization and mapping tool that provides predictions for the Democratic Primaries. Like many similar non-profit organizations, Grassroots Analytics needed to track this information to keep their supporters up-to-date on primary predictions and to increase their donor base.
Our mandate was to rely on an open source approach in order to ensure minimum possible cost to our client. This framework carried the additional benefits of removing the need for deploying and maintaining a database or needing to learn a new CMS API. Consequently, we developed a lightweight, adaptable, and understandable backend solution.
Our research revealed an in-depth walkthrough of how to create a service account within the Google Developer Console, and to provide that service account “edit-access” to a Google Sheet. Once we had everything set up and our spreadsheet was ready to accept email addresses and timestamps, we took an extra value-adding step to secure the keys Google provided. Given the ease of getting an AWS serverless function set up with the Serverless Framework and Node.js, we created and deployed an AWS Lambda function behind AWS API Gateway. Once deployed, this enabled our ability to post the validated email address from the form to Google Sheets with only exposing a single API route on the front-end.
As we look toward the future, many of our clients come to us with data stored in many different formats, but chief among them are spreadsheets. With the serverless framework outlined above, we can now power our React and D3.js visualizations with real-time data directly from Google Sheets.
Christopher Lanoue is a Creative Technologist/Data Visualization Engineer who is currently the Director of Engineering and Innovation at Graphicacy with a focus on designing and building innovative and creative solutions for mission-driven clients all over the world.
