Microsoft Excel, doesn't natively have a function for translating text from one language to another. Add it in just a few minutes with Windy.GoogleTranslate!

Translating text

The Windy.GoogleTranslate function allows translating text from one language to another. You can invoke it from a formula in a cell, just like any other function in Excel:

=Windy.GoogleTranslate(B4, "de", "en")

The above formula will translate the text in cell B4 from English ("en") to German ("de").

The function has two mandatory parameters and one optional parameter:

1. texts: an array of one or more texts to translate
2. targetLanguage: the 2-letter code of the target language (list of codes)
3. sourceLanguage [optional]: the 2-letter code of the source language

Note that the second parameter is the target language, and the third parameter is the source language (not the other way around as you might expect).

This is because passing in the target language parameter is mandatory but passing in the source language parameter is optional - if you omit it, it will be auto-detected by the Google Translate API.

For example:

=Windy.GoogleTranslate(B4, "de")

Auto-detecting the source language works well for longer texts but it can be a guessing game for shorter texts (e.g., two short words). Some short phrases are valid in multiple languages, so it's usually a good idea to specify the source language if you know it.

Translating multiple cells at once

In spreadsheets, we tend to work with data in bulk, so it might often be the case that we need to translate many cells instead of just one.

Windy.GoogleTranslate performs translation by invoking the Google Translate API. To avoid the performance penalty of sending many HTTP requests to Google servers, you can pass in an array of values to translate. The function will send all of them in bulk to the server and return an equally sized array containing translated texts.

The function uses the dynamic arrays functionality of Excel 365 to return a table of results that spills to take up the required amount of space.

In older versions of Excel, you can still make use of the function, but you'll have to manually select the area that will hold the results and enter the formula using the Ctrl+Shift+Enter command.

Caching

The translation of a word or phrase from one language into another is not something that's going to change over time (at least not very often). This makes the result of the translation a good candidate for caching.

Caching the result can save bandwidth, time, and costs, which is why the Windy.GoogleTranslate function caches results automatically. This is done at the session level, meaning that restarting Excel will clear the cache.

Language detection

Sometimes you might be dealing with text without knowing which language it's written in.

To determine which language a text is written in, use the Windy.GoogleDetectLanguage function.

You can invoke it in Excel like so:

=Windy.GoogleDetectLanguage("Some text in English")

The returned result for the above formula will be "en".

Alternatively, if you'd like to detect the source language for many cells at once, you can pass in an array of texts and the function will return an array of results of equal size.

How to install the package

Installation is done in two steps.

Step 1: Install the QueryStorm runtime. This is a free 4MB add-on for Excel that adds the ability to install various useful extensions to Excel. Basically, an app store for Excel.

Step 2: Open the Extensions dialog from the ribbon in Excel, find the function in the "online" tab, and install it.

Once that's done, the function is immediately visible in Excel. However, it's not yet ready for use. To use it, you must first:

1. Activate it via a license key
2. Supply it with a Google Translate API key.

Activating via license

When you attempt to use one of these functions, you will be prompted to enter a license, unless you have already entered one before.

To obtain a license key, visit the store to buy a monthly or yearly subscription for the Windy.GoogleTranslate package. All subscriptions start with a 3-day (for monthly subscriptions) or 7-day (for annual subscriptions) free trial period.

Once you have activated a subscription, you will immediately get a license key which you can enter into the license prompt:

Configuring the function (API key)

If you try to use the function without setting up an API key, you will see the following prompt asking you to provide an API key that will let the newly added functions talk to the Google Translate API on your behalf:

Let's see how to set up access with the API key...

The Windy.GoogleTranslate function internally uses the Google Translate API. The API has a free quota (currently 500,000 characters per month, i.e. about half of a book) but if you need to translate more characters than this, you'll need to use a paid plan.

To use the API you need a Google account and an API key that allows accessing the Google Translate API on your behalf. Here's how to create one...

1. Sign into Google Cloud Console with your Google account
2. Search for "Translation API" and click the "Cloud Translation API" option

3. Click "Enable" (you might need to add billing information to proceed, but you can limit the API to never go over the free tier).

4. Click the "Credentials" tab, then the "Create credentials" button and choose "API Key"

5. This will create your API Key

6. Once you have your API key, go to the "QueryStorm" tab in Excel, click "Configure" and paste it into the "Google API Key" in the "Windy.GoogleTranslate" tab

That's it. The Windy.GoogleTranslate function is now ready for use.

Note that the API Key should be treated as a password i.e. it should be kept secret and should not be shared around as it could be used by others to run translations at your expense. Limiting the API key to a certain amount of use can also be done in the google cloud console.

Conclusion

Adding a translate function to Excel is fairly easy. The initial setup involves creating a Google Translate API key and takes only a few minutes.

After the initial setup, you can easily translate texts in Excel from any language to any other without much hassle.