I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I created a simple sheet that translates words from column A in column B. So far so good.

The problem is I can't tell Google Sheets to "translate every cell in column A to column B". I used ARRAYFORMULA but it doesn't work as expected. =ArrayFormula(GOOGLETRANSLATE(A2:A,"en","de")) only results to

only B2 is translated, not everything else

As you can see, only B2 is translated, not everything else.

FWIW, dragging the fill handle down still works but I want an endless dictionary of such translations. I created the sheet on the web app but I use it primarily via the Android app. I want to just type the word in column A and get its translation. Unfortunately, if the fill handle even works in mobile, I don't know how to trigger it. (And I know this forum is for web apps so I'm going to stop right there.)

3 Answers 3


ARRAYFORMULA does not support GOOGLETRANSLATE. The best you can have is to pre-program the array like this:

={GOOGLETRANSLATE(B20, "en", "de");
  GOOGLETRANSLATE(B21, "en", "de");
  GOOGLETRANSLATE(B22, "en", "de");
  GOOGLETRANSLATE(B23, "en", "de");
  GOOGLETRANSLATE(B24, "en", "de")}


Another workaround would be to join stuff:

 GOOGLETRANSLATE(TEXTJOIN(". ", 1, B20:B24), "en", "de"), "."))))


You may also resolve it via script or with API calls: https://developers.google.com/apps-script


After analysis, I found kind of compact solution. a1:a, "en", "ko" are options it doesn't need ARRAYFORMULA:

=TransPose(Split(GoogleTranslate(Join(". ",A1:A), "en", "ko"),". ",false,false))

Exception, if target lang is "ja", they need another way like this: =TransPose(Split(GoogleTranslate(Join(". ",A1:A), "en", "ja"),"。 ",true))


I have found @Sage's approach or any using GOOGLETRANSLATE(JOIN()) does not work well with Chinese. GOOGLETRANSLATE() seems to treat the JOIN() as a sentence no matter how meaningless it would be, and translates each character changeably according to the sentence.

What so far seems to give consistent results is


Using a literal array {} for the LAMBDA result, you can return multiple values, such as Pinyin and translation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.