2

I have a large spreadsheet with a list of English phrases/words in one column and then another column where all of those are translated into another language using the GOOGLETRANSLATE function. One example of such a row:internal Google error

The formula is =GOOGLETRANSLATE(G786, "en", "nl")

When I click on it a box appears saying "Error" and then beneath that

"Google Translate internal error."

I am unsure what the issue is and how I can solve it. The strange thing is that if I change the formula to replace the cell number to a plain string like "Hello" and click enter it will properly translate it to Dutch, and actually if I re-insert the same formula shown in the picture with the cell number it actually translates it as expected.

EN NL
Confirm before proceeding =GOOGLETRANSLATE(G777, "en", "nl")
Improve this question
4
  • I attempted to recreate this formula, but the result is exactly as expected, with no errors, if you can share a sample please do so, see How to create a Minimal, Reproducible Example Commented Oct 31, 2022 at 18:47
  • I cannot reproduce this in a new sheet, this may be an issue because it's a very large spreadsheet perhaps? Commented Oct 31, 2022 at 18:49
  • it could be. from your image you are using formula per cell... did you try to run Lambda instead? Commented Oct 31, 2022 at 18:51
  • 1
    @Aamir yes, It might be a GOOGLETRANSLATE unhandled limits error, working fine for me see this. Commented Oct 31, 2022 at 18:51

4 Answers 4

Reset to default
1

try in H2:

=BYROW(G2:INDEX(G:G; MAX((G:G<>"")*ROW(G:G))); 
 LAMBDA(x; GOOGLETRANSLATE(x; G1; H1))
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I tried this and it does work, but this only applies the function once, right and then leaves it as plain text? One of the advantages of using the function in each cell was if I changed the english translation for any cell it would update it without needing to re-run the translation manually
@Player0, Does this method of "selecting the start of the array" have a name 'G2:INDEX'?
@Aamir not sure what do you mean. the formula will automatically recalculate if any change in G column occurs (unless you inject values into your sheet with a script?)
@Osm not aware of any. how about DDIRS (dynamic direct indexed range selection)
1

The Googletranslate function appears to create this internal error when there is more than one possible meaning of a word or sentence e.g. when I type "one" (1). which also has the meaning of united etc. it creates an error but it translates "one dog" correctly.

To get a list of verbs, I wrote e.g. "to love" . It created an error , it seems because "to love" is not just an infinitive but can can include the meaning "in order to love" .

So it seems the googletranslate function works much better with sentences where the translation is unique, than it does with single or a few words.

Writing period/ full stops "." after a short sentence or phrase also removes this internal error

Improve this answer

Comments

1

Just mark the cell or the row, press "command" + "R", and then "Command" + "Z". This will fix the issue.

Improve this answer

Comments

0

This seems to be a new problem because I've been using GOOGLETRANSLATE for about five years on lots of spreadsheets (vocabulary translations with thousands of entries). They have had a habit of timing out and becoming unusable. This error seems to have replaced the timing out. The period seems to have worked, so I just need to REGEXREPLACE it in the translation, I guess. And I need to do some more copy-and-paste-values-only once I have good translations.

=REGEXREPLACE(GOOGLETRANSLATE(concatenate(E430,"\."),"en","es"),"\\\.","")

adds the period, translates, and then removes it (but it strips the \ from the comments so I can't show it here!)

Improve this answer

1 Comment

Are you trying to provide a solution to the problem described at the top of this page? That is not so obvious. Please edit according to How to Answer or delete the post.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.