Create your own personalised translator in Google sheets

28 March 2019
Adrian Francis

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.

Create your own personalised translator in #GoogleSheets, by following these three steps #usetechbetter Click To Tweet

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.

Check this blog to see how to combine several functions in #googlesheets to translate phrases from any language into the language of your choice. Click To Tweet

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.

Recent Posts

Here’s What
Our Clients Say

PR Industrya


In 2018, Using Technology Better designed and delivered a two phase post-migration training program for this New Zealand based graphic design firm. The initial goal of the training program was to reduce frustration with G Suite, with the long term aim of facilitating a change in culture and collaboration that can lead to transformative practices

Download your skills checklist to see how you score...


Choose Industry:

Which Skills Checklist?


Nice Move Allstar!
You're about to get
awesome value in your
inbox that's going to make
life that little bit easier & sweeter!


Pop in your details below and we'll send you the PLD Checklist 📧

First time applying for MoE Funded PLD?

Get the Latest Blog Posts, New Tips And Tricks


NSW DoE Leadership Webinars


NSW DoE Leadership Webinars


Holiday Rescue Maker Camp

Student to agree to and tick:
Guardian to agree to and tick: