Sunday, May 17, 2009

Google Spreadsheets + APIs = Interactive Flash Cards

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.

For about 6 months in the Mountain View Google office, I went to a once-weekly hour-long German language class. Since I didn't have much time to really do homework outside of class, I looked for excuses to use Google APIs to give me different ways of learning German. My first attempt was a gadget that combined image search with a spreadsheet-stored German vocabulary wordlist. I liked the idea of using computer-generated hints for the words and wanted to make a more general solution for any topic/language.

So, I made the interactive flash cards gadget. Each flash card has a hint and a form of guessing, with different options for hint generation and guessing strategy. The hints can come from the spreadsheet (e.g. user-entered definitions), Google translate (any supported language pair), Google image search, or Wikipedia (*currently down). The guesses can be entered in a simple text input, selected from multiple choices, or solved in a word jumble. Hopefully, these options are diverse enough for all types of learners.

Here are steps for using the gadget:

Create a new spreadsheet, put a list of words in the first column, and put hints in the second column if you'd like to use that option. My sample spreadsheet has an animals wordlist:

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:

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 whatever columns you've created (either just words, or words and hints), and you should see the Range text field update with the range. If it doesn't work, you can manually type it in. Click "Apply".

The flash cards gadget is designed to take up a bit more space, so it's best to move it into its own sheet. Click the menu in the upper left of the gadget and select "Move to own sheet".

You can now play with the different hint/guess options to find your favorite. Several combinations are shown below.

Image Search + Word Jumble

Second Column + Multiple Choice

Translation + Type-in

You can play with my sample flash cards here. One day, I hope to add the ability to track your progress in learning the flash cards content... but if you're a developer, feel free to beat me to it!

If you're a developer and want to tweak this gadget further, you can grab the code from here. It's licensed under Apache 2.0, so feel free to tweak it and use it however you'd like. (And if you've got fixes, I'm happy to patch them back in).

I hardly dare to ask, but I have a feature request:I would like to have added an extra colomn because I want to show only first names, but let Image search look for full names.For example when guessing persons I want the visitor to fill in the first name, like Steve.Image search should be able not to search for Steve (too many) but for Steve Jobs or Steve Rubel.Now when I use full names the spaces are neglected and some names are very long.I hope you understand what I mean. I'v taken a look at the code but this goes far beyond my knowledge!But I've translated the instructions and now offer your beautiful gadget to be used in the Netherlands.

Thank you for creating this! I am trying to use this to create links on my school website for my students to use to study SAT vocabulary words. I have managed to successfully publish a link to one list, but for the life of me cannot figure out where i got the information to use to make a successful link.

Would please post instructions on what I need to do so they can only see the cards and not the whole spreadsheet? Thanks!

@Miss Brown: What you need to do is the following (Sounds like you can skip to step 3):

1. Insert the Word Study Gadget2. Make sure the setting are just like you want them3. Click on down arrow on the top right corner of the gadget4. Choose "Move to own sheet..."5. Click on the Share button on the top right of the entire spreadsheet (next to the Autosaved box)6. Select "Publish as a web page"7. On the Sheets to publish: it must have "All Sheets" selected8. But on the Get a link to the published data: Select Webpage for the first selection, and "Gadget1" for the second selection9. Click on the "Republish now" button10. Copy the link on the box at the bottom11. Share that link to others :)

Here is an example of one of my Government study card that I did, and yours should look familiar (notice that it only shows the gadget, and nothing else):http://spreadsheets.google.com/pub?key=tai4MUrsRP1xZ6mDcir7laA&single=true&gid=1&output=html

(Please let me know once you look at that link, in order to make it inactive which I will after a month or so. Also anyone else let me know if you need to see an example once this link is inactive)

P.S. last time I asked Pamela Fox about this gadget she said she wasn't following this blog so much, and wasn't developing on this gadget so much. But I now how to get in touch with her if you need a response from her specifically, so let me know if anything.

Thanks for the great gadget. The code appears to strip out any characters that are not in the english alphabet. See: formattedValue.replace(/[^a-z,]/gi,""). This will cause letters in foreign languages and common punctuation to be stripped out. Can you alter the gadget to allow foreign alphabets and some punctuation? Thanks - Dennis

Thanks so much for this gadget! I've been waiting for it to be updated, and an extremely happy that it works again! Would it be possible to implement a correct/incorrect flag and the Leitner system into card repetition? If so, this gadget would give flashcardexchange.com a run for its money on desktops/laptops/iphones/etc.