This is a super simple formula in Google Sheets, and I don't want to understate its utility. You can literally translate text from any language into another language. This formula came out on Google Sheets in early 2019 I believe. You basically don't have to copy and paste into Google Translate anymore to get the translation you need. This function could be helpful for those of you who are are working with PDFs that contain tables of text in a different language, and you need to convert the text to your native tongue. Google Sheet for this episode is here.

If you don't get the reference above in relation to language translation, watch this video :)

Le Grand Débat National dataset

One of the reasons I thought about this function is that I am trying to learn a new language myself (French). I came across this dataset on Kaggle and it is a heap of data from Emmanuel Macron's initiative to increase debate all over France in 2019 regarding issues like taxes, democracy and citizenship, and the structure of government. I have no idea how the French government collected and aggregated all this data. I believe if you were not able to attend a debate in person, you can also answer questions online (which probably contributes to the bulk of the responses).

The dataset contains a whopping 170 million words with contributions from 250,000 French citizens. It looks like there are mixed reviews about the effectiveness of Macron's initiative. For analysts and democracy, this was a big step in engaging citizens in public discourse and increased transparency around the data that was collected. We also get a rich dataset to utilize the GOOGLETRANSLATE function.

Applying the function to our dataset

I have a subset of one of the CSVs from this dataset in the Google Sheet. You'll see in Column E we have some messy text with random characters but it's clearly in French:

If I want to translate that title column into English, I start writing the GOOGLETRANSLATE function in column F. You'll notice that when you start typing "GOOGLE" in cell F1, you'll see only two built-in Google functions:

I imagine that Google will come out with many more functions that allow you to query other Google services. I think it's strange there are only two built-in Google functions right now, but it may be Google's way of nudging you to purchase an upgraded account to Google for Work to utilize more features (similar to Office 365). I can see more functions that allow you query your Gmail, Google Calendar, and Tasks. There are some built-in Google Bigquery functions if your organization has Connected Sheets and is using BigQuery as a data warehouse.

Anyway, back to GOOGLETRANSLATE. The function takes 3 arguments:

* Text to translate* Source language (current language of the text)* Target language (language you want to translate to)

The caveat is you need to know the two-letter abbreviation for the last two parameters (full list here). If we want to translate the text in column E from French to English,