0

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

bumped to the homepage by Community 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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")}

0

Another workaround would be to join stuff:

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(
 GOOGLETRANSLATE(TEXTJOIN(". ", 1, B20:B24), "en", "de"), "."))))

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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