How to Bulk Translate with Google Sheets
Updated: Aug 5, 2023
Everyone’s heard of Google Translate, and, like me, you’ve most likely used it more than a few times. From travelling abroad, communicating with friends and colleagues or perhaps even translating content on a website to serve foreign markets, to name a few.
With Google Translate, you can translate simple words and sentences, documents or entire websites using this tool, but did you know that you can also achieve this using Google Sheets?
You can download the Google Sheets Template for FREE below and start using it straight away.
This might come in handy if you have a list of sentences or words to translate at once. The neat thing about this function in Google Sheets is that you can translate hundreds of sentences simultaneously.
Let’s dive in and have a look.
Syntax
The syntax is straightforward. You need the original text to be translated, defined as text in the function, followed by the [source_language] and then the [target_language] language.
GOOGLETRANSLATE(text, [source_language], [target_language])
Google Translate
We’ll choose cell A3 for our text, then just add a comma to move into the source language argument.
=GOOGLETRANSLATE(A3,
For the source and target language, we need to use the two-letter language code, for example, "en" for English or "fr" for French.
Alternatively, you can set this to 'auto', which will then automatically detect the source language.
We must wrap these two letter-language codes in double quotes and separate the source and target languages with a comma. This will translate from English to French in this example.
=GOOGLETRANSLATE(A3,"en", "fr"
Just close the parentheses to finish the function, and you’re done. Now we have our sentence translated.
=GOOGLETRANSLATE(A3,"en","fr")
If you double-click on the little blue square, it will copy this function down to all the cells below that need translating.
We just translated these six sentences in a few seconds. If you had hundreds of lines to translate, it would take next to no time to get the job done.
We can make this dynamic too so that we can choose a language from a dropdown list which will then collect the two-letter-language code for us using a bit of magic. Well, not strictly; we just need a few more functions and some data validation to get this to work.
Import HTML
Using the IMPORTHTML function, we can import the two-letter-language code table found on Wikipedia.
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes","table",1)
Data Validation
Then we can set up Data Validation to collect all the country names from this imported table which we will display as a dropdown menu on the main translation table.
Now we’ll be able to choose the country we want from this list. But for this dropdown to work, we’ll need to make two more changes.
VLOOKUP Function
First, we need to collect the two-letter-language code once a language has been chosen from the dropdown menu, and we can do this using the VLOOKUP function.
Lastly, we can update our original formula to make the target language part dynamic by choosing this $C$2 cell, which collects the country code. You’ll need to ensure the cell is locked to make it absolute, so when we copy it down, this part does not move.
When you choose another language from the dropdown menu, the two-letter-language code will update automatically from the VLOOKUP function. Everything will get translated into German or whatever language you choose.
Conclusion
This can save lots of time if you don’t have specific people to translate sentences for you assuming you want to use it on a website, for example, or you can just compile a bunch of sentences you think you might need for your next trip to a foreign country.
Granted, Google Translate is not 100% perfect by any means, but it does the job pretty well.
Here is the FREE Google Translate Tool used in this tutorial; feel free to make a copy and use it as you wish, or have a go and make one yourself.
Comments