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
- 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)
- 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).
- 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:
- 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)
- You can change the order of questions by clicking and dragging them
- 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.
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).
- Now, select the range of cells containing the data you want to visualise, and click on Insert > Gadget…
- Pick a suitable chart
- Edit the settings so it has a title, axis labels, etc.
- And click Apply & close
You should now have a chart on your spreadsheet.
4. Embedding the chart on another webpage
- 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.
- Select Publish gadget…
- 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.