How to use GoogleTranslate() with Arrayformula()
Out of the box, Google Sheets arrayformula() doesn’t support Google Translate.
If you try to use it, you’ll get a result that just gives the translation for the first sentence.
But you can trick Google Translate by combining an array into one delineated string, then using Google Translate, and then splitting it again.
This solution doesn’t exactly use ArrayFormula(). But it uses a workaround that gives, in practice, a very similar result.
Enjoy!
Google Translate with arrayformula() — In a Nutshell
The way I like to set this up in a sheet is for two columns, with the source language in the first column’s A1 heading, e.g. en, and then the target language in the B1 heading, e.g. fr.
Use this formula to use googletranslate() on an array:
=transpose(split(googletranslate(join(". ", A2:A), A1, B1),". ", false, false))This formula does this, working from the inside to the outside:
- Joins sentences in the target array of A2:A, using a “. ” (a period with a space) delineator.
- Translates the entire concatenated list from the source language to the target language.
- Splits the translated list around the same “. ” delineator.
- Transposes it into a vertical array.
Options
You can also use, instead of “a1”, “auto”. This lets Google Translate automatically detect the language.
=transpose(split(googletranslate(join(". ", A2:A), "auto", B1),". ", false, false))This works for sentences, but it might get confused for short words.
Limitations of this approach
There are a few limitations of this approach.
Firstly, it doesn’t really like punctuation. Weird things happen not just because of periods, but also with other punctuation, because of how they translate. For example, some languages don’t use exclamation marks or question marks. I would avoid using punctuation in the first column.
A secondary limitation is that every time you add a new sentence to the list, it has to translate the whole lot again. This means that there’s a brief flash in which the whole column B recalculates. This isn’t as smooth an operation as just calculating one new cell.
Thirdly, this formula doesn’t work well on languages that use different periods, like Chinese or Japanese.
For those languages, I use a modification. Use the one below if you want to translate to Chinese, Japanese, or another language with a different period.
=transpose(split(googletranslate(join(". ", A2:A), "auto", B1),(if(or(B1="ja", B1="zh", B1="zh-CN", B1="zh-TW"), "。", ". ")), false, false))Finally, a limitation of this method is the size of a string that Google Sheets can work with. A cell can hold a maximum of 50000 characters.
So you can’t use this on an impossibly long list. But I’ve tried it on lists of ~50 sentences and it hasn’t maxed out, so it does work in many practical instances.
Commonly used translation languages for Google Sheets googletranslate()
Below are some languages that people commonly translate to. It’s not the same as the full list of Google language shortcodes — for example, you can’t translate into Klingon.
Some commonly used translation language codes include:
| Language | Shortcode |
|---|---|
| English (UK, US) | en-GB, en |
| Spanish (Latin American, Spanish) | es-419, es |
| Chinese (Simplified, Traditional) | zh-CN, zh-TW |
| French | fr |
| German | de |
| Russian | ru |
| Arabic | ar |
| Japanese | ja |
| Persian | fa |
| Portuguese (Brazilian, Portuguese) | pt-BR, pt-PT |
| Korean | ko |
| Italian | it |
| Hebrew | iw |
| Maori | mi |
| Swahili | sw |
Wrap up
Go nuts and good luck!
If you found this useful, drop me a note and say thanks.
