Sunday, May 17, 2009

Creating a Wordsearch using Google Spreadsheets

Update: Google is deprecating Google Spreadsheets gadgets, as they announced in this post, so I am no longer updating or supporting them. If you're a developer, you can try Apps Script or the Spreadsheets Data API to see if you can accomplish the same thing. If you're a user, sorry, sometimes Google shuts down little used features.

I'm a fan of alternative learning and testing techniques. Back when I was the teaching assistant for the "History of Video Games" class (yes, that's a real class), I gave the final exam as an illustrated crossword puzzle. It was surprisingly hard to find software for creating that crossword, so I hoped to make a Spreadsheets gadget to make it easier. Unfortunately, crossword-solving algorithms that run entirely in JavaScript are hard to find, and I gave up and went for second best: a wordsearch gadget. (A big thanks to Robert Klein for the wordsearch JavaScript library.)

Here are steps for using the gadget:

  1. Create a new spreadsheet, and put a list of words in the first column. (Or, alternatively, use an existing spreadsheet that has a column of words you're interested in). My sample spreadsheet has a simple animals wordlist:

  2. Click on the "Insert" menu and then select "Gadget..." This presents you with various categories of gadgets to choose from (similar to the iGoogle directory). My gadget isn't yet in the gallery, so you'll need to select "Custom" and then type in the URL to the gadget:

  3. The gadget will appear embedded in the current worksheet, and it will prompt you to select a range of data to send to the gadget. Select all the cells that contain the desired words, and you should see the Range text field update with the range. If it doesn't work, you can always manually type it in.
  4. You can now customize the number of rows and columns. The default is 10 by 10, but if you have more words, you likely want a larger wordsearch. Click "Apply", and see the generated output.
  5. You have a few options for how you use the wordsearch. You can play with it immediately, inside that gadget, or you can use the option on the gadget menu to move the gadget to its own sheet and use it there. Note that each time you reload the spreadsheet, the wordsearch will be randomly generated with a new layout - so don't rely on it staying the same.

  6. You can also publish your spreadsheet so that other people can play the wordsearch. Click "Share > Publish" and you'll see the published URL, which you can share with friends. You can also use options on the gadget menu to embed the gadget in your iGoogle page or inside a normal webpage.

You can play with my sample wordsearch here. Enjoy, and hopefully one day, I'll have a gadget that makes crosswords too.


ik ga said...

Great gadget! Thanks for sharing. I blogged about it on my edublog.
Do you have any idea how I could embed this gadget on a Google Site?
I tried but with no success...

Pamela Fox said...

It's a little tricky to add to sites.

In Sites, do Insert Gadget, by URL.

To get the URL, go to your gadget, click More -> Get Gadget XML.

Paste that as the URL.

Then a preferences dialog will pop up in Sites.

To get the data source query URL, go to your gadget and click More -> Get query data source URL.

Paste that in the Sites preference.

Tweak the size and number of rows/cols as desired - you'll likely need a bigger width to accomodate the sidebar of words.

ik ga said...

Thanks again.
This works fine in Google Sites now!

ik ga said...

The gadget has been adepted by many more through my weblog but now people ask me how they can make it public when used in Google Sites.
It works for me but later on I noticed that you have to be logged in.
Is there a way to make it public on a Google Site?

Pamela Fox said...

Hmm. Is the actual spreadsheet published?