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:

  1. 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:

  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 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".

  4. 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".

  5. 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).


Interkulturelles said...

Hi Pamela,

I like your FlashCards. But for the time being it ignores German special characters (i.e. äÄöÖüÜß). So I cannot use it. It would be nice, if you could fix this.


Pamela Fox said...

Could you explain what you mean by ignores? (Do you have a spreadsheet that shows the problem?)

J McCright said...

How can a teacher/parent present these gadgets to children? Do you drag the gadget over the spreadsheet to hide the correct answers?

I'd like to use these on a Promethean SmartBoard so that the entire class can watch and take turns spelling words.

Pamela Fox said...

Hi Joe-

I would make them ahead of time, and then only project the gadget (inside its own sheet, away from the answers). Would that work for you?

Interkulturelles said...

About the ÄÖÜ-problem:

For example the word in the database is "Bücher". This shows up in the gadget like "Bcher".
This is what I mean with ignoring the German char's.


Zora said...

I love your flashcards and am starting to develop them for the fall. My problem is subscripts and superscripts. As a chemistry teacher, I need them. Any thoughts?

Thank you for giving this tool to me.

ik ga said...

I would like to translate this gadget to Dutch.
Is the code open source and can I freely translate it or are rights reserved?
Of course I will mention you as the source!

ik ga said...
This comment has been removed by the author.
Pamela Fox said...

Hey Willem-

I've updated the bottom of the post with code info. It's all open, Apache licensed. Enjoy and let me know what you do with it.

ik ga said...

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.

alexandrojv said...

yeah, for some reason it ignores the spaces from the guess list and it combines the words if there are multiple, but it still works, thanks a lot, hope I pass my government test now :)

Sergey said...

It does not understand Korean. And lookes like Russian too. Alas...

Miss Brown said...

Ms. Fox,

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!

alexandrojv said...

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

1. Insert the Word Study Gadget
2. Make sure the setting are just like you want them
3. Click on down arrow on the top right corner of the gadget
4. 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" selected
8. But on the Get a link to the published data: Select Webpage for the first selection, and "Gadget1" for the second selection
9. Click on the "Republish now" button
10. Copy the link on the box at the bottom
11. 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):

(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.

Dennis said...


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

Dennis said...

I think that changing a line of code will improve the user experience. Try changing




Pamela Fox said...

Thanks for the code change suggestion, Dennis. I've pushed the fix live, along with a few other fixes. (

It should be live within a few hours.

Let me know if you notice any regressions.

OJ said...

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 a run for its money on desktops/laptops/iphones/etc.