How spreadsheets can make journalists’ jobs easier and projects better

In a recent blog post about journalists using technology to make their lives and jobs easier, I mentioned that one of the ways I’ve made my job easier and more efficient has been to rely heavily on spreadsheets. I use spreadsheets to make color-coded maps, to update my gay marriage map/timeline, to help reporters and editors update projects on their own, and to easily organize my own interactive projects. I even use spreadsheets when making simple charts in Illustrator. Not just for the data, but adding the labels.

When we think of spreadsheets, it’s easy for us to think of them in terms of numbers: daily history of the Dow, changes in gas prices, budgets from fiscal year to fiscal year.

These are fine uses for spreadsheets, as we know, but we can use them for so much more than numbers and charts. We can, and should, be using them to help organize and execute a variety of alternative story forms, and not just things we know will visually be presented as grids. Some examples:

  • Series of bio boxes
  • Q&As
  • Timelines
  • Photo galleries
  • Map locations and associated text blocks
  • Step-by-step process explainers

And those are just some of the examples.

There are several reasons to use spreadsheets:

  • Shared Google spreadsheets mean that everyone can collaborate and edit a spreadsheet, rather than having to walk over to someone and dictate a change (or having to be the guy to whom all those changes are dictated).
  • This eliminates people making fixes by just sending body copy in an e-mail. If you send an e-mail, you don’t know if the manager of the spreadsheet has gotten around to updating it. But if you can go in there yourself, you know the change has been made.
  • By keeping the content in one Google spreadsheet doc that you can share on the cloud, you eliminate having multiple files that could confuse everyone. You know which file to use, because there’s only one, rather than ones called “version2,” “version2NEW,” “version2USETHIS.”
  • If everyone can see what’s on the spreadsheet from the beginning, it opens up communication and can potentially prevent problems later.
  • Having a spreadsheet means that whomever is working on the digital presentation can easily turn the data into a JSON that JavaScript and jQuery can use to write the HTML for you. If you have a spreadsheet with 20 rows of data about school board members, it’s a lot easier to write the script to write those div tags and image tags once rather than having to copy and paste the code 20 times. Copying, pasting, and deleting can be dicey, as you might delete div tags or forget to change the content of a p tag.

You might sense a theme in these reasons: Not only do they make things easier, they minimize or eliminate the introduction of errors. Cutting down on errors is good for us, our bosses, our readers and our sources. It’s good for everyone. Using spreadsheets to help me do that is worth any of the effort I put into it.

A colleague asked me for a primer on how I use Google spreadsheets, and I thought I’d share it here. For the sake of this exercise, we will assume we are creating a spreadsheet of bios on candidates of some kind.

For many people, the top of their Gmail inbox looks like this:

Click on the three-by-three square toward the right side of the top:

You’ll see multiple icons. Click on the one that says “Drive.”

You’ll be taken to a screen that should look like this:

On the left side, click on the big red button that says “NEW” and select “Google Sheets.”

You will then be taken to a new page: your blank spreadsheet.

To rename it, click on the part at the top that says “Untitled spreadsheet.” You’ll get a pop-up that looks like this:

In your spreadsheet, you can now fill in the columns as you please.

To share the spreadsheet with reporters, editors, designers, web producers and other collaborators, click on the blue share button at the top right.

In that share window, you’ll get the option to type in email addresses and decide what level of access you want to share. You can allow them to just view the spreadsheet, or you can allow them to fully edit it.

Once the spreadsheet is edited, the real fun begins. This is where producers, graphic artists, developers or whoever can use the spreadsheet to make the magic happen. The following steps are not going to directly affect reporters or editors, but they are cool steps that show just how awesome and powerful these spreadsheets can be for journalists.

Select all the data in your spreadsheet and copy it:

Go to Shan Carter’s Mr. Data Converter, a resource I use almost daily and the reason why Mr. Map Generator is named what it is.

Carter’s tool is fantastic: it converts your Excel or Google spreadsheet data into one of several web-friendly formats, including HTML, JSON and XML. You paste your data in the top box and it spits out your format in the bottom box.

For our example, we want the format to be JSON, in row arrays:

We then copy our JSON from the bottom box and add it our JS file. Using a combination of JavaScript and jQuery that cycles through a for loop, we append div tags to our document using elements from the JSON.

