Google Sheets Functions – GOOGLETRANSLATE, DETECTLANGUAGE

Lots of people know about and have used Google Translate either on their phones or on the Google website but what they often don’t know is that there is a built-in function in Google Sheets, which will allow you to translate from one language to another, and even automatically recognise the language and translate it. So, in this post we’re going to look at the functions GOOGLETRANSLATE and DETECTLANGUAGE and you’ll see how easy these are to use.


Example 1 – Translating from one language to another

I created a system where I work, where teachers can report problems in their classrooms via Google Forms on their mobiles. We’re in Seville, Spain, and some teachers can’t speak Spanish very well, so I needed a system that would allow them to report the problems in English and then it would translate it into Spanish, so that the maintenance and IT guys, who don’t speak English, can understand the problems and act on them.

To do this, I used the GOOGLETRANSLATE function. To show this, let’s look at a simple example below. In cell A2 and I have the problem reported by the teacher in English, then the translation will appear in cell B2. In cell C2 I’ve written the formula that has been used.

functions14-1

The GOOGLETRANSLATE function has 3 parts. First, the source text (here in cell A2), second, the source language (here English), and thirdly, the target language (here Spanish). Note, you have to use the codes “en” for English, “es” for Spanish (español). As you can see, it translated the original sentence fine.


Example 2 – Detecting a language

The above example works fine for our English teachers, but in our department we also have French, German, Portuguese, and Chinese teachers and not all of them speak good Spanish. So, how do allow them to use this in their own languages? This is where DETECTLANGUAGE comes in. This will look at the text and determine what language it is, then return the code for that language.

So, in cell A2 we have some text in English. in cell B2, we have the DETECTLANGUAGE function I’ve written in cell C2. As you can see, it’s correctly returned that the text is in English.

functions14-2


Example 3 – Detecting a language and translating it

Having the DETECTLANGUAGE function return the language is useful but really we then need it to combine the GOOGLETRANSLATE one to automatically change from the various languages that the problems could be written in, to Spanish.

So, in cell B2, we add the formula you can see in cell C2. Basically, we’ve replaced the source language part with the DETECTLANGUAGE function. So, it looks at the text in cell A2, then uses DETECTLANGUAGE to determine which language it is, then translates it to Spanish.

functions14-3

Here’s the same but with the problem reported in French. Notice that the translation is slightly different, as the “El” is missing this time. As we’ll see in the next example, Google Translate is good but at the moment it is still a long way off being perfect, as translating language is extremely complicated.

functions14-4


Example 4 – Automatically translating board vocabulary

Another use of this is in the classroom, where in foreign language classes it’s usual to record unknown vocabulary on the board during the lesson. I sometimes use a Google Doc and share it with the students, but here we can use a Google Sheet and it can translate the words automatically as I add them.

To do this, I have a Google Sheet with two columns. In column A I type the English words  and phrases that come up. In column B, I have a GOOGLETRANSLATE formula copied down lots of rows, so that as I type in a word, the translation appears in the cell next to it.

functions14-5

I use the same formula as in example 1, except that I’m going from English to Spanish, but also I wrap it up in an IFERROR function, just so that empty cells don’t produce an error message, instead they just leave the cells in B blank.

functions14-6

If you know some Spanish, you will notice that in fact some of the translations aren’t that good, or at least will depend on the situation. The individual words are ok, but the last two phrases aren’t what I wanted. For example, “to have a coffee” is “tomar un café” but it could be with the word “para”, if I say, “I went to the bar to have a coffee”. The last one, should translate as “hacer turismo”, as it’s come up with a translation that says “to go and see landscapes”. So, as you can see it needs to be used with care, but this can also be used as a discussion point with your students.


Example 5 – Having a conversation where neither person speaks the others language

This is a nice example where two people can have a conversation, despite not knowing each other’s language. In this case, it was two children in different countries, using a Google Sheet to communicate with each other.

In the white cells they type their conversations, one line at a time going down the page. English on the left and the other in Spanish on the right. In the yellow part are the translations. The translations are good enough for them to communicate. They could of course use this to help them learn each other’s language.

functions14-7

In column B, I’ve written this formula down the rows:

functions14-8

And in column C, I’ve written this one:

functions14-9

Again I’ve wrapped them up in the IFERROR function, so that it removes error messages.


In the GOOGLETRANSLATE we can also omit the source language and target language and let Sheets do it automatically. For example, in example 1 this function will look at cell A2, it will detect the language and then translate it to the language your Google account is in. This can be fine in many cases, but I prefer to be more prescriptive, just to avoid it coming up with strange results.

=GOOGLETRANSLATE(A2)

Also, we’ve only looked at single words and short sentences, but this works with long texts too.

Google Translate doesn’t translate all of the 6,000+ languages in the world, but it does cover the most common ones. Here’s a list of the language codes. This is continually being updated so new ones may not be on here.

Language Code Language Code
Afrikaans af Irish ga
Albanian sq Italian it
Arabic ar Japanese ja
Azerbaijani az Kannada kn
Basque eu Korean ko
Bengali bn Latin la
Belarusian be Latvian lv
Bulgarian bg Lithuanian lt
Catalan ca Macedonian mk
Chinese Simplified zh-CN Malay ms
Chinese Traditional zh-TW Maltese mt
Croatian hr Norwegian no
Czech cs Persian fa
Danish da Polish pl
Dutch nl Portuguese pt
English en Romanian ro
Esperanto eo Russian ru
Estonian et Serbian sr
Filipino tl Slovak sk
Finnish fi Slovenian sl
French fr Spanish es
Galician gl Swahili sw
Georgian ka Swedish sv
German de Tamil ta
Greek el Telugu te
Gujarati gu Thai th
Haitian Creole ht Turkish tr
Hebrew iw Ukrainian uk
Hindi hi Urdu ur
Hungarian hu Vietnamese vi
Icelandic is Welsh cy
Indonesian id Yiddish yi

In summary, it’s a great way to automatically translate within a document.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s