How spreadsheets can make journalists’ jobs easier and projects better

Posted on May 17, 2015 by Patrick Garvin

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.

How to make your own Mr. Map Generator with your own SVG

Posted on March 26, 2015 by Patrick Garvin

A few days ago, I wrote about a responsive mapping tool I created called Mr. Map Generator and explained how to use it. Charles Apple blogged about the reasons behind why I created the tool. In the days since, people have reached out to ask me how they could make similar generators for their own cities, states, countries, and so forth.

The aim of this post is to walk you through the process of how to do that, starting with an Illustrator AI file and ending with testing it to make sure it works. It might seem like a tedious process, but it will be worth it in the end when you can easily generate maps rather quickly.

Here is a zip file that includes all you’ll need:

An Excel file for your data
An Illustrator file I’ve created for this exercise
A folder called “The files to make the generator”
A folder called “The files to make your responsive map”

Open up the Illustrator file called mapGen.ai. In this example, I’ve put together a map that divides the US into three regions: the west, the southeast and the northeast. This was an arbitrary selection of just grabbing states and making a path with the pathfinder tool. Meaning that… No, I don’t think the northeast extends down to the midwest. It’s simply relative to the other shapes, as it is the most northern and eastern of those shapes.

Each section of the map has its own layer in the Illustrator file. Furthermore, each layer has a name describing the content of said layer: southernUS, northeasternUS, westernUS. These names are one word, with no spaces or breaks. This is important.

To be safe, I select everything in the layer and then use the pathfinder tool to combine those elements to make a compound path. You can also go to the objects menu, select compound path and then select make.

Save the Illustrator file as an SVG. It’s easy to do in the “Save as” settings.

Illustrator will give a pop-up and you can leave it be.

Once you’ve saved the file as an SVG in Illustrator, open it in a text editor. Programs like Notepad or TextEdit are free with your computer. They are perfectly functional, but I use TextWrangler, which you can download for free. It color-codes tags in HTML and makes it easier to organize what you’re writing.

When you open the file in a text editor, it could look rather daunting if you’ve never looked at SVGs in text editors. Looks like a lot of gibberish, right?

And yet while it looks foreign at first, certain things will look familiar. Just as p tags and div tags need opening tags and closing tags, so do g tags. For the sake of this exercise, let’s think of g tags as like div tags for SVGs.

Look on one of the earlier lines in the code, probably around line 6.

You see that, right? Of course you do: one of the g tags has an ID of “southernUS.” It has that ID because that’s the name of one of the layers from the Illustrator file. Whatever those layers are called will be the same names used as the IDs for the corresponding g tags. That’s why it’s important to name them something easier to distinguish from each other than just using “Layer 1,” “Layer 2” and “Layer 3.”

Look just above that line to line 5, particularly the part that says “viewBox.”

Look at the part that says viewbox=”0 0 337.5 212.5.” That 337.5 means the box for your SVG is 337.5 pixels wide and 212.5 means it is 212.5 pixels tall. We’re going to eventually delete that part, but before we do, write down those two numbers somewhere or copy them into another file.

Write them down yet? Ya sure? OK, good.

In your text editor, highlight everything from the first line until the space just before the first g tag.

Got it highlighted? Good. Now delete that.

At the bottom of the document, highlight everything after the last closing g tag. And then hit delete.

Now we just have to clean up the file a bit.

In my original file, the shapes had a black stroke and a white fill. We want to get rid of that. Do a find-and-replace search for fill=”#FFFFFF” and replace it with nothing.

Next, do a find-and-replace search for stroke=”#000000″ and replace it with nothing

The last find-and-replace search is to find all the g tags that include an id. Our file will have three.

By adding class=”state,” we are assigning a class to our three g tags. That class is defined in the CSS files already downloaded.

Now select all that’s in the document and copy it. We’re ready to add it to our two different HTML files.

We have two files: baseForm.htm and starter.htm. The first one, baseForm, is the template for the page where you actually paste your data, click the buttons, decide your colors, preview your map, etc.

The other file, starter.htm, is the blank file that will become your map that you post.

In both files, look for the part that says, “HIGHLIGHT THIS AND PASTE YOUR SVG OVER IT!” Highlight that part, and well, do what it says: Delete it, and paste your SVG that you’ve cut and pasted.

In starter.htm, that is going to be on line 34.

In baseForm.htm, that is going to be on line 89.

While you’re in those files, notice the parts that say “viewBox” and “enable-background.” Particularly, notice that they each include a line that says “0 0 XXX XXX.”

This is why I told you to write your width and height down. Or paste it somewhere. Because now you need it.

Paste over “XXX XXX” with your actual width and height. So if your SVG had a width of 300 and a height of 400, “0 0 XXX XXX” should become “0 0 300 400.” In our case with our files, we had a width of 337.5 and a height of 212.5, so we changed “0 0 XXX XXX” to “0 0 337.5 212.5” in our files.

Save the two HTML files, close them, and go back to the two folders. In both folders, there will be a JS file called responsive.js. Open both of those.

The top three lines of each one will look like this:

In both files called responsive.js, change the XX to reflect the appropriate value. Our exercise, with our three regions of the US, had a width of 337.5 and a height of 212.5.

Save the JS files and close them. The only thing left now is to format your spreadsheet.

In the zip file you downloaded, open the Excel spreadsheet called data. It should be blank and look like this:

We have three columns: “G id name that matches layer names in Illustrator,” “What name you want to appear in the rollover,” and “Dataset.”

