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.

functions14-4

=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.

LanguageCodeLanguageCode
AfrikaansafIrishga
AlbaniansqItalianit
ArabicarJapaneseja
AzerbaijaniazKannadakn
BasqueeuKoreanko
BengalibnLatinla
BelarusianbeLatvianlv
BulgarianbgLithuanianlt
CatalancaMacedonianmk
Chinese Simplifiedzh-CNMalayms
Chinese Traditionalzh-TWMaltesemt
CroatianhrNorwegianno
CzechcsPersianfa
DanishdaPolishpl
DutchnlPortuguesept
EnglishenRomanianro
EsperantoeoRussianru
EstonianetSerbiansr
FilipinotlSlovaksk
FinnishfiSloveniansl
FrenchfrSpanishes
GalicianglSwahilisw
GeorgiankaSwedishsv
GermandeTamilta
GreekelTelugute
GujaratiguThaith
Haitian CreolehtTurkishtr
HebrewiwUkrainianuk
HindihiUrduur
HungarianhuVietnamesevi
IcelandicisWelshcy
IndonesianidYiddishyi

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


This post is taken from my book “Google Sheets Functions – A step-by-step Guide“, available on Amazon here.

Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).

JavaScript Fundamentals for Apps Script users

a

Google Apps Script Projects 1
Google Apps Script Projects 2

9 comments

  1. The word translated by formula “GOOGLETRASLATE” is not same as “Google Translate- web site” . For Ex: South Korean word “(주)광주과학” is translated as “Ltd. Guangzhou Science” by “GOOGLETRANSLATE”, but “Gwangju Science Co., Ltd.” by the web site. Any idea why are they different?

    1. To be honest no but I’ve found that for short translations the formula one can be very literal, I.e. translating word for word rather than whole phrases.

  2. I want to translate Danish texts to 50 languages and vice versa in Google Sheets

    Se this screen shot on Quora:

    https://qph2.c7.quoracdn.net/main-qimg-1a75b5d2e378860cd657aea19dbc3be9

    I want to use cell dat from the spreadsheet’s cells

    B2 is the cell that contains the text for translations from Danish

    “C$4” translated from this language into this language “D$4”

    Instead of using “da” and “en” in the cells, language must be retrieved using cell name as shown below.

    I know how to translate into Google sheets. But my problem is that I want the translation to retrieve the language code from a particular cell.

    Like this:

    = GOOGLETRANSLATE (B2; $ C4; $ D4)

    And not so:

    = GOOGLETRANSLATE (B2; “da”; “el”)

    Because I have to translate thousands of words to and from different languages. Danish to English, French to Italian etc.

    Not only once but this work is fixed for months to come.

    I need to do it easily and quickly to save on working hours.

    Best regards

    Leif

    1. Hi Leif,
      I don’t see a problem with your =GOOGLETRANSLATE($B$2, $C4, $D4) apart from putting the dollar signs around both parts of the B2, so the reference doesn’t change as you go down the rows.

  3. This is really nice, Is there a way to also display a romanized texts for languages so that when I translate Thai I get it in both Thai script and phonic English? Thanks

  4. Baz, say we have a Google Doc, as opposed to a sheet. How could we create a copy of that file in the same folder as a translated doc?

    Ex:
    list of URL of Google Docs in English
    Need to loop through, create copy (or create new doc) with same formating, converting all text to Spanish.

Comments are closed.