1

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")
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Aamir
  • 443
  • 3
  • 16
  • 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](https://stackoverflow.com/help/minimal-reproducible-example) – Osm Oct 31 '22 at 18:47
  • I cannot reproduce this in a new sheet, this may be an issue because it's a very large spreadsheet perhaps? – Aamir Oct 31 '22 at 18:49
  • it could be. from your image you are using formula per cell... did you try to run Lambda instead? – player0 Oct 31 '22 at 18:51
  • 1
    @Aamir yes, It might be a `GOOGLETRANSLATE` unhandled limits error, working fine for me [see this](https://i.imgur.com/IS9KxpL.png). – Osm Oct 31 '22 at 18:51

3 Answers3

1

try in H2:

=BYROW(G2:INDEX(G:G; MAX((G:G<>"")*ROW(G:G))); 
 LAMBDA(x; GOOGLETRANSLATE(x; G1; H1))
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    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 – Aamir Oct 31 '22 at 19:03
  • @Player0, Does this method of "selecting the start of the array" have a name 'G2:INDEX'? – Osm Oct 31 '22 at 19:14
  • @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?) – player0 Oct 31 '22 at 19:53
  • 1
    @Osm not aware of any. how about DDIRS (dynamic direct indexed range selection) – player0 Oct 31 '22 at 19:57
0

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

Ian
  • 1
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!)

Tyler2P
  • 2,324
  • 26
  • 22
  • 31
Matthew Link
  • 1
  • 1
  • 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 [answer] or delete the post. – Yunnosch Dec 04 '22 at 16:57