We’ll use an example from the previous blog post about how to use Mr. Map Generator. Here is a screen grab we had of state-by-state data about the “Dawson’s Creek” finale, showing the percentage of people in each state who rooted for Joey to end up with Pacey, Dawson, or neither.

Notice that the first column is the US postal codes, the second is the name of those states, and columns three through five are actual numbers.

The postal codes match the names of the g tags in the SVG. So, the row with MA in the first column corresponds to Massachusetts, and the g in the SVG has an id of MA. For this to work, the text in the first column has to match the g tags in the SVG files.

In our Illustrator file, we gave the layers these names: southernUS, northeasternUS, westernUS. Thus, our g tags have the ids of southernUS, northeasternUS, and westernUS. Thus, we have to use those in our first column in our Excel spreadsheet.

The second column is where we type whatever we want to see when the user rolls over the shape. That can be whatever you want.

And the third, fourth, fifth, sixth, seventh, and forever onward columns can be, well, the actual data.

So now you’re ready to test out your generator!

Copy your data from your spreadsheet, and in baseForm.htm, paste it to try it out.

If it doesn’t work at first, refresh it once or twice. If it still doesn’t work, check console.log to see if any errors appear.

If it does work, copy the code from step 8 and then paste it in code.js.

It might seem like a tedious process, but it will be worth it in the end when you can easily generate maps rather quickly.

How to use Mr. Map Generator, a map tool for journalists

Posted on March 24, 2015 by Patrick Garvin

In a recent blog post about journalists using technology to make their lives and jobs easier, I mentioned that I created templates in Illustrator of the maps we use the most. I saved those as SVGs and then turned those SVGs into HTML. The heavy lifting was writing code for a map generator that would allow me to copy and paste spreadsheet data to color-code the maps for me. It took some time, but it paid off. It creates responsive maps that I can easily pluck onto the website, but I can also print-to-PDF those suckers and have the basis for the print version. People who have made vector maps will know that the “old way” of having to select each state/county individually to color-code it was a pain. And fraught with the possibility of introducing error.

Mr. Map Generator has maps of the US, Massachusetts and Boston. For this post, I’ll explain how to use the US map generator, though the concepts will be almost the same for the other map generators.

GETTING STARTED

1. We’ll start with a spreadsheet. I usually use a Google Spreadsheet or an Excel Spreadsheet. In our fake spreadsheet, we will pretend it’s a spreadsheet about the “Dawson’s Creek” finale, showing the percentage of people in each state who rooted for Joey to end up with Pacey, Dawson, or neither. Obviously, these are made-up numbers, because most people will agree that Joey should have ended up with Pacey. But for the sake of this exercise, we will pretend.

Format your data with state postal codes in the first column, state names in the second column, and data in however many columns you want, starting with the third column. You can download a zip for the responsive files here or if you just want to make a print map, download the Excel template here. Neither the postal codes or state names need to be in any order.

2. On the page of the US map generator, paste your data in the field that says, “Paste your data in this field.”

3. After pasting your data, click on the button that says, “Click here to select your data set.”

4. After you’ve done that, you’ll get a button that shows you the columns from your spreadsheet. In this scenario, as we’ve created a fake spreadsheet, our options are “Rooted for Joey ending up with Pacey,” “Rooted for Joey ending up with Dawson,” and “Rooted for neither Dawson nor Pacey.” For the sake of this exercise, I’ve clicked on those who “Rooted for Joey ending up with Dawson.” Which, of course, was not how it ended.

5. Step 5 allows you to select your color from the options of red, blue, a gold/yellow, green, and purple. Step 6 allows you to decide whether you want the map to have 2, 3, 4 or 5 colors, or to be a chloropleth where the smallest value is the lightest shade and the largest value gets the darkest shade.

6. In our case, we’ve opted to have four shades of red. The generator is designed to calculate your smallest value and your largest value, and then calculate the ranges in between those two values. I strongly suggest not leaving those values as is, for a variety of reasons:

Outliers that skew the ranges
Ranges that aren’t intuitive (6.1-11.6, 11.6-17.1, etc.)

7. Once you have selected your color and ranges, you can click a button to preview your map. Do that.

Here’s what your map will look like:

8. You’ll then have the option to generate your code. It will appear in a text box.

9. To make a web version, download these files if you didn’t already do so in the first step. Copy the code in the box above and paste it inside the file called code.js. Then open the HTML in a browser. You don’t have to edit or change any of the other files. When you upload it to your server space, you’ll need four files:

code.js
map.css
US-map.htm
US-responsive.js

10. You’ll need to add a key/legend on your own. If you inspect element on this page, you can copy the HTML you’ll need and can plunk it into US-map.htm before uploading it.

11. To use this to make a vector print map, right click and select “Print.”

12. In the print dialog box, select “Save as PDF.” I usually have it only save page 2 of the PDF, rather than saving the entire file.

13. I then open that PDF in Illustrator. Using the white arrow, I select everything I want to delete. I delete until all is left is the shapes of the states.

14. Once I’ve deleted the extra parts from the PDF, such as text characters, I have only the map left. I then plug that into a Globe template file. I’ve done enough of these that I have a starter file with the state labels in place.

If you see yourself making a lot of these, having an Illustrator template with a layer of labels is not a bad idea.

Patrick Garvin: Blog

Tag Archives: Mr. Map Generator

How spreadsheets can make journalists’ jobs easier and projects better

How to make your own Mr. Map Generator with your own SVG

How to use Mr. Map Generator, a map tool for journalists