Vermont Public is independent, community-supported media, serving Vermont with trusted, relevant and essential information. We share stories that bring people together, from every corner of our region. New to Vermont Public? Start here.

© 2024 Vermont Public | 365 Troy Ave. Colchester, VT 05446

Public Files:
WVTI · WOXM · WVBA · WVNK · WVTQ
WVPR · WRVT · WOXR · WNCH · WVPA
WVPS · WVXR · WETK · WVTB · WVER
WVER-FM · WVLR-FM · WBTN-FM

For assistance accessing our public files, please contact hello@vermontpublic.org or call 802-655-9451.
Play Live Radio
Next Up:
0:00
0:00
0:00 0:00
Available On Air Stations

How We Built A Responsive Election Results Page In 10 Minutes

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).  

Switch back to the old Google Sheets by clicking on the button on the bottom of the sheet.

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.  

Publish your spreadsheet to the web, making sure to get a link to the sheet as a CSV.

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.

Matt was a web developer at VPR from 2013 to 2014. He joined VPR after learning how to program by launching his own web startup. He studied Physics as an undergraduate at the University of Maryland and found his way to Vermont in 2010 and fell in love with the place. Matt is an avid rock climber and a backcountry skier and—when not in front of his computer—can be found in the mountains or wandering along forest streams, fly rod in hand.
Latest Stories