GOOGLETRANSLATE函数自动翻译功能失效问题求助

I’ve run into this exact issue before! When using GOOGLETRANSLATE with "auto" as the target language, the function sometimes returns a language code with a country/region suffix (like pl-PL) instead of the standard two-letter ISO 639-1 code (like pl). Since GOOGLETRANSLATE only recognizes those two-letter codes, you get that frustrating "Google Translate doesn't support translating from en to pl-PL" error.

Why This Happens #

The GOOGLETRANSLATE function expects target language codes to be in the two-letter ISO format, but the automatic language detection can sometimes append a country code (e.g., -PL for Poland) to the language code. This extended format isn't supported by the function, causing the failure.

Solutions to Fix It #

1. Pure Formula Fix (No Scripts Needed)

Use the LEFT function to extract the first two characters of the detected language code, ensuring you pass a valid two-letter code to GOOGLETRANSLATE:

=GOOGLETRANSLATE("Cat", "en", LEFT(GOOGLETRANSLATE("test", "auto", "en"), 2))

Here’s how it works:

• The inner GOOGLETRANSLATE("test", "auto", "en") triggers automatic language detection to get your preferred language code (even if it has a country suffix).
• LEFT(..., 2) strips off any suffix, leaving just the two-letter language code.
• The outer GOOGLETRANSLATE uses this cleaned code as the target, avoiding the error.

2. Custom Function (More Reliable)

For a cleaner, more stable solution, create a custom Google Apps Script function to fetch your interface language directly (without any suffix):

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Paste this code:

function AUTO_TARGET_LANG() {
  // Get the user's interface language and return first two characters
  return Session.getActiveUserLocale().substring(0, 2);
}

3. Save the script (name it something like "AutoLanguageFix").
4. Back in your sheet, use the function in your formula:

=GOOGLETRANSLATE("Cat", "en", AUTO_TARGET_LANG())

This method pulls the user's actual interface language directly from Google's session data, so you’ll always get a valid two-letter code—no need to rely on translation detection quirks.

Preventing Future Issues #

Both methods ensure you’re always passing a valid two-letter language code to GOOGLETRANSLATE, so this error won’t pop up for any language with a country-code suffix (like es-ES, fr-CA, etc.).

内容的提问来源于stack exchange，提问作者outlying