Vocabulary Trainer

My oldest daughter Matilda have started to get English vocabulary homework this semester. What could be a better opportunity to build an app to help her!?

I decided to make a Pen for it. My idea was: Display an English word and she picks the Swedish translation from a list, increase score counter if correct. Simple enough. But how can she enter her words of the week? In a JavaScript array? Naaaa... In a Google Spread Sheet of course! Because it turns out there is a comprehensive web API you can use to get data out of spread sheets.

I began reading the JavaScript Quick Start Guide but that involved loading a Google JavaScript library, gapi, and was a lot of fuzz for not much benefit so I turned to the plain WEB API instead. Google claims it's REST, I could agree that some parts are at least.

There is a reference manual. And there are good examples. But what I found the most useful was the Google API Explorer, it helps you construct a correct URL for your HTTP request and then run it and display the response. Great! Note that you can use it for many (all?) other APIs as well.


Here is a step by step guide for how you could build something similar.

Create the Spread Sheet

  1. Create a Google Sheet document
  2. Set the Sharing option to "Anyone with the link can view". (Upper right.)
  3. Enter words in English in column A.
  4. Enter corresponding Swedish translation of the words in column B.
  5. Give the sheet a name (tabs at the bottom). There can be several sheets in one Google Sheet document.
  6. Next week you can go to the next sheet (tab) at the bottom and enter new words.

Set Up Authorization

  • Create an API key here. From Google's documentation:

After you have an API key, your application can append the query parameter key=yourAPIKey to all request URLs.

The API key is safe for embedding in URLs; it doesn't need any encoding.

  • Configure the HTTP referrer, the web site where the JavaScript is going to reside. http://s.codepen.io/* in my case. If a request is made from another referrer it will be denied. Say you try and use the URL directly in your browser, this is what you will get back: Requests from referer 'xyz.com' are blocked.

Create the Application

  • Create an application that first queries for all the sheets in the document. https://sheets.googleapis.com/v4/spreadsheets/1P8Os3dG_FTaypq7Aksf3NBTs13BQ-knVZXFHqDa3CtA?includeGridData=false&fields=sheets(properties(sheetId%2Ctitle))&key=123... Don't include any cells (includeGridData=false) and return sheetId and name (title).
  • Put the sheet names in a drop down menu.
  • When Matilda selects a sheet name in the drop down: make a new query for the words with translations in that sheet and store them in a list in the application. https://sheets.googleapis.com/v4/spreadsheets/1P8Os3dG_FTaypq7Aksf3NBTs13BQ-knVZXFHqDa3CtA/values/${$scope.selectedWeek}!A1%3AB100?majorDimension=COLUMNS&key=123... Where ${$scope.selectedWeek} is the sheet name selected in the drop down. Cells to fetch are: A1:B100.
  • Let the application pick a random English word and ask Matilda for the Swedish translation.
  • Increase the score counter if correct.
  • Make it possible to flip between the two languages (display a random Swedish word and ask Matilda for the English translation).


I choose to use Angular for the app. It has a nice $http service that you can use to make the HTTP calls. The app has zero styling so far. Maybe my kind brother will help me out...

The app is on CodePen: Vocabulary Trainer

Final notes

You can set up all kinds of fun integrations with the many services that exists on the web today. Just look at the amazing things Zapier and IFTTT (If This Then Than) can do! I just noticed that Zapier has documented how to integrate their service with Google Sheets.

How did Matilda do on the test? you ask. Well, she did excellent of course! =)

In 1996 I did the same application for my self as a school project using Turbo Pascal. It was a lot of fun but it took weeks! I remember I drew the UI on the screen with ASCII characters. This time around it took two evenings.

Thanks to Jonas Elfström who proof read this post.

5,387 6 40