Have you ever thought about how you could combine a language lesson with mathematics and spreadsheets? The magic of Google Sheets allows us to be able to use certain functions to create our own language translating spreadsheet using Google translate. Follow these three steps to create your own personalised translator for you and your students.
[bctt tweet=”Create your own personalised translator in #GoogleSheets, by following these three steps #usetechbetter” username=”adifrancis”]
The other week I was running a workshop and demonstrating how to use google sheets and was demonstrating how to see what functions are available inside sheets. It was then that I saw there was a function called Google. There are a number of commands available within a spreadsheet that utilise some of the cool features of Google and one of them is Google Translate.
By combining the detectlanguage and googletranslate function you can build a spreadsheet that automatically identifies the language that you are writing in and converts it to another language. The detectlanguage function looks at what has been entered into a cell and identifies the language used. The googletranslate function will translate the text in a cell to a language you specify. This function works when you tell it what language the original text was written in. By combining the two functions, you can detect the original language and convert it to your desired language all in one step.
Whenever I combine two or more functions I tend to make sure they both live independently and then combine them. By doing this I can build up quite complex combined formulas ensuring that every single step works before writing one big long formula and having to find syntax errors. The next steps take you through how each function works before we combine them.
Step 1
In your spreadsheet enter a phrase from another language (in this example I am using French in row 2). Then in the next column use the detectlanguage function to find out what language it is written in. The syntax is =detectlanguage(A2). The result will be a two character code that Google uses to identify the language.
Step 2
Use this code and the googletranslate function to convert your phrase into another language, in this example, I am converting it to English. The syntax is =GOOGLETRANSLATE(A2, “fr”,”en”). Enter the cell that has the text in it, the language you have just detected and the language you want it converted to.
Step 3
Now that we know the formulas work, we can delete the working columns’, combine the functions by removing the “fr” in the googletranslate function and replace it with the detectlanguage function. It should look like this =googletranslate(A2, DETECTLANGUAGE(A2), “en”). This will allow you to enter text in any language in the first column and have it converted to English.
If you want to add another layer of coolness to the sheet add a vlookup function to pick what language you want the phrase converted to. This combines a vlookup table, a named range and data validation. Have a look and give it a crack.
Grab your copy of the sheet here to see how all the magic works.
[bctt tweet=”Check this blog to see how to combine several functions in #googlesheets to translate phrases from any language into the language of your choice.” username=”adifrancis”]
This is a great way of combining a couple of subjects that we don’t think go together by using the Google Translate function within Google Sheets. Check out this blog post on data validation and conditional formatting to help you master sheets and to learn more tips and tricks join our online professional learning community.
