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:
|
1 2 |
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib |
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.
|
1 2 |
CREDENTIALS_FILE = "credentials.json" |
Next, you need to restrict access by specifying the scope.
|
1 2 3 4 |
SCOPES = ['https://www.googleapis.com/auth/spreadsheets'] API_SERVICE_NAME = 'sheets' API_VERSION = 'v4' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
import google.oauth2.credentials from googleapiclient.discovery import build from googleapiclient.errors import HttpError from google_auth_oauthlib.flow import InstalledAppFlow def get_authenticated_service(): flow = InstalledAppFlow.from_client_secrets_file(CREDENTIALS_FILE, SCOPES) credentials = flow.run_console() return build(API_SERVICE_NAME, API_VERSION, credentials = credentials) |
Now add the following lines and run your script to make sure the client has been setup properly.
|
1 2 3 4 5 6 |
if __name__ == '__main__': # When running locally, disable OAuthlib's HTTPs verification. When # running in production *do not* leave this option enabled. os.environ['OAUTHLIB_INSECURE_TRANSPORT'] = '1' service = get_authenticated_service() |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
import os import pickle import google.oauth2.credentials from googleapiclient.discovery import build from googleapiclient.errors import HttpError from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request ... ... def get_authenticated_service(): credentials = None if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: credentials = pickle.load(token) # Check if the credentials are invalid or do not exist if not credentials or not credentials.valid: # Check if the credentials have expired if credentials and credentials.expired and credentials.refresh_token: credentials.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( CREDENTIALS_FILE, SCOPES) credentials = flow.run_console() # Save the credentials for the next run with open('token.pickle', 'wb') as token: pickle.dump(credentials, token) return build(API_SERVICE_NAME, API_VERSION, credentials = credentials) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
def translate(service, phrase): sheet = service.spreadsheets() spreadsheet = { 'properties': { 'title': 'translator' } } print('Creating sheet...') spreadsheet = sheet.create(body=spreadsheet, fields='spreadsheetId').execute() spreadsheet_id = spreadsheet.get('spreadsheetId') values = [ [ phrase, '=GOOGLETRANSLATE(A2, "en", "fr")' ], ] body = { 'values': values } print('Translating phrase...') result = service.spreadsheets().values().update( spreadsheetId=spreadsheet_id, range='A2:B2', valueInputOption='USER_ENTERED', body=body).execute() print('Retrieving translation...') result = service.spreadsheets().values().get( spreadsheetId=spreadsheet_id, range='B2').execute() rows = result.get('values', []) print('Original: {}'.format(phrase)) print('Translation: {}'.format(rows[0][0])) print('Done') |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
.... sheet = service.spreadsheets() spreadsheet = { 'properties': { 'title': 'translator' } } print('Creating sheet...') spreadsheet = sheet.create(body=spreadsheet, fields='spreadsheetId').execute() spreadsheet_id = spreadsheet.get('spreadsheetId') |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
.... values = [ [ phrase, '=GOOGLETRANSLATE(A2, "en", "fr")' ], ] body = { 'values': values } print('Translating phrase...') result = service.spreadsheets().values().update( spreadsheetId=spreadsheet_id, range='A2:B2', valueInputOption='USER_ENTERED', body=body).execute() |
Finally, the machine translation is retrieved and displayed:
|
1 2 3 4 5 6 7 8 9 10 11 |
.... print('Retrieving translation...') result = service.spreadsheets().values().get( spreadsheetId=spreadsheet_id, range='B2').execute() rows = result.get('values', []) print('Original: {}'.format(phrase)) print('Translation: {}'.format(rows[0][0])) print('Done') |
You need to add a call to the newly created function at the bottom of the script:
|
1 2 3 4 |
.... service = get_authenticated_service() translate(service, 'Hello') |
When you run the script, you will get the following output:
|
1 2 3 4 5 6 7 |
Creating sheet... Translating phrase... Retrieving translation... Original: Hello Translation: Bonjour Done |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
import os import pickle import google.oauth2.credentials from googleapiclient.discovery import build from googleapiclient.errors import HttpError from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request CREDENTIALS_FILE = "credentials.json" SCOPES = ['https://www.googleapis.com/auth/spreadsheets'] API_SERVICE_NAME = 'sheets' API_VERSION = 'v4' def get_authenticated_service(): credentials = None # Check if saved token exists if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: credentials = pickle.load(token) # Check if credentials are valid if not credentials or not credentials.valid: # Check if credentials have expired if credentials and credentials.expired and credentials.refresh_token: credentials.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( CREDENTIALS_FILE, SCOPES) credentials = flow.run_console() # Save the credentials for the next run with open('token.pickle', 'wb') as token: pickle.dump(credentials, token) return build(API_SERVICE_NAME, API_VERSION, credentials = credentials) def translate(service, phrase): sheet = service.spreadsheets() spreadsheet = { 'properties': { 'title': 'translator' } } print('Creating sheet...') spreadsheet = sheet.create(body=spreadsheet, fields='spreadsheetId').execute() # Save the spreadsheet id spreadsheet_id = spreadsheet.get('spreadsheetId') values = [ [ phrase, '=GOOGLETRANSLATE(A2, "en", "fr")' ], ] body = { 'values': values } print('Translating phrase...') result = service.spreadsheets().values().update( spreadsheetId=spreadsheet_id, range='A2:B2', valueInputOption='USER_ENTERED', body=body).execute() print('Retrieving translation...') result = service.spreadsheets().values().get( spreadsheetId=spreadsheet_id, range='B2').execute() rows = result.get('values', []) print('Original: {}'.format(phrase)) print('Translation: {}'.format(rows[0][0])) print('Done') if __name__ == '__main__': # When running locally, disable OAuthlib's HTTPs verification. When # running in production *do not* leave this option enabled. os.environ['OAUTHLIB_INSECURE_TRANSPORT'] = '1' service = get_authenticated_service() translate(service, 'Hello') |
Software Engineer & Dancer. Or is it the other way around? 🙂