Search code examples
arraysgoogle-sheetsgoogle-sheets-formulagoogle-translateflatten

Google translate formula not working properly in array function

When I was using as such it was working perfectly.

GOOGLETRANSLATE(V2,"en","ms")

But, now I change it to array function its not translating ROW 2 and below instead its translating ROW 1 which contain the header(Director Designation)

=ArrayFormula(IFS(ROW(AE:AE)=1,"Director Designation (BM)",A:A="","",TRUE,GOOGLETRANSLATE(V:V,"en","ms")))
Solution

  • GOOGLETRANSLATE is not supported under ARRAYFORMULA. you can either hardcode it:

    ={GOOGLETRANSLATE(A2, "en", "ms");
  GOOGLETRANSLATE(A3, "en", "ms");
  GOOGLETRANSLATE(A4, "en", "ms")}

    enter image description here

    but it would be a waste of time typing the whole column by hand so you can use:

    ={""; ARRAYFORMULA("={"&TEXTJOIN(";", 1, "GOOGLETRANSLATE(A"&
 ROW(A2:A)&", ""en"", ""ms"")")&"}")}

    enter image description here

    or you can try:

    =INDEX(FLATTEN(TRIM(SPLIT(GOOGLETRANSLATE(QUERY(
 FILTER(A2:A&".", A2:A<>""),,9^9), "en", "ms"), "."))))

    enter image description here

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