GoogleTranslate | Google Translate | REST library

I read the comments and you seem to be doing everything ok. I would recommend you to try:

• This error message can appear because you set API restrictions in the API key, is this the case? Maybe you’re restricting this specific API.
• If you aren’t setting any API restrictions, is it possible to try adding an IP instead of the domain just for testing purposes?

based on the GOOGLETRANSLATE and DETECTLANGUAGE functions,

try this:

Try this:

=arrayformula(if(A1<>"",join("",if(isnumber(flatten(split(GOOGLETRANSLATE(join(" ",iferror(regexreplace(to_text(flatten(split(A1," "))),"(\[.*\])|(\%.*\%)","["&row(A$1:A)&"]"),)),"en","fr"),"[]"))),vlookup(flatten(split(GOOGLETRANSLATE(join(" ",iferror(regexreplace(to_text(flatten(split(A1," "))),"(\[.*\])|(\%.*\%)","["&row(A$1:A)&"]"),)),"en","fr"),"[]")),{sequence(len(regexreplace(A1,"[^\ ]",))+1,1),flatten(split(A1," "))},2,false),flatten(split(GOOGLETRANSLATE(join(" ",iferror(regexreplace(to_text(flatten(split(A1," "))),"(\[.*\])|(\%.*\%)","["&row(A$1:A)&"]"),)),"en","fr"),"[]")))),))

GOOGLETRANSLATE does not work with ARRAYFORMULA, but you can drag down this formula from cell B1 if you want to apply it to multiple rows in column A.

Individual steps taken:

1. Split text by space character, then flatten into one column.

   Cell D1: =flatten(split(A1," "))

2. Replace [***] and %***% with [row#].

   Cell E1: =arrayformula(iferror(regexreplace(to_text(flatten(split(A1," "))),"(\[.*\])|(\%.*\%)","["&row(A$1:A)&"]"),))

3. Join the rows into one cell.

   Cell F1: =join(" ",E:E)

4. Apply Google Translate.

   Cell G1: =GOOGLETRANSLATE(F1,"en","fr")

5. Split by [].

   Cell H1: =flatten(split(G1,"[]"))

6. Where rows contain numbers, lookup item 1) above.

   Cell I1: =arrayformula(if(isnumber(H1:H),vlookup(H1:H,{row(A$1:A),D:D},2,false),H1:H))

7. Join the rows into one cell.

   Cell J1: =join(" ",I:I)

I thought that when you are actually using deeplapi() in your question, that function doesn't return the value. Because the last line of the function is Logger.log(data);. And also, in your following situation (This is from your question),

You are using the function deeplapi() as deeplapi(value1, value2, value3). But, your function deeplapi() doesn't use the arguments. I thought that these might be the reason of your issue.

If you want to use your function as deeplapi(value1, value2, value3), how about the following modification?

I believe your goal as follows.

• You want to translate the following situation.

  • From

EDIT (following the comment)

You need to wrap the formula in the IFERROR function

This is a known issue, and it is a design decision by Google to use the older translation feature. See this issue: https://issuetracker.google.com/178959844. Perhaps you could create a Feature Request to have the better translation. If so you could do it in the the same public Issue Tracker and clicking on the "Create Issue" button.

like regex

Using the function keyword changes the 'this' context. You can either save 'this' outside of the function or use arrow functions.

Here's how you would use an arrow function