Step by step guide to interact with Google Sheets using Python

These days I need to interact with Google Sheets, going to modify values, based on data taken from an external source (a DB) and on existing values in the target sheet.

There are services available to do this kind of operations (if something happens in this application, then do this operation on another) but if you need particular custom logic, both in the retrieval of input data and in writing, you have to implement it.

It is possible to use specific ETL tools but, to keep everything simple and if availability is not crucial, even a simple middleware app can be effective.

I have to say that, looking through Google’s documentation on Sheets integration, I found it rather confusing.

For this reason, I invoked Python’s superpowers for help and the answer was: gspread, a Python API for Google Sheets.

Let’s see now step by step, how to make it all work.

Google Configuration

Before starting to code, is necessary to setup projects and access on Google Developer Console. To do this, follow these steps:

1) Create a new project

2) Enable Google Drive and Google Sheets APIs using the options present on the project dashboard

3) Go to Credentials and create a new credential, choosing Service account key

4) You’ll need to create a service account and the relative key too. As role, if you have to edit too the sheet, you can choose “Editor” (or “Owner”), otherwise “Viewer” is ok.

Take note of the service account ID because you’ll gonna need it next step.

Then download the key as JSON, you’ll use it in the app as credential file. Let’s rename it “credentials.json”

5) Go to the sheet you want to use it and share it with the service account ID. This is the way to access it programmatically.

All set! Now let’s see how to access it with Python

Python

1) Install the needed libraries : gspred and oauth2client

pip install gspread
pip install --upgrade oauth2client

2) Now, with just a couple of lines, you can access the sheet. For simplicity, put the credential json file on the app root (and don’t forget to add it to git ignore!)

First, you define scopes of your interaction with the application (more on this here), then create the credentials, passing scopes and the credential file path.

Finally, you create an authorized client and with it, you just open the sheet (using its title) and getting the first sheet.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(credentials)
work_sheet = client.open('python-test').sheet1

That’s it…now you have at your disposition all the functions and attributes to handle the sheet in all its parts, using the worksheet instance.

You can find all the info here, in the gspread official documentation

For example, you can write functions to update a specific cell with an external value if a certain value is found in a row or… update an existing value, based on a mix of a combination of external and on sheet values. Possibilities are endless.

Enjoy!