Create a Translator Using Google Sheets API & Python

Spreadsheets are among the most popular office utilities in the world. Almost all professions use spreadsheets for a wide of ranger reasons, from tallying numbers and displaying them in graphs to doing unit conversions, just to mention a few.

Google Sheets is one of the more popular spreadsheet applications available today. Backed up by the Google platform, it has some nifty features that make it stand from its competitors.

In this tutorial,  you will learn how to use the power of Google Sheets API and Python to build a simple language translator.

Project Setup

For this tutorial, you will need a Google account, so go ahead and create one if you do not have one already.

Once you have an account, go here and click “Enable the Google Sheets API”. A modal dialog will pop with your client Id and client secret. Click “Download client configuration” to save the configuration on your machine. Remember to copy the downloaded file to your project directory.

You now need to install a couple of dependencies. In your project directory, run the following:

Note that you might need start the command with sudo  on Mac and Linux to avoid permissions issues.

Client Setup

Since the Google API client is usually used to access to access all Google APIs, you need to restrict the scope the to Sheets.

First, you need to specify the credentials file you downloaded earlier.

 

Next, you need to restrict access by specifying the scope.

 

Now that you have successfully defined the scope, you need to build a service that will be responsible for interacting with the API. The following function grabs the constants defined before, builds and returns the service that will interact with the API.

 

Now add the following lines and run your script to make sure the client has been setup properly.

 

When you run the script you will be presented with an authorization URL. Copy it and open it in your browser.

Follow through the prompts till  you get to the following screen:

Copy and paste the code from the browser back in the Terminal / Command Prompt. At this point, your script should exit successfully indicating that you have properly setup your client.

Cache Credentials

If you run the script again you will notice that you have to go through the entire authorization process. This can be quite annoying if you have to run your script multiple times. You will need to cache the credentials so that they are reused every time you run the script. Make the following changes to the

get_authenticated_service function.

What you have added is the caching of credentials retrieved  and storing them in a file using Python’s pickle format. The authorization flow is only launched if the stored file does not exist, or the credentials in the stored file are invalid or have expired.

If you run the script again you will notice that a file named token.pickle is created. Once this file is created, running the script again does not launch the authorization flow.

Sheet Setup

It is time to start working on the translator.  Firstly, you need to create a function that will handle the translation. Add the following snippet:

 

The translate function does a couple of things. First of all, it creates a new sheet called translator and saves the id in spreadsheet_id.

 

Next, it populates the sheet, cells A2 and B2. Cell B2 holds the translate function. You will notice that the translate function references cell A2  and that it translates from English (en) to French (fr). Since the GOOGLETRANSLATE  function is linked to Google’s machine translation API, it supports all languages supported by the API:

 

Finally, the machine translation is retrieved and displayed:

 

You need to add a call to the newly created function at the bottom of the script:

 

When you run the script, you will get the following output:

 

That’s it! You have successfully built a simple translator using Google Sheets.

One thing to note is that the translate  method creates a new sheet every time the script is run. You can easily delete the sheets by going to the dashboard and deleting them.

Complete Project Code

 

 

No votes yet.
Please wait...

Leave a Reply