Google translate formula not working properly in array function-CodePudding

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

CodePudding user response：

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

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

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

or you can try:

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

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