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.

=GOOGLETRANSLATE(A2,“en”, “es”)

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.

=DETECTLANGUAGE(A2)

---

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.

=GOOGLETRANSLATE(A2,detectlanguage(A2), “es”)

Here’s the same but with the problem reported in French. 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.

=GOOGLETRANSLATE(A2,DETECTLANGUAGE(A2), “es”)

---

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.

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.

=iferror(GOOGLETRANSLATE(A2, “en”, “es”),“”)

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.

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

=IFERROR(GOOGLETRANSLATE(A2,”en”, “es”),””)

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

=IFERROR(GOOGLETRANSLATE(D2,”es”, “en”),””)

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.

---