Creating dynamic charts using Google docs

I recently posted about how to add a quick form using Google docs. Kasper Sorenson left a comment asking if it was possible to update an image dynamically

Sometime ago, I wanted to create a dynamic chart based on values that were being entered into a database (in this case we used Google Spreadsheets). Basically if I was to export the chart as a static image, it would be outdated after an hour.

The chart created in the spreadsheet can be embedded instead of exported as an image so in principle you could update it. But I thought I would give it a try.

So, here is a form made using Google docs forms.

That drives a spreadsheet that drives an image:

Update: Well, I hope there is an image there but there seems to be this weird thing were you can’t see the image if you aren’t logged in to Google docs. Odd!. It seems you need to be quite specific about how you share the sheet before you then share the image. But you can also add a chart as a gadget and this seems less temperamental. Down side is that this is also a little unpredictable for updating. Not ideal but still, it’s free.

Add your data to the form and refresh the page and you should see the graph change. Okay the page refresh is not ideal but hey, it’s free.

How does it work

Selecting the whole column means you pick up new entries
Selecting the whole column means you pick up new entries

It works by picking the whole of the results columns the graph rather than specific data cells. With very little info in it it looks pretty sparse but as the results come in it will change. So not a perfect result but not bad.

When to use it
It might be useful if you were tracking fuel prices over time. Add the data and map the timestamp against the price. Add a postcode field the form and you could also output it as a map.

A quick look at charts

Most of my teaching today has been about the basics of online presentation – online writing etc. As an exercise I pulled some council news from the web to give us something to work with.  It was a simple story, a local council by-election. But it had some nice angles to explore – turnout etc – and it was on a patch I was interested in as part of the Bespoke project running out of the University.

I was really pleased to see some of the third year print students immediately looking at tables and graphs as a way of displaying the result. They were using Word to hack out the copy and used the Chart generator to make pie charts. A few things tripped them up.

The first was the generic colour scheme for the charts. The election results included Labour, Conservative and Liberal Democrat candidates. But you can see that the biggest result was formatted as blue – a bit of a problem when it was a Labour win.

A win for Blue?
A win for Blue?

Changing the colour of the data is pretty easy in Word 2007. Just right-click the segment and pick Format Data point. Pick Fill from the list then check Solid fill. Pick and colour and click close it’s done.

That's better. A win for Labour.
That's better. A win for Labour.

But then comes the next problem. You have to get the image out of Word.

In principle this is not too hard. Simply highlight the image, copy it and then paste it in to an image editing package to save it for the web. Of course it’s that last bit that is the pain – especially if you don’t have a decent editor to hand. And often the image is squashed or distorted.

Using online services

As an alternative solution to using Word or needing an image editor, I suggested using Google docs. The spreadsheet option allows you to create a chart which you can then share or export as an image. Easy to do

  • Enter the data
  • Select the data you want to work with
  • Use the Wizard to make the chart
Easy to use but limited
Easy to use but limited

When the image is saved there is a neat option to export the image

Getting an image out of Google docs
Getting an image out of Google docs Google spreadsheet chart

The image it chucks out looks pretty good but you can see that we have the same problem with colour, that we had with Word. Unfortunately there isn’t much we can do about that.

The Chart API

One option is to delve a little deeper in to the thing that helps make the google charts – the Google Charts Api. Unfortunately the code is a little arcane and often needs a little more effort to understand the construction of the chart than a visual hack around.  Luckily there are plenty of third party sites that offer wizards for google charts.

Jon Winstanley has a great little app for quick charts and it also allows you to pick colours for your data.  It still requires a little concentration around the data structure but it works well.

Another is James B. Allen’s chart generator which has a more of a WYSIWYG feel to it.

Google Chart

Appropriate use.

There are other chart apis and chart builders but the open and free tools from Google are really tempting and, with the help of people like James and Jon, some easy to use third-party apps.

Graphs and charts are an obvious and easy way to show people data, especially as they scan for detail. Of course in all of this chart magic, one thing is worth noting. Perhaps the best way to present the information is the simplest:

  1. Labour: 656 Votes
  2. Conservatives: 283 Votes
  3. Lib Dems: 239 Votes