Data laser

Google Sheets magic tricks for language learners

The best language partners not only open your eyes to new words, but to new techniques. It is always the case with excellent iTalki teacher and polyglot friend Marcel Balzer, for example. Never short of fantastic tips, he recently shared a gem of a trick for language learning through the free, online spreadsheet software Google Sheets.

The magic happens thanks to the cross-pollination between Google Products, namely Sheets, and Google Translate. Using a simple formula, you can translate the text contents of one cell into another.

It is very easy to set up. Say you create two columns, A and B, headed German and English. In cell A2, you add a new German word you come across. In cell B2, you have the following formula:

=googletranslate(A2,"de","en")

As soon as the word is entered into the first cell, a handy quick translation will pop up in the second. You may recognise the short codes de and en as international abbreviations, which you can substitute for the languages you are learning. See this link for a full list of them.

You can be as creative with your pairings as you like; I’m currently experimenting with cross-translating vocabulary lists in Norwegian and Icelandic, for example. Great for filling gaps in a weaker language by referencing a stronger one!

Using automated Google Translate in a Google Sheets spreadsheet

Using automated Google Translate in a Google Sheets spreadsheet

Google Sheets Combo power

Google Sheets has many more tricks up its sleeve for the linguist open to a bit of tech exploration, though. With some imagination, you can create some quite powerful learning applications by combining them.

You can, for example, join together the text in several cells to create a single line of text. For example, if you have “j’ai” in cell A2, and “mangé” in cell B2, in cell C2 you could add:

TEXTJOIN(" ",TRUE,A2,B2)

The TEXTJOIN method pulls together the text contents of cells, and requires a couple of arguments, or pieces of information. The first ” ” is a space in quotes, and tells TEXTJOIN what to place between the words it joins together. Here, I use spaces, but you could use hyphens, commas, or whatever else is appropriate. The TRUE simply tells TEXTJOIN to ignore any blank cells that contain no text – if you want them included, change this to false. Finally, there is a list of all the cells containing the content you want to join (A2, B2). This can be as long as you need.

This is useful for words and phrases on their own. But more usefully, I found, was to use this along with target language words to build URLs. To explain why, it might be useful to outline one of the main methods I use to mine for new vocabulary.

The vocab mining process

When I actively seek out and check new vocabulary, I have a step-by-step routine. This will start in one of two ways, depending on which direction I’m learning it in. Sometimes, for example, I will realise that there is a gap in my target language vocabulary by comparing it with my native and other languages. It’s important to actively interrogate your languages like this, always looking out for gaps. Alternatively, I will just come across new vocab already in the target language when I read or listen to podcasts.

Google Translate

Once I have a word to look up, I use dictionary resources (online and offline), as well as Google Translate, to find a translation. Of course, Google Translate comes with many caveats, being a very blunt instrument for linguists. As a former teacher, I feel the pain of anyone marking a piece of homework that has so obviously gone through the Google mangle. However, as a quick vocabulary look-up tool, it is hard to beat.

Wiktionary

Of course, you have to keep your wits about you when using it. And so comes the final step for me: Wiktionary. Wiktionary is a crowdsourced multilingual dictionary, full of detailed entries for countless words in a whole raft of languages. This includes multiple meanings, contextual examples and even detailed etymologies for many entries – all things that provide real hooks for the learner to understand and assimilate new lexical items.

By now, I should have a good overview of how the word fits into the target language. At this point, I will add it to Anki for learning and testing. The Anki entry may include brief usage notes from Wiktionary and other sources.

That’s a fairly simple procedure, but it does involve a bit of jumping around from site to site. However, if you look at the URLs of Wiktionary pages, they have a very regular form. For example:

https://en.wiktionary.org/wiki/suigh#Irish

You can leverage this kind of regularity when automating tasks. But how?

Chain of command

Enter TEXTJOIN, combined with the power of Google Translate! The chain goes like this: with an English word in cell A2, an automatic translation (say, into Icelandic) pops up in cell B2. Cell C2 then takes the output in cell B2 and builds a link to the relevant Wiktionary page, which I can click to check the entry:

=TEXTJOIN("",TRUE,"https://en.wiktionary.org/wiki/",B2,"#Icelandic")

This builds up a full link to a Wiktionary page referencing the word in B2, and the position on the page where the Icelandic entry appears (if it exists). Suddenly, it is a lot quicker and easier to perform my three-step vocab lookup.

Tip of the iceberg

There is a vast array of other methods available in Google Sheets. The above example is a fairly simple chain, but much more complex processes are certainly possible with a bit of creative play. They can be used in myriad ways, too. Google Sheets can be viewed by multiple users at the same time when shared, for example, and Marcel explains that he uses his along with his teacher during online lessons. New words are added to the sheet as they come up, and can be instantly cross-referenced.

Modest Marcel insists that the trick was not his invention, and merely came to him via another helpful polyglot colleague. Nonetheless, I am extremely grateful for the inspiration, which has triggered hours of geekish exploration! I pass it on in the hope of helping more fellow linguaphiles in the same way. Harness the power of Google, and happy learning!

3 thoughts on “Google Sheets magic tricks for language learners

  1. Jen says:

    Hey thank you so much for sharing this! I’m creating my own spreadsheet of vocabulary words for Chinese. Do you know of a way to use the googletranslate function to include pinyin for Chinese? I’ve been looking at the ISO-639-1 Code but couldn’t find any reliable leads.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.