Dear Analyst #82: Translating the best lines from The Godfather with Google Translate in Sheets

I haven’t seen a good use case or template for the GOOGLETRANSLATE function in Google Sheets. I was inspired by Blake Burge’s Top 8 Google Sheets Tweet and thought a bit more about when you might use GOOGLETRANSLATE in Google Sheets. I suppose if you work as a translator or at the United Nations, you need to translate text in a spreadsheet versus a general translator tool. I think you would use GOOGLETRANSLATE when you want to dynamically translate text from one language to another and perhaps do some word analysis on the translated text. I thought it would be interesting to translate famous quotes and lines from The Godfather and create an dynamic translator tool in Google Sheets in the process. Spreadsheet here.

Source: The Guardian

Setting up quotes to translate

In the Google Sheet I have some famous quotes from The Godfather in column A. Column B is where I want to translate the quote into another language. Column C is just for translations into Chinese which we’ll talk about later.

Data validation for creating a dropdown menu

To make this a more dynamic tool, I want to turn cell B1 into a dropdown menu. This way you can select any language (from a list) and Google Sheets will automatically translate the text into that language. Off to the side in columns E and F, I wrote the main languages I want to translate into and the two-letter abbreviation. Those abbreviations can be found here.

I’ve talked about data validation before (fancy name for saying “dropdown”). See episode 46 for another instance where you would use data validation.

I only select cells E2:E7 in this situation but you might want to increase the range by a few or several rows depending on how many languages you want to be able to translate into. Now cell B1 contains a simple dropdown of the languages listed in cells E2:E7:

Setting up the Google Translate formula

In cell B2, we write the following formula:

=GOOGLETRANSLATE(A2,"auto",vlookup($B$1,E:F,2,0))

Let’s look at this in context of the spreadsheet and break down each part of the formula:

The GOOGLETRANSLATE function takes in 3 arguments:

  1. Text you want to translate
  2. Source language of your text (2-letter abbreviation)
  3. Destination language of your text (2-letter abbreviation)

A2 contains our text. For the second argument, I could’ve written “en” to represent English, but “auto” is a little more flexible as Google will try to detect the source language. This means you can put text in any language and Google should be able to detect it.

The VLOOKUP for the 3rd argument is to find the two-letter abbreviation that represents the destination language we want to translate to. Notice how the lookup value is cell B1 and it’s locked. In the previous section, we established cell B1 as the dropdown that contains the languages listed in cells E2:E7. When I drag the formula down, I want cell B1 to stay fixed since that cell always contains the language I want to translate to.

Experimenting with different languages

As you change the dropdown in cell B1, Google Translate automatically changes the translation:

Pretty neat seeing these famous quotes in different languages and being able to add new quotes over time to this spreadsheet.

Adding Chinese pinyin to Chinese translations

Unfortunately I can’t read a lot of Chinese characters and rely on pinyin to help sound out the characters. Unfortunately Google Translate in Google Sheets doesn’t have a built-in pinyin translation for Chinese characters, so I looked for one in the Extensions menu. Aside from the XLMiner Analysis Toolpak extension (mimics Excel’s Analysis Toolpak add-on), I haven’t used another extension before.

When you search for pinyin add-ons, this Mandarin Cantonese API add-on is probably the best one (if not only one) that does pinyin translation from Chinese characters:

In column C, the PINYIN function simply takes one argument (Chinese characters):

I’ve always wanted to learn how to say “leave the gun, take the cannoli” in Chinese. Unfortunately cannoli doesn’t translate well into Chinese. Google Translate does do a good job of taking names and giving you the phonetic translation (see Fredo).

Other Podcasts & Blog Posts

No other episodes mentioned!