Creating graphics with Google Docs

Last week I taught a session on visualizing data at the Knight Center, highlighting free and easy-to-use multimedia tools. Over the next couple of days, I’ll be posting tutorials on everything we discussed. Last week I posted tutorials on basics of HTML and Tableizer, which converts a spreadsheet into HTML.

Today, we’re going to create interactive graphics using the same unemployment rate information we used with Tableizer.

If you haven’t already done so, create a Google Docs account. You do not have to have a GMail account to use Google Docs — you can use your work e-mail address or a Hotmail account or a Yahoo account or any other.

With Google Docs, you can create documents, spreadsheets and presentations, and upload files that have been saved in Microsoft Word or Excel, or Open Office. Google Docs also offers a few functions that those other programs do not, and lets you share with others so more than one person can add to or edit a file.

After logging in, create a new spreadsheet. Copy this table (start with “year”, highlight the rest of the table and copy the data — ctrl-C on a PC or command-C on Apple) and paste it into your new spreadsheet.

Save and name the spreadsheet: Click the “save” button above the blue toolbar on the right.

First, let’s create a chart. Fair warning: These are not the most beautiful charts you’ve ever seen.

(See what I mean?)

Here’s how to create it:
• In the toolbar, click on Insert > Chart. A new window will open with several different types of charts.
• With this information, a line chart works best. Click on “lines” and select the subtype you like best.
• To include all of the data, change “What data?” to A1:M12. A1 represents the first cell (column A, row 1) and M12 represents the last cell (column M, row 12). The colon establishes that you want the range from A1 to M12.
• In this spreadsheet, row 1 describes the information in each column; mark the “Use row 1 as labels” box. Column A describes the information in each row; mark the “Use column A as labels” box.
• You can add a chart title and description for the axes: The horizontal axis would be the months, and the vertical axis would be the rate.
• Save the chart — there’s a button under the preview.
• The chart will be saved on top of your spreadsheet. I like to move them to a new page so I can continue to edit the spreadsheet if needed. Click on the chart, and it will be outlined by a gray box with the word “Chart” and an arrow in the top left corner. Click on that arrow; the last option is “Move to own sheet.” After it is moved, there will be a series of gray buttons above the chart. Use those buttons to edit the chart, publish it or to save the image.
• To publish the chart in a blog or story, click on the “publish chart” button. A new window will open with an embed code — in this case, it is nothing more than an HTML image tag. The charts are rather large and bulky — if you would rather use a smaller version, save the image (the button next to the “publish chart” button) and resize it. You can then upload the image to your site and write the HTML to add it to your blog or story.

Gadgets

That works for a flat, non-interactive chart. But Google Spreadsheets can do better with gadgets. The great thing about gadgets: They’re interactive. The not-so-great thing: They’re picky, and usually require spreadsheets to be formatted in a specific way. We’re going to create two gadgets: The first will be an interactive version of the chart above; the second will be much more fun and will show how that unemployment rate has changed over time.

To make the first gadget, the table needs to be rearranged a bit — basically, each row needs to be a column. Lucky for you, I was bored on a Sunday night: Copy this table and paste it into a new sheet in your spreadsheet. (To add a sheet, click on the “add sheet” button under the spreadsheet.)

Let’s build the first gadget:

• In the toolbar, click on Insert > Gadget. A new window will open, allowing you to pick the kind of gadget you want to create. For this one, click on the “charts” link on the left; on the left, under the line chart, click the “Add to spreadsheet” button.
• In the new window, change the range to A1:K13. Change the number of header rows to 1. Click “apply.” The gadget should look very similar to the chart, but when you click on the points, the year and unemployment rate will show up in an information bubble. Add a title and axes labels as you want, then click “apply and close.”
• To add the gadget to your blog or website, click on the arrow on the right side of the gray bar above the gadget. Click on the publish link. Copy the embed code and paste it in your site. If the gadget is too big, you can change it: The last numbers of the embed code are the height and width. It just takes a little bit of math to keep the gadget proportional.

That’s a nice graphic, but not as useful as it could be. So let’s rearrange the information again, and create a new gadget. This time, I assigned a date to go with each number. The gadget requires a specific date, so instead of “January 2005,” I used “Jan. 1, 2005.” (Google changes the format to 1/1/05.) It’s a funny-looking table, I know.

Creating this gadget is very similar to the previous gadget. In the toolbar, click on Insert > Gadget. Click on the “charts” link on the left; on the left, add the “Interactive Time Series Chart.” Change the range to A1:B124. You should get an interactive gadget like this:

Tomorrow: Building interactive graphics with Many Eyes.