How to manipulate Google spreadsheets using Python

In this article, I briefly explain how you can easily manipulate your Google spreadsheets using Python.

Contents

- Step 1: Create a Google Service Account
     - 1.1 Create a new project on Google Cloud Platform
     - 1.2 Enable the required APIs
     - 1.3 Manage Google Sheets API credentials
- Step 2: Share your Google Sheet with your Service Account
- Step 3: Manipulate your Google Sheet with Python

Step 1: Create a Google Service Account

First, we will have to create a Service Account. This account will be used to make authorized API calls to Google Cloud Services. It is important to mention that to create a Service Account it is required to have a Google account first.

1.1 Create a new project on Google Cloud Platform

1. Go to the developer’s console and click on “Select a project”.

2. Click on “New project” to create a new project

3. Fill in the required fields and then click on “ To Create”.

1.2 Enable the required APIs

Now that the new project is created, we need to enable two APIs:

• Google Drive API
• Google Sheets API

To do this, on the top bar of the console, where “Find Products and Resources” (or “Search products and resources”) is displayed, type “Google Drive API” and select the first option.

After that, click on “Enable” to enable this API on your project.

Now do the same with Google Sheets API:

1.3 Manage Google Sheets API credentials

Now that both APIs are enabled, we will add credentials for the Google Sheets API:

1. Search again “Google Sheets API” on the top bar and click on “Manage”.

2. Go to “Credentials” on the left menu and then click on “Manage service accounts”.

Let’s create the Service Account:

Select a name and then click on “Ready”

You will see something like this:

Go to Actions and then “Manage Keys”. Add a new key by clicking on “Add Key”

Select JSON and click on “To Create” or “Create”.

A file will be downloaded on your computer.

Step 2: Share your Google Sheet with your Service Account

Next step is to copy the Service Account ID (In my case myprojectserviceaccount@myproject-336515.iam.gserviceaccount.com)

Open a new Google Sheet and share your sheet with this account

Step 3: Manipulate your Google Sheet with Python

To manipulate the Google Sheet with Python we will use gspread, a Python API for Google Sheets.

Create a new project and install gspread:

pip install gspread

Locate the credentials previously downloaded in step 1.3 and then create the folling script “my_script.py” to manipulate your Sheet with Python.

# import the library 
import gspread

# import Google Sheets API credentials 
service_account = gspread.service_account(filename="path/to/credentials")

# open a spreadsheet by its title (in this case "My New Project")
sheet = service_account.open("My New Project")

# define the worksheet by its name (in this case "Sheet 1")
wks = sheet.worksheet("Sheet1")

# manipulate the worksheet
wks.update("A1", "Hello!")

Once you run this script

python my_script.py

you will see that cell A1 has been updated to read “Hello!”

That’s it! Now everything is set up so you can manipulate your spreadsheets using Python.

Check the gspread documentation to see what else you can do with this API.
This documentation is well written and contains many examples that you may find useful in your projects.

I hope you find this article useful. If you have any queries you can find me on LinkedIn.

Laura Calcagni
Software Data Engineer