Search code examples
google-sheetsgoogle-translategoogle-sheets-formulagoogle-translation-api

Is there any way to skip particular words from translation in 'GoogleTranslate()' function in spreadsheets?


I am using GoogleTranslate() with Sheets to translate some contents into different languages. In those contents, we have some placeholders that do not need to translate. E.g.:

This is a Sample Text added by `{__NAME__}` on `{__DATE__}`

I do not need to translate placeholders like {__NAME__} and {__DATE__}.

How can I skip those words from the translation process?


Solution

  • =ARRAYFORMULA(QUERY(IFERROR(VLOOKUP(TRANSPOSE(SPLIT(GOOGLETRANSLATE(QUERY(
     REGEXREPLACE(TRANSPOSE(SPLIT(A1, " ")), "\{.*\}", "♦"),, 999^99), "en", "es"), " ")),
     {REGEXREPLACE(TRANSPOSE(SPLIT(A1, " ")), "\{.*\}", "♦"), TRANSPOSE(SPLIT(A1, " "))}, 2, 0),
     TRANSPOSE(SPLIT(GOOGLETRANSLATE(QUERY(REGEXREPLACE(
     TRANSPOSE(SPLIT(A1, " ")), "\{.*\}", "♦"),,999), "en", "es"), " "))),,999^99))
    

    0