Little Vermont’s been in the limelight lately—C-SPAN style. Between Sen. Bernie Sanders, now more well-known now than ever, and Sen. Patrick Leahy, the senior-most member of the Senate Judiciary committee, my team here at Vermont Public Radio knew that the new administration’s cabinet nomination process would be of key interest our audience. We knew we wanted to track our senators’ roles throughout.

It’s worth noting that national publications have built systems for this many times over. Every one I’ve seen is thorough, and each does a fine job of showing the complexity of the bigger structure. Each tracks the senate as a whole.

But my colleague Taylor Dobbs, VPR’s digital reporter, and I knew that our focus was local. We wanted to track not only our senators’ final votes but also the statements they made in advance. As a reporter, Taylor wanted a tool that was sharp, nimble and reliable. As a web developer, I wanted something that could function dynamically inside our temperamental content management system. I confess, I also wanted to avoid being bothered every time we needed a change in the database.

The result? A responsive table fed automatically from a Google Spreadsheet and embedded into our Drupal-based CMS.

Let’s Try This Thing

Here’s how we did it.

The first step was to create a Google spreadsheet in VPR’s Google Drive. Easy? Excellent. So much smoother than spinning up an instance of PostgreSQL.

Next, I headed to the Google Developer’s Console. I like to use gspread for Google Drive interactions in Python, which requires OAuth2 credentials. Once inside the console, I created a new project, enabled the Drive API, created a service account key, and established credentials in an acquired JSON file. I then took the email address listed as client_email in the JSON file and shared it with the Google spreadsheet itself. More of a step-by-step setup can be found here in the repository’s README.

For the code itself, I started from VPR’s app template. It’s a quick Python repository that uses Flask to deploy static web applications to S3.

First, I added my JSON credentials file cabinet-access.json to the project and included a reference to it in my .gitignore file. That second step is crucial if your code is going public. Don’t share your private credentials!

In sheet.py I’m calling the JSON file with json.load(open(‘cabinet-access.json’)), and I’ve made sure to fill in the exact name of our Google spreadsheet. From this file, I’m sending the result of get_all_records() to query.py, where it returns a dictionary of the spreadsheet results. Those results pass to content.html, where I’ve set up a _votes.html template.

This little HTML file holds the meat of the project, and I have the NPR Visuals team here to thank. I decided to use the responsive tables that Alyson Hurt put together in this blog post, and I styled them to match our CMS’s design. From there, all that’s left is an iteration over each nominee in our spreadsheet. Well, nominee iterations and a little wingdings pizazz for a successful check upon confirmation.

I pushed the code up to VPR’s S3 account and wrote a cron job to update the table every few minutes. Once the confirmation process is complete, I’ll shut off the cron and the table will be static.

Finally, a massive thanks to pym.js. It’s only a simple few lines of JavaScript needed in the code itself paired with a call in the source of our post to get this table embedded into our site.

<p data-pym-src=“http://www.vpr.net/apps/cabinet-votes/index.html”>Loading…</p><script type="text/javascript" src="//pym.nprapps.org/pym-loader.v1.min.js"></script>

And there you have it!

More Spreadsheets = Less Pain

Of course, this process can be replicated. Here at VPR, I use the Google Drive API often in projects ranging from simple graphics embeds to our Podcast Directory to Timeline, a web application built to showcase VPR Classical’s podcast about the history of Western musical traditions.

For these bigger builds, I’ve found that the process of using a Google spreadsheet as my database has helped to alleviate potential pain throughout the course of a project. Setup is quick. It doesn’t require a heavy technical load. It’s fairly painless for those of us lone junior newsroom developers who aren’t able to receive code reviews.

An added benefit? Google spreadsheets have allowed the non-developers in my newsroom to own technical projects. I have most VPR Google-powered projects set up on crons, which allows my hands to become free of the projects the second they publish. Updates happen automatically, and reporters or producers can bounce in and out of the spreadsheets as they please.

Developers, We Can Be Champions

It can be tricky, I admit, to step away from the spreadsheets and let others take ownership, no matter how much time they save me. The new reality of media is so all-hands-on-deck these days, especially in small newsrooms and especially in cities outside of major news markets. There’s such urgency that often I find myself wanting to save my colleagues a few spare minutes, so I’ll make the content changes myself. It’s not as if I don’t know how to update a spreadsheet, after all.

But I’ve had to remind myself what all-hands-on-deck actually looks like, for someone like me. Which can be hard to do—this is my first professional web development role, and I came to it from a background of writing, well, non-code. I held communications positions in the tech industry before coming to VPR, and I’ve worked with developers who forget to remove the Lorem Ipsum. Developers who misspell their client’s name. Developers who can’t care less about the content or the copy. It’s just not the exciting part of the project to them, and that’s OK.

I learned to code because I wanted to be a developer who did care.

But as the only developer in our newsroom, the most meaningful way for me to contribute is to maximize the time I spend writing code. Not only can I use my skills to build newsroom tools that optimize efficiency, but I can champion technical projects that require more non-developer involvement, therefore also encouraging more technical literacy and soliciting more creative ideas.

I have my fingers poised to tinker with the wide web that’s in front of me. I’m excited about the work ahead, and I’m eager to see what kinds of solutions other newsrooms can spin up, too.

• Share on Twitter
• Share on Facebook