MMMeeja Blog - Using Google Spreadsheets To Create A Simple Online Poll

Using Google Spreadsheets To Create A Simple Online Poll

Posted on 08 Feb 2008 by Andy -

I read this post on the Google Blog today and my first thought was “Wow, cool”, my second thought was “I wonder if you could create a poll with that?”. It turns out that there are a whole bunch of problems displaying the results. Here is a quick proof of concept...

Note: for any readers subscribed to the blog via RSS, you need to view originating page, due to embedded IFRAMEs.

Go ahead, try it. Choose an option, you know you want to!

And here are the results so far

Pretty cool eh?

Unfortunately, your results will not immediately update the chart because it’s just published as an image. However, there is a load of potential there.

So, How Is It Done?

Well, the first thing you need is a google account, then go to Google Spreadsheets and create a new document. Make the first few rows be the same as the number of options in your poll. A view of the spreadsheet showing the list of options

The next thing we need is to provide formulae for column B. We need to count the number of occurrences of each option text that occurs below our options. The formula for this is =COUNTIF(C2:C100000, “I like it a lot”) (the option text in this case is “I like it a lot”, change it to match your option).

Repeat this formula for each option and then create a graph of the results.

The next thing to do is to share the sheet as form. Click the share tab and choose the “Invite people to fill out a form” option. You’ll be asked to fill out a quite complex window, but fill in the question and choose either multiple choice or choose from a list. You’re smart, you’ll figure out the rest.

Finally, send the form to your own email address. It will let you test it out and add some data to your spreadsheet.

How Do I Embed It In My Blog?

There are two stages, first embed the poll, second embed the results.

Go back to your spreadsheet and click “Go to live form”. It will open a new window with the form, so you can copy the URL from your browser’s address bar.

Next add an IFRAME to the page that you want the poll to appear in. Try the following text:


<iframe src="YOUR LIVE URL">
<p>Please wait...<p>
<iframe>

Next, we want to embed the results. If you’re doing this as a chart, it’s just an image. To get the URL of the image, click on the chart in your spreadsheet and then click the menu at the top left of the chart. Choose the “Publish chart...” option. A window will pop up with the HTML you need. Put that in your page too.

Finally, we must ensure that the chart is updated regularly, for this you need to publish the entire spreadsheet, so click the “Publish” tab and choose the option to automatically republish after changes.

Unfortunately, the chart won’t be updated after each time you add a poll entry because the picture is stored in your browser’s cache, but there is not much we can do about that.

Improvements

It is early days for this new feature and it’s already quite exciting but I would like to see a few new things as a result of this experiment:

A better confirmation message - especially one that could show the chart
Improved chart updates - the spreadsheets themselves are really good for collaborative working and the charts could do with some of the same
More control over the chart rendering - you can see above that the legend in painfully small

Overall, it’s a very cool feature and I’m sure that forms providers like Wufoo are taking note and raising their game.

Possible chart update improvement

One way to prevent that chart image getting cached is to add a parameter to the img src URL so that it thinks you're loading a different image and doesn't ever cache it.

So if your image is at http://google.com/mychart01.png, you can put http://google.com/mychart01.png?rand=RANDOM_NUM where "RANDOM_NUM" is some random number generated via PHP, javascript, or whatever. Now the browser will get tricked into downloading the image everytime. (Adding a useless parameter won't affect anything except to stop the caching)

Re: Possible chart update improvement

Excellent point, Niraj. What I'd really want is the poll to be replaced by the results graph - or at least some HTML of my choosing, or a 302 redirect.

There are tons of possibilities but past experience shows that webmasters should really play by Google's rules, not try to second guess them. They can easily add a mod_rewrite rule to ignore any URL parameters.

Thanks for the comment.

Re: Possible chart update improvement

While they could ignore the extra parameters on their end, that still wouldn't stop your reader's browser from getting fooled into getting the image again since it thinks the image it has cached is not the one being requested.

That said, you make a valid point that it's kind of misusing Google's service since you'd be grabbing an image more often than you ought to be. While I don't think they'd have trouble sparing that much bandwidth, a more elegant solution would be to cache the image on your own site at some interval (i.e. every 15 minutes), and use the random parameter to force the reader's browser to load your cached image. Of course this is all a bunch of extra work, but I think the end result would be worth it. And since (presumably) some script from your site would grab the image from Google to create the local cache on your site, you'll get the most updated version from Google every 15 minutes.

Re: Possible chart update improvement

You are, of course, right that the parameters are only relevant to the browser. That'll teach me to comment late at night.

I think Google can afford the bandwidth of a few extra image downloads :-) Placing a server-side script on my site negates the simplicity of using Google forms for a poll, because I might as well just place a poll script on there and use <a rel="nofollow" href="http://code.google.com/apis/chart/">Google charts</a> for the visualisation.

I see this tool being used by bloggers that are not programmers and don't know how to set up third party scripts. Unfortunately, it's not ready for them - yet.

MMMeeja