Web and import

GOOGLETRANSLATE and DETECTLANGUAGE functions in Google Sheets

GOOGLETRANSLATE translates text from one language into another; DETECTLANGUAGE reports which language a text is in.

GOOGLETRANSLATE puts machine translation into a cell. A column of product names, survey answers or messages can be translated without leaving the sheet, and the translations refresh if the original text changes.

No results are printed here, because a translation service can change its answers over time, and we would rather not print one that Sheets no longer gives.

Syntax

=GOOGLETRANSLATE(text, [source_language], [target_language])

=DETECTLANGUAGE(text_or_range)

Examples

• =GOOGLETRANSLATE("Good morning","en","hi")Copy
  Result: depends on live data or changes each time

  English to Hindi. The result appears in Devanagari script.

• =GOOGLETRANSLATE("Thank you","en","pa")Copy
  Result: depends on live data or changes each time

  English to Punjabi, in Gurmukhi script.

• =GOOGLETRANSLATE(A2,"auto","en")Copy
  Result: depends on live data or changes each time

  "auto" as the source lets Google detect the language of the cell.

• =DETECTLANGUAGE(A2)Copy
  Result: depends on live data or changes each time

  The code of the language the text appears to be in, such as "hi" or "fr".

• =ARRAYFORMULA(IF(A2:A10="","",GOOGLETRANSLATE(A2:A10,"en","hi")))Copy
  Result: depends on live data or changes each time

  A whole column translated by one formula.

• =GOOGLETRANSLATE(A2,"en",B1)Copy
  Result: depends on live data or changes each time

  The target language taken from a cell, so one sheet can serve several languages.

How it works

Give the text, the language it is in and the language you want. Languages are named by short codes, in quotation marks.

Use "auto" for the source when the column holds a mixture of languages, or when you do not know what it is.

If the last two arguments are left out, Sheets translates from a detected language into the language of the spreadsheet.

It is a machine translation

The result comes from the same service as Google Translate. It is good for getting the sense of a text and for short, plain sentences. It makes mistakes with idioms, names, technical words, humour and anything where tone matters.

• Do not send a machine translation to customers, or print it, without a person who knows the language reading it first.
• Names of people, places and brands may be translated when they should be left alone.
• A single word often has several meanings, and the function sees no context. Translating a whole sentence usually gives a better result than translating word by word.

For legal, medical or official documents, use a qualified translator.

DETECTLANGUAGE

DETECTLANGUAGE returns the code of the language a text seems to be written in. Very short texts are hard to judge and can be wrong. It is useful for sorting a column of mixed-language responses before translating them.

Translating a column

Copy the formula down, or use ARRAYFORMULA as in the fifth example. Guard against empty rows with IF, since translating an empty cell gives an error.

To keep translations fixed, so that they do not change later, copy the results and paste them back with Paste special, Values only.

Two-way checking

A rough test of a translation is to translate it back: =GOOGLETRANSLATE(GOOGLETRANSLATE(A2,"en","hi"),"hi","en"). If the round trip returns something close to the original, the translation is probably reasonable. It is a hint, not a guarantee.

Limits and privacy

• The text is sent to Google's translation service to be translated. Think before using it on private or confidential information.
• A sheet with thousands of translation formulas can be slow to load, and cells may show "Loading..." for a while. Heavy use can hit daily limits, after which cells return errors until later.
• Formatting inside a cell, such as bold words, is not carried over.

Common mistakes

• Language codes without quotation marks, or the full name in place of the code.
• Source and target the wrong way round.
• #VALUE! from an empty cell or a number. The function needs text.
• Using the output without a human check.

In Excel

Excel has no GOOGLETRANSLATE. Recent Microsoft 365 has a TRANSLATE function and a Translate command on the Review tab. Translation formulas do not convert between the two programs.

Related functions

• ARRAYFORMULAOnly in Sheets
• IMPORTHTML, IMPORTXML and IMPORTDATAWeb and import
• SPLIT and JOINOnly in Sheets
• IF and IFSLogical

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.