How Our Digital Newsroom Uses Google’s Chart Tools

Fight disinformation: Sign up for the free Mother Jones Daily newsletter and follow the news that matters.

Here at Mother Jones, our reporters, editors, and army of fact-checkers hoard more troves of chart-tastic data than our 2.5-person interactive team can keep up with. We love quality charts and take great pride in those we’ve produced before—our visualizations of income inequality from March 2011 are still in our all-time biggest traffic items. But our booming daily content calls for a charting method that allows for faster, easier collaboration across the newsroom, and our go-to solutions—Illustrator and Excel—don’t always cut it.

So in June, our interactive editor Tasneem Raja asked me to dig into Google’s Chart Tools API. Two nice things about this approach: first, our reporters and editors already know and love Google Doc’s collaborative editing features. And second, since Chart Tools can hook into a Google spreadsheet, a reporter can easily update a chart visualization themselves by simply changing the data in the underlying spreadsheet. The API also comes with a suite of configuration options that allows you to customize your chart’s font, colors, and dimensions to better match your existing site styles (to an extent—more on that later).

Here’s how we got it working for us.

After some preliminary Googling, I found a Google bar chart example that used the Fusion Table API, and, even better, included some sample code. As you can see below, the chart mostly runs on JavaScript, pulling data from a Fusion Table and applying some basic queries and layout.

<html> <head>
    <meta charset="UTF-8">

    <title>Fusion Tables API Example: Google Chart Tools Bar Chart</title>

    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load('visualization', '1', { packages: ['corechart'] });

      function drawVisualization() {
        google.visualization.drawChart({
          containerId: 'visualization',
          dataSourceUrl: 'http://www.google.com/fusiontables/gvizdata?tq=',
          query: 'SELECT Year, Austria, Bulgaria, Denmark, Greece FROM 18k8XNgsc5ktLP2EHMCpoKIwymsVlzVV-xVuceA',
          chartType: 'BarChart',
          options: {
            title: 'Yearly Coffee Consumption by Country',
            vAxis: {
              title: 'Year'
            },
            hAxis: {
              title: 'Cups'
            }
          }
        });
      }

      google.setOnLoadCallback(drawVisualization);
    </script>
  </head>
  <body>
    <div id="visualization"></div>
  </body> </html>

To get it working properly with our data, I copied the code into TextMate and started tweaking. I swapped out the Fusion Table key for an old dataset we had lying around, and changed the query selection so the chart would pull data from the appropriate columns. Pulling data from a Fusion Table would certainly make collaboration easier, but pulling it from a spreadsheet was the real goal here, since our reporters and editors are already familiar with dumping data into Excel.

Thankfully, Google has documented how to query data from a Google spreadsheet. There are two ways to do this:

You can follow Google’s instructions here to query your data source and select the data all directly in the dataSourceUrl line.
Alternately, you can paste your spreadsheet URL into the dataSourceUrl and select your data in the query line, as you would when pulling in a Fusion Table. I find things easier to read when not all jumbled in a URL, so I went with the latter approach.

      function drawVisualization() {
        google.visualization.drawChart({
          containerId: 'visualization',
          dataSourceUrl: 'http://www.google.com/fusiontables/gvizdata?tq=',
          query: 'SELECT Year, Austria, Bulgaria, Denmark, Greece FROM 18k8XNgsc5ktLP2EHMCpoKIwymsVlzVV-xVuceA',

Note: If you’re querying your data source and data selection on separate lines, be sure to paste the full URL in the dataSourceUrl, including the spreadsheet key. (Unlike, say, with Fusion Tables, you would only include the URL up to the key, then paste the remaining key into the query on the next line).

When that’s all set, you’ll query which data points you want the chart to plot using Google’s query language clauses.

Next, determine your chart type. I focused on bar and column charts, but would love to hear from those who’ve tried out other types.

Last but not least, the formatting options. Here’s a handy reference guide to all of the API options on Google’s documentation site for developers. Given that this was my first time really digging into JavaScript—or learning how to read and apply instructions from user documentation—it took an extra day or two to get familiar with manipulating strings and boolean options to style the charts. The reference guide saved me a lot of time.

We decided to roll out Google charts on a not-so-time-sensitive piece—a roundup of fascinating insights into what the world’s garbage can tell us about the health of the global economy. MoJo editorial intern Sarah Zhang combed through the World Bank’s copious data on the subject, whittled them nicely into a Google spreadsheet, and then prototyped a few charts within the spreadsheet itself.

Once we started charting, we ran into a number of limitations. Chiefly, layout. We wanted to compare five countries by population, trash output, and GDP, creating something like this chart, which Sarah had prototyped directly in the Google spreadsheet:

This prototype didn’t really tell the story we were after—that the US generates a disproportionately large amount of the world’s trash relative to our share of world population and GDP. We needed to compare the countries across these three metrics, not compare these three metrics by each country. We ultimately decided to parse this data out into three separate charts:

In hindsight, a set of stacked column charts could have done the job just as well, if not better. I’d be curious to hear others’ take on the best way to visualize this data and tell the story we were after.

It also would have been nice if:

The axis labels wrapped, so that longer labels could appear in full without having to squeeze the actual chart area into a tighter space.
The visualization ignored irrelevant content in the spreadsheet. You can specify which columns to pull data from, but if there’s other content living in the spreadsheet, your chart will think there’s additional data to chart and squeeze itself into a smaller area.
The API included mapping. Publishing and embedding our maps of current and projected world trash generation straight out of the Google spreadsheet did not work to our liking. There was a ton of data that took too long to load, and the color scale was too small. We ultimately took screenshots of each map and uploaded them as static png files.

All that said, we finally had the charts where we wanted them. A bit of initial investment paid off nicely. The best part was that even as the piece went through multiple drafts and fact-check, changes we made to the data in the Google Spreadsheet automatically loaded on the chart embedded on our site.

Since our test run, we continued using Google charts in our: