Excel VBA Google Translate Function

In this article, you will learn how to implement a Google Translate function in Excel VBA. The function will use the Google Cloud Translation API. The API is free for a small amount of usage, after that you will need to pay to use the API but it is relatively inexpensive. More information is available on the Google Cloud Translation API Pricing here. Unfortunately, due to interfacing with Powershell, be aware that this tutorial is only functional without modification on Windows.

Step 1: Setup the Google Translation API in Google Cloud Platform

Enable the Cloud Translation API

Create a new project on the Google Cloud platform if you do not already have one. Then, go to Enabled APIs & Services. If the Cloud Translation API is already enabled, you don’t have to do anything. If you can’t find it in the list, enable it.

Create Credentials for the Cloud Translation API and Macro

While still in the Google Cloud Platform Console, select Credentials from the sidebar. Click Create Credentials and choose Service Account.

Enter service account details, and click Done. Once created, click the new service account. From the top menu, select Keys. Click ADD KEY, then click Create new key. Use JSON for key type.

Step 2: Setup Google Cloud CLI

Refer to the install instructions found at https://cloud.google.com/sdk/docs/install.

Step 3: Getting an Auth Token using VBA and PowerShell

Insert a new module. (Insert > Module). Add the following function. Enter the path to your authentication file where the code says YOUR_PATH_HERE. Do not copy and paste this without modifying, it will not work. You must enter the path to your auth JSON. You may wish to modify this to be a sub and print the output for testing purposes, as shown in the video embedded above.

Function GetAuthToken()

Set wShell = CreateObject("WScript.Shell")

'Replace YOUR_PATH_HERE with the path to your JSON auth key
Set wShellExec = wShell.Exec("Powershell -executionpolicy bypass $env:GOOGLE_APPLICATION_CREDENTIALS='YOUR_PATH_HERE'; gcloud auth application-default print-access-token")

output = wShellExec.StdOut.ReadAll

GetAuthToken = output


End Function

Step 4: Create the Google Translate Function

Firstly, add VBA-JSON to your project. You can download it from the VBA-JSON Github page. Follow the instructions there to get it set up in the project. Then, add the following function into the same module, including the two global variables at the top. For more detail on how the function works, refer to the video above.

Dim authToken As String
Dim hasAttemptedAuth As Boolean

Function GTRANSLATE(text As String, targetLang As String, Optional sourceLang As String)

If authToken = "" Then
    If hasAttemptedAuth Then
        GTRANSLATE = "N/A"
        Exit Function
    End If
    authToken = GetAuthToken()
    hasAttemptedAuth = True
    
End If

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

Url = "https://translation.googleapis.com/language/translate/v2"

objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "Authorization", "Bearer " & authToken

request = "{'q': '" & text & "',"

If sourceLang <> "" Then
    request = request & "'source': '" & sourceLang & "',"
End If

request = request & "'target': '" & targetLang & "', 'format': 'text'}"

objHTTP.send request

Dim translateJSON As Object
Set translateJSON = JsonConverter.ParseJson(objHTTP.responseText)

GTRANSLATE = translateJSON("data")("translations")(1)("translatedText")

End Function

Step 5: Use the GTRANSLATE Function in Excel

You can use the GTRANLATE function in your worksheet just like you would any other built-in Excel function. Remember that the final parameter, source language, is optional. To translate to Spanish from English, you would use =GTRANSLATE("My text to translate", "es", "en") .

Congratulations! Hopefully the Excel VBA Google Translate function is now up and running on your system. Remember that in order to send a worksheet that uses the Excel VBA Google Translate function to a colleague, you will have to copy and paste values of the cells that use the function or ensure your colleague has done the same setup on their system.