Do you ever find yourself struggling to understand foreign language text in your sheets?
The GOOGLETRANSLATE formula is here to save the day! It not only provides efficient translations, but in most cases, it also eliminates the need for any additional software or tools.
But what if the GOOGLETRANSLATE formula isn’t cutting it?
In this article, I’ve got a few alternative translation methods up my sleeve that will have you translating like a pro in no time.
Before I wrap up, don’t forget to check out the helpful tips I’ve included at the end of this article! You’ll find some great advice on how to prepare your sheet for translation. If you’re new to the world of translation, don’t worry – I’ve got you covered.
- Understanding the Google Sheets Translate Formula (GOOGLETRANSLATE function syntax)
- Translating Text in Google Sheets
- Using DeepL for Translation in Google Sheets
- Third-Party Add-ons for Translation in Google Sheets
- Bonus material: Organising Your Google Sheet for Translations for Beginners
- My Final Thoughts
Understanding the Google Sheets Translate Formula (GOOGLETRANSLATE function syntax)
The Google Sheets Translate formula syntax is simple and takes three arguments:
=GOOGLETRANSLATE(text, source_language, target_language)
text: represents the content you want to translate
source_language: is the language code of the original text
target_language: is the language code of the desired translated language.
target_languageshould be provided in the ISO 639-1 two-letter language code
- If you’re unsure of the source language or want Google Sheets to detect it automatically, you can use the “auto” option as an argument like so:
=GOOGLETRANSLATE('text to translate', 'auto', 'fr')
alternatively, you can use the GOOGLEDETECT function to detect the language first.
Translating Text in Google Sheets
Here I prepared a step-by-step guide on how to use the translation formula
Step 1: Open a new spreadsheet or the working sheet I prepared (if you’d like to follow along)
Step 2: In A column create a “default language” header and add some phrases for translations below
Step 3: In column B create a “french translation” header and enter this formula:
=GOOGLETRANSLATE(A2, "en", "fr")
Step 4 (Optional): If you want to translate the entire column, click on the blue handle and drag the formula down to apply it to all cells.
Step 5 (Optional): If you want to translate the text into multiple languages simply repeat Step 3 for each language you want to translate to.
Using DeepL for Translation in Google Sheets
Step 1: Open your Google Sheet, and click on the “Extensions” menu at the top. Then, select “Apps Script” to open the script editor for Google Sheets.
Step 2: Create a new script file by naming it “DeepL.gs”. Copy the contents from this file
Step 3: Locate line 26 in the script, which should look like this:
const authKey = "b493b8ef-0176-215d-82fe-e28f182c9544:fx"; // Replace with your authentication key
You can find your DeepL Authentication Key in the summary tab (Scroll down until you see the “Authentication Key for DeepL API” tab).
Step 4: Close the Apps Script editor and go back to your Google Sheet
Step 5: Use the DeepLTranslate function to translate text like so:
DeepLTranslate("Good morning", "auto", "FR")
Step 6: If you need more detailed information on how to use the function you can find it on the project page
If you are wondering why DeepL may be a better solution make sure to check this article comparing Google Translate and DeepL features
Third-Party Add-ons for Translation in Google Sheets
Did you know there are other add-ons you can use to make translating even easier? These add-ons offer extra features to help you translate your spreadsheet content quickly and efficiently. Check out some of the most popular ones below:
Translate My Sheet
Download Translate My Sheet here
At the time of writing, this is one of the most popular translation plugins for Google Sheets.
While it doesn’t offer much beyond what the built-in GOOGLETRANSLATE function can do (it’s actually using the same Google Translate engine), it does have one major advantage: it can translate the contents of your cells directly.
However, there is a downside too. The free version has a daily limit of a few thousand cells, so you may need to upgrade to a paid plan if you have a lot of content to translate.
On top of that, there’s one thing I personally don’t like about this plugin. The thing is that it automatically formats translated cells with a green background.
InstaGlot is another add-on for translating content within Google Sheets, designed to simplify the process.
It uses the powerful DeepL engine to offer real-time translations in 26 different languages, making it a great option if you want a straightforward translation solution without having to play with App Scripts as I described in the previous chapter.
In my opinion, it’s unnecessary as you can integrate DeepL directly without using a middleman like InstaGlot.
This add-on is very similar to Translate My Sheet (in fact, it was also written by the same author). The main difference is that it has a more user-friendly interface and can be used with other Google Workspace services, including Google Docs and Google Forms.
Bonus material: Organising Your Google Sheet for Translations for Beginners
To have a successful translation process in Google Sheets, it’s important to prepare and organize your data properly. Here are some tips to help you get started:
Clean and format your data:
Before starting the translation process, ensure your data is free from inconsistencies, errors, and formatting issues.
Remove any extra spaces, unwanted characters, or incorrect line breaks. Also, ensure your text is consistently formatted with proper capitalization, punctuation, and font styles.
Separate content into columns
Divide your content into logical sections or columns to make it easier to manage.
For example, if you’re translating product descriptions, create separate columns for product names, descriptions, and specifications.
This approach allows you to focus on translating one column at a time, and it minimizes the risk of errors or missed translations.
Create separate columns for translations
For each language, you want to translate your content into, create a separate column next to the original text.
This organization makes it easier to track and review translations, as well as modify them if needed.
For example, if your original text is in column A, you could create columns B, C, and D for translations in Spanish, French, and German, respectively.
Add header labels
Label each column with a clear header to indicate the content and language. This practice helps you quickly identify the original text and its translations while working with the data.
Use headers like “Product Name (EN)”, “Product Name (ES)”, “Product Name (FR)”, etc.
Freeze header row
To move around your sheet easily, freeze the header row. This means that the headers will stay put even when you scroll down the page. It also gives you a consistent place to look for the original text and its translations.
Consider using data validation
If you’re sharing your Google Sheet with others, consider using data validation to prevent accidental modification of the original text or language codes. This feature helps maintain the integrity of your data and reduces the risk of translation errors.
My Final Thoughts
In this article, I showed you how to use Google Sheets for translations. You can use the powerful GOOGLETRANSLATE formula and third-party add-ons to make translations easy.
I also gave you tips on how to prepare your data and organize it effectively in Google Sheets. These tips can help you avoid mistakes and make your translations smoother and more efficient.
I hope this guide will be helpful to anyone who wants to translate the content within Google Sheets, whether you’re a beginner or an advanced user.