How to translate documents using Google Sheets

No one can learn or know several languages. Thanks to Google for providing a powerful tool: the Google Translate application. We can translate any existing language. What can we do if we have Multilingualism in text data? For that, we can make use of Google sheets. Nowadays, we are experiencing a large set of data. We may have survey data, self-generated data, and many more types of generated data. This article will show an example of translating from more than two languages to the desired language. We need to cover some functions as well. A step-by-step guide has been shown.

Step#1 Sample dataset

Fig.1: Sample dataset

There are 8 text documents in column A; these are taken from DOAJ. We directly can translate the text document, but for more understanding, we can follow some additional functions. Fig.1 shows four different coolumns: Titles in DOAJ, Langauge code, Language, and Translation in Bengali.

Step#2 Identifying the language code

We will follow the DETECTLANGUAGE() function to identify the text-language.

Fig.2: Language Code

This is how we can identify any language (1), although, one languge(2) couldn't be detected; there may be some problems, but this will not affect any of our task. Users can behold by reading this article "Understanding the New Language Tags."

Step#3 Adding language codes

We can find the language codes from Google Language Support. Here, all the codes along with the name of languages have been populated in the next columns. We can use IMPORTHTML() function to retrieve the language codes or copy-paste from the support. This task can help us in converting the code into a language that we can understand better.

Fig.3: Language codes

Step#4 Vertical lookup for matching the codes with the languages

We will formulate:

IFERROR(VLOOKUP(B2,ARRAYFORMULA({H1:H109,G1:G109}),2,0),"No Detection")

If we break the formula, we may be able to understand the whole thing of vertical lookup. The lookup has been made using an array. VLOOKUP always works from right to left. It means, our index part always should be on the left side, but in this case, our index part "ISO-639-1 Code"/"Code" is on the right side of the language. We need to make an array to take the H column on the left side of column G. We have to use ARRAYFORMULA to do this; we will be able to do the VLOOKUP function. Finally, we can use IFERROR formula to avoid any errors, with comment statements within the quotations.

Fig.4: Vertical lookup

Step#4 Translating

This is the ultimate and the final step to translate any language. We could have done it earlier, but for identifying more, we had to do those stuff. The following formula will do the task:

GOOGLETRANSLATE(A2,"auto","bn")

we have to set two language codes: source language, and target language. We can define source language as "auto", it will detect any language. Next, we need to add the target language that we want to translate for. In that case, "bn" (Bengali) has been set. Let's see the result.

Fig.5: Translation

Fig.5 shows the translation of the different languages' text documents into Bengali. We also can see the there is a "No Detection" in the C7, which means our IFERROR comment statement worked; the language code didn't match with any language code but was able to translate.
This is how we can translate any dataset for our work.

Share it: