Yesterday was Town Meeting Day in Vermont. For the uninitiated, Town Meeting Day takes place on the first Tuesday in March and is a day when Vermonters get together in their town halls to make decisions about their local government. From the perspective of a media organization needing to report results, it's a lot like an election day.
At VPR, we needed an easy way to share results. We wanted something a little more elegant than a public link to a Google Spreadsheet but resilient enough to handle modest amounts of traffic and flexible enough that a reporter could easily update it without needing to edit any HTML. Oh, and it also needed to work in our CMS.
Here's what we built:
Sure, this isn't the flashiest election results page and it's hardly on the bleeding edge of design or of tech, but we can update the content in real time, the page is fully responsive and, best of all, it plays nice with our CMS when embedded as an iframe.
How'd it do? In less than 24 hours, it became one of our top five content pages of the past year.
If you're a web developer in need of a similar tool or are a journalist looking to broaden your toolkit to include a few tech tricks, here's a detailed explanation of our process.
Google Sheets to manage results
We use Google Sheets (a.k.a. Google Spreadsheets) as a lightweight CMS in a lot of places on VPR.net. We updated our Olympic Results dashboard from a Google Sheet and our prize page gets updated frequently during the pledge drives. Even the top stories in our homepage are managed using a Google Sheet. Using one to actually display tabular data seemed like a no brainer, so that's what we did.
jQuery CSV to Table plugin to grab the data
One of the nice things about Google Spreadsheets is that you can publish them to the web and grab the data in various formats. For our Town Meeting results, we published the results as a CSV. This allowed us to use a jQuery plugin to consume the CSV and return a clean HTML table.
Don't know any jQuery or javascript? No worries, pulling this off is pretty simple. You just need to be able to write some basic HTML.
Here's our index.html:
We use Flask to generate HTML (which uses Jinja templates) but you can write straight HTML and get the same result. To see exactly how we did it, check out our iframe repository on Github. In the above document, we include one stylesheet (using the Bootstrap CDN) and three javascript files. The first is the Google CDN copy of jQuery, next is the CSVToTable plugin and the last file we'll need to write. You'll need to download the plugin and then move it inside your project directory so that it's got the following structure:
Now you'll need to write script.js:
To get your spreadsheet's URL, you'll need to be using the old Google Sheets. If it says "Try the new Google Sheets" at the bottom of your spreadsheet, you're good to go; if it says "New Google Sheets", click on that button and then the link that says "Switch back to the old Sheets" (see photo below).
Once you're sure you're using the old Google Sheets, go to File >> Publish to the web, click "Start Publishing" and select "CSV" in the "Get a link to the published data" dropdown. Then copy the resulting URL and paste it into script.js.
Now open your page and you've got a beautiful table, served from static HTML that can be updated just by updating the spreadsheet. Pretty cool, huh?
For more last-minute newsroom hacks and some more ambitious efforts, follow Matt on Twitter.