Chris Essig

Walkthroughs, tips and tricks from a data journalist in eastern Iowa

Archive for November 2013

How we used a Google spreadsheet to power our election app

with one comment

Election 2013 app screen shot

Last Tuesday was election night in the Cedar Valley. While we didn’t have a huge number of contested races on the ballot, we did have a mayor’s race in Waterloo and Waverly, Iowa, as well as city council races in several area towns.

As NPR’s Jeremy Bowers proudly proclaimed, election nights are exciting times for news nerds. This election, we decided to do a little bit of experimenting.

Before the election, we posted biographical information for all the candidates running in a contested race in Waterloo, Cedar Falls and Waverly. We also promoted other races in smaller towns. This gave our readers a good overview of the races on the ballot and information on the candidates. And best of all, it was all in one place.

Three reporters were responsible for getting the biographical information for each candidate and entering it online. We used a Google spreadsheet to store the information. This allowed the reporters to enter the information online themselves. I could then go into the spreadsheet, edit the text and make sure it was formatted correctly. We then used Tabletop.js to import the data into our app and Handlebars.js to template it.

The basic setup for the app is available on my Github page. This is a very similar setup mentioned in my last blog post.

Before the app went live, I exported all the data in the Google spreadsheet into JSON format using a method mentioned here. I did this for two reasons: 1) It spend up the load time of the app because browsers didn’t have to connect to the spreadsheet, download the data, format the data into JSON (which is what Tabletop.js does with the data in this app) and then display it online using templates rendered in Handlebars.js. Instead, it’s already downloaded, formatted into JSON and ready to be templated. And: 2) Tabletop.js has a bug that may cause some readers not to see any of the data at all. I wanted to avoid this problem.

The night of the election, we wanted to update the election results live. And we wanted to use the same app to display the results. Fortunately, the process was easy to do: I added new columns in our spreadsheet for vote totals and precincts reported. The new data was then pulled into our app and displayed with simple bar charts.

To display the results live, we had to ditch the exported JSON data and use the Google spreadsheet to power the app. This allowed us to update the spreadsheet and have the results display live on our website. We had one reporter at the county courthouse who punched in numbers for our Waterloo and Cedar Falls races. We had another reporter in Waverly who punched in results for races in that town. And we had another reporter in the newsroom who was monitoring the races in rural towns and giving me updated election results to enter into the spreadsheet.

The workflow worked great. While most news outlets were waiting for the county websites to update with election results, we were able to display the results right away. Unfortunately, the Black Hawk County website never wound up working on election, making our app the only place readers could go to get election results.

Our effort paid off: The app received about 11,500 pageviews, with about 10,000 of those pageviews coming the night of election (about 7,000 pageviews) and the following day. The app was more popular than any single story on our website for the month of October and November.

Reporters at the party headquarters said many of the candidates first tuned into the local television station to get election results but quickly went to our webpage when they realized we were the only ones with updated results. In fact, Waterloo’s mayor found out he won his race by looking at our website. Here’s a photo of him checking out our website on election night.

Which leads me to my last point: You’ll notice how our mayor is checking out the results on a smartphone. Our app (like all of our apps) was responsively designed, which means it looks great on mobile phones. It’s critical that news producers make sure their apps work on mobile. It’s pretty much mandatory. Because as our mayor shows, people love checking the news on their phones.

Advertisements

Written by csessig

November 10, 2013 at 10:19 pm