If you need to gather data on the ground – or want to crowdsource data through an online form – this is how you can visualise the results as they come in using 3 Google Docs tools. They are:

• Google Forms
• Google Docs spreadsheet
• Google Gadgets

And here’s the process:

1. Set up your spreadsheet

1. Log on to Google Docs and click on Create > Spreadsheet. (Sometimes it’s easier to skip this stage and click on Create > Form to create the form, which will automatically generate a spreadsheet for it too, but I’m showing the simplest way first)
2. At the top of each column type the headers for the data you’re going to collect and visualise. The fewer columns, the more likely it is that people will fill it in (including yourself).
3. Go to Tools > Form > Create a form…

2. Setting up a Google Form

You’ll now be presented with a wizard for editing your form. The column headers will be used as question titles, and the spreadsheet title as the form title. Now you need to do some tweaking:

1. If this is intended to be seen by other people, edit the spreadsheet title (just click on it) and questions (hover over, and click the pencil icon on the right) so they give people a reason to respond. You should also expand on this in the form description (underneath the title)
2. You can change the order of questions by clicking and dragging them
3. Using questions where users can choose from a list has a number of advantages, including speed, consistency, and likelihood of response. To limit the possible responses to a question:
• Edit it by hovering over it and then clicking the pencil icon that appears to the right.
• Select the drop-down menu for ‘Question Type‘ and select ‘Choose from a list‘
• Then enter the values they can choose from, and click Done.
4. If your form is going to be public, you may want to hide particular questions. You can delete them from the form by hovering over the question and clicking on the bin icon. This will not affect your spreadsheet.
5. You should also edit the confirmation that users get when they submit the form. You can do this by selecting ‘More actions‘ along the top right area above the form, and ‘Edit confirmation‘. Change it to something that thanks the user and points towards a page where they can find out more about the data, for example, a related story or the resulting visualisation.
6. Click ‘Save‘.
7. Look for ‘You can view the published form here:‘ along the bottom of your form, with a link. Email this link to yourself so you can link to it or access it elsewhere. For example, if you bookmark this on a smartphone, you can use this phone to input data as you’re researching your patch (for example, interviewing people about unreported crime or counting the numbers of closed down shops on a street).

3. Visualising the results with Google Gadgets

Once your spreadsheet has some data on it (you or someone else, or both, have entered data using the form or the spreadsheet) you can start to publish a visualisation of the results.

You may need to add an extra column or two to calculate numbers if your data doesn’t have them – for example, to count how many entries of different types (see this on the =COUNTIF formula for how to do that) or to add up ratings or generate average scores. Make sure that your formula will still work when new rows are added – for example, refer to a whole column by using (C:C) rather than (C2:C200).

1. Now, select the range of cells containing the data you want to visualise, and click on Insert > Gadget…
2. Pick a suitable chart
3. Edit the settings so it has a title, axis labels, etc.
4. And click Apply & close

You should now have a chart on your spreadsheet.

4. Embedding the chart on another webpage

1. Select your gadget and click on ‘Gadget‘ in the upper left corner. A drop-down menu appears giving you a number options including editing the settings, should you need to change the title, legend, etc.
2. Select Publish gadget…
3. A window will appear containing script you can copy and paste into webpage code, embedding the chart.

Note: this won’t work on all webpages. For example, if you have a WordPress.com blog, any script that begins with <iframe> or <script> won’t work.

That’s the final part of the process. In short, we have a Google Form, which is feeding data to a Google spreadsheet, which is feeding data to a Google gadget on a webpage.

The data won’t update automatically, but automatic updating is one of various elements of customisation of Google Gadgets that you can do if you explore some of the documentation on Google Code.

If you’ve any tips on using Google gadgets please let me know.