Character Limit for Google Sheets Translation Function

[AdventurousHiker17]

I’m working on translating content in Google Sheets from English to Italian and some other languages. The translation process works fine, but I need to make sure each translated text doesn’t exceed 200 characters because of platform restrictions.

I’m using a vlookup function to get the English text and then translate it. However, I can’t figure out where to add a character limit check. Is there a way to modify my formula so it only translates text that’s 200 characters or less?

Here’s my current formula:

iferror(GOOGLETRANSLATE(vlookup(B4,'Data Sheet'!B:Y,12,false),"en","it"),"")

The formula works but I need to add the character restriction somehow. Any ideas how to do this?

[Tom_89Paint]

You could also truncate the text instead of skipping it entirely. Try this formula: =iferror(GOOGLETRANSLATE(left(vlookup(B4,'Data Sheet'!B:Y,12,false),200),"en","it"),""). The LEFT function cuts anything over 200 characters before translation. I’ve done this with product descriptions that were too long - you get partial translations instead of blank cells. Just heads up, chopped sentences don’t translate as well since you’re cutting context mid-sentence. If you want to track which entries got truncated, add a helper column that flags when the original text goes over your limit.

[alexlee]

You can wrap LEN around your VLOOKUP result, but don’t repeat the same lookup twice - it’s inefficient. Store it in a variable first: =iferror(if(len(vlookup(B4,'Data Sheet'!B:Y,12,false))<=200,GOOGLETRANSLATE(vlookup(B4,'Data Sheet'!B:Y,12,false),"en","it"),"Text too long"),""). Better yet, use the LET function if you’ve got it: =LET(text,vlookup(B4,'Data Sheet'!B:Y,12,false),if(len(text)<=200,GOOGLETRANSLATE(text,"en","it"),"Exceeds limit")). This way you only lookup once and check the length before translating. I’ve done this on multilingual projects - saves unnecessary API calls when text is too long.

[Liam_25Meditation]

Use the LEN function to check your vlookup length. Try: =if(len(vlookup(B4,'Data Sheet'!B:Y,12,false))<=200,GOOGLETRANSLATE(vlookup(B4,'Data Sheet'!B:Y,12,false),"en","it"),"") - it’ll only translate if it’s under 200 characters.