The HTML itself is only one line:

And the CSS is not complex, either:

Thus, we’ve been able to turn a spreadsheet that looks like this…

Into a digital presentation that looks like this:

It’s a rather simple process, in which all the players who need to edit the text can do so at once. By turning the spreadsheet into a JSON and having jQuery/JavaScript write the HTML elements for you, it makes editing easier. In the past, the designer, developer or producer handling the web project would be peppered with changes and edits from reporters and editors. This makes it way easier for everyone: just make the change you need in the spreadsheet, alert the the designer, developer or producer to update the JSON and that’s about it. There are ways to have the Google spreadsheet directly hooked up to your code, but depending on your newsroom’s setup, that could require some server access that not everyone has.

Earlier this year, Boston Globe reporter James Pindell wrote about the activists whose endorsements are key to Republicans in the New Hampshire primary. Data and multimedia editor Laura Amico, James and I used a spreadsheet to keep tabs on all of these people: their names, hometowns, current jobs, endorsements for 2016, 2012 and so forth.

I then used that spreadsheet to create our endorsement tracker. I wrote code that goes through the JSON, tallies how many people have endorsed whom, adds those up, and includes those numbers on the labels and buttons referring to the candidates. It also calculates how many fields in the spreadsheet are blank, meaning the person hasn’t endorsed anyone yet. The code does all the math for us. As more people endorse more candidates, we just update the spreadsheet, update the JSON and then re-upload it.

That’s it. No having to get out our calculators to add up how many have endorsed a candidate. No having to go into the HTML and look for the labels for each candidate. No having to go into div tags and no worrying that we are deleting a tag on accident. Just the JSON, and the code then automatically updates all the numbers and rewrites the HTML.

Another spreadsheet project that I worked on with Laura Amico was a primer on all the people in the case of Aaron Hernandez, the former Patriots player on trial for murder. Metro Editor Anica Butler, Laura and I formatted the sheet, adding links to stories about the various individuals connected to the case.

I then wrote code that would sort through the spreadsheet-turned-JSON and put the bios of these people in the proper categories: victims, family, legal, and so forth. Thus, if the editors decided to move anyone to another category or add/remove someone altogether, I didn’t have to change anything or move anything other than re-pasting the JSON in the file. That’s it.

Even quizzes can benefit from the content being organized in spreadsheet. Last year, The Boston Globe’s deputy editorial page editor, Dante Ramos, wrote a profile of each of the five tech hubs in the Boston area. For BostonGlobe.com, we thought it could be fun to present a quiz allowing users to find out which of those tech hubs suited their personality. The quiz relied on JavaScript, jQuery, an extensive JSON file and a whole lot of if/then statements. That JSON file was made from a spreadsheet that allowed many members of the editorial staff to read over the quiz questions and edit as they went along.

The most ambitious project I’ve used a spreadsheet for, though, is something a project I blogged about in late 2013..

For the last year and a half, I have been editing and updating this BostonGlobe.com interactive graphic about the history of same-sex marriage in the United States. There’s a sticky navigation that stays with you when you scroll through the timeline, shows maps and tallies that change as states allow gay marriage, ban gay marriage or allow civil unions or domestic partnerships. The timeline includes links to archived stories, copies of bills and statutes, and PDFs of past Globe front pages.

Each blurb on the timeline has its own row in a spreadsheet that has more than 700 rows:

Every time there is a new update or development, I use Shan Carter’s aforementioned great resource, Mr. Data Converter, to turn that spreadsheet into a JSON file. The jQuery reads that and appends a main div on the page with a div for each event, including nested divs for the headline, the text, the photos, the captions and the links.

The maps are SVG, taken from a template that former graphics director Chiqui Esteban created for our department. When the timeline is scrolled through, the color and tally changes are triggered by div IDs when those IDs are near the top of the screen. What the tally and colors should be are dictated by values in the JSON that was created from the spreadsheet.

Read more about that project here.

As you can see, these spreadsheets help in a variety of projects. Shan Carter’s Mr. Data Converter especially incentivizes the use of spreadsheets. Reporters and editors need not know about JavaScript or JSONs or any of the terminology. They need not know how the sausage is made to appreciate that these spreadsheets make it easier for them to edit projects and that it makes it super easier for graphic artists, developers, web producers and anyone else creating web projects.