Saturday, February 5, 2022

Self-Checking Translation Quiz in Google Sheets

I have always wanted to improve my proficiency in other languages. Back in high school (many, many years ago) I took four years of French. I can still say "I have a red pencil" … "J'ai un crayon rouge" … but not a whole lot more.

Thankfully Google has a lot of great translation tools that can help you when learning a new language, from the Google Translate website, to the Google Translate extension, to the Google Translate mobile app (Android or iOS), and on and on.

However, I was wanting to find a tool that could be used as a self-checking translation quiz when practicing a different language. I ended up creating this tool with Google Sheets. This sheet allows you to test yourself on translating words or sentences into a different language, and gives you hints as needed if you get stuck.

This could be helpful for an English Language Learner to enter words in their language and then practice translating into English, or for an English-speaking student to test themselves on a language they are learning in school, or for anyone to practice any other language.

See below to get your own free copy of this self-checking translation quiz template, as well as a short 9-minute video tutorial on how to use it, and some written directions.


Video Tutorial (9 minutes)




Get Your Own Copy of the Template

The "Self-Checking Translation Quiz" is created with Google Sheets. To get your own copy of the sheet, simply click the link below.
Once you have your copy of the Google Sheet, you can use it yourself, or make it available to your students. For example, if you use Google Classroom you could push a copy out to all of your students.


Using the Template

Now that you have a copy of the template, let's take a look at how to use it to practice a different language.
  • The first tab is the "Directions" tab with a short list of instructions.
  • The second tab is the "Quiz" tab where you will actually practice your translations.


On the "Quiz" tab, the first thing you will do is choose your source language and the language you are translating into.
  • From the drop-down menu in the top left corner, choose the language for your source text.
  • Then from the next drop-down menu, choose the language you want to translate the text into.
  • For example, if English was your primary language and you were trying to learn French, you could choose English for the "Source Language" and then choose French for the "Translated Language", or the other way around if you want.


Next you will want to enter the original text that you want to practice translating.
  • In the column titled "Original" type in the words or sentences that you want to start with.
  • These could be words from your own language that you want to practice translating into a language you are learning, or it could be words from the language you are learning that you want to test yourself on translating back to your own language.
  • There is room in the template for 20 lines of text at a time.


Now that the original words are entered, you will now want to try to quiz yourself on translating them into the other language you chose.
  • To do this, simply type in what you think the translations should be in the "Translation" column.
  • If you type the correct translation, the box will turn green.
  • If you type the incorrect translation, the box will turn red.
  • You can retype the translation until you get it correct and the box turns green.


The translation needs to match perfectly, including capitalization
  • For example, in German all nouns are capitalized. 
  • So to translate "I am a man." into German, I would not be able to write "Ich bin ein mann." with a lowercase "m", but instead would need to use a capital "M" for it to be marked correct.


If you need to enter accented characters, unfortunately Google Sheets does not have the "Special Characters" menu option like Google Docs does.
  • Instead you will need to use another option to copy and paste in accented characters.
  • For example you may want to use the Chrome Web Extension called "Special Characters - Click and Paste".
  • This extension makes it easy to simply click on the needed accented character, and then paste it into the sheet.


In addition to telling you if you got the translation right or wrong, this template can also give you hints if you are stuck.
  • The columns on the right side of the sheet have checkboxes that you can select to reveal a series of hints to help you if you get stuck.
  • You can check the box for "# of Words" to see how many words there are in the translation. 
  • For example, in English "That's life!" is just two words, but in French it is "C'est la vie!" which is three words. That hint may be what you need.


  • In the same way you can select the box for "# of Letters" to see how many characters are in the translation. 
  • This does not include spaces, but does include punctuation. 
  • For example "I love you." in English becomes "Je t'aime." in French which is 9 characters long including the apostrophe and period.


  • For more hints you can also check the boxes for "1st Letter", "2nd Letter", "3rd Letter", and/or "Last Letter" as needed to get a little more help. 
  • Sometimes just reminding yourself how the translation starts or ends may be enough to get you going.


  • Even after all the hints, if you are still stuck, you can click the box in the "Answer" column to see what the translation should be.


When you are all done, you can simply uncheck any boxes, and delete the text from the "Original" and "Translation" columns and begin again with new words to quiz yourself on.


Nerdy Stuff - How it Works

For those interested in a little behind the scenes details on how the spreadsheet actually works, let's go through a couple quick points. This will not be a thorough explanation of how to build this sheet from scratch, but rather a few tips and tricks I used to set this up.
  • First, there are two hidden tabs in the sheet.
  • You can see these by clicking "View" then "Hidden Sheets" then "Show Key" and "Show Languages".


  • The "Key" tab is where the translations are actually being done in the background, so the sheet can tell if you typed in the correct text.
  • This is done using the "GOOGLETRANSLATE" function to do the translating, and the "VLOOKUP" function to grab the correct language codes from what you have chosen.
=IF(A5="","",GOOGLETRANSLATE(A5,VLOOKUP(A$2,Languages!A:B,2,FALSE),VLOOKUP(B$2,Languages!A:B,2,FALSE)))


  • The "Languages" tab simply has a list of the available languages and their corresponding language codes.


To reveal the hints, I used an "IF" formula to see if the checkbox is selected, and then combined that with formulas to determine the hint to show you including:
  • "COUNTA" =IF(C5=TRUE,COUNTA(SPLIT(Key!B5," ")),"")
  • "LEN" =IF(E5=TRUE,LEN(SUBSTITUTE(Key!B5," ","")),"")
  • "LEFT" =IF(G5=TRUE,LEFT(Key!B5,1),"")
  • "MID" =IF(I5=TRUE,MID(Key!B5,2,1),"")
  • "RIGHT" =IF(M5=TRUE,RIGHT(Key!B5,1),"")

And to color in the translation green for correct and red for incorrect, I used conditional formatting.
  • You can see the setup by selecting one of the translation cells, then clicking "Format" and "Conditional formatting".


  • To see if the word you typed in is correct or not, I used the "EXACT" function, which is necessary to make it match uppercase and lowercase letters, and not just the content of the text
=EXACT(B5:B24,INDIRECT("Key!B5:B24"))
=NOT(EXACT(B5:B24,INDIRECT("Key!B5:B24")))
And that's the gist of it. If you have specific questions about how the sheet was made beyond that, feel free to reach out to me.


Conclusion

And that's it! Hopefully this self-checking quiz template will be a helpful way for you and your students to practice translating words and sentences from one language to another. Although having a red pencil is nice, having a digital tool like this can provide feedback and hints to help you say that, and many more things, in another language.

For more resources related to world languages and English Language Learners, check out my resource document for "Google Tools for ELL and Languages".


Post by Eric Curts. Bring me to your school, organization, or conference with over 50 PD sessions to choose fromConnect with me on Twitter at twitter.com/ericcurts

No comments:

Post a Comment