Connecting Python to Google Sheets

Connect to Google Sheets from Python with easy steps(a visual guide).

Connecting Python to Google Sheets

Google Sheets is a free web-based spreadsheet application provided by Google that allows users to create, manage and format spreadsheets online. It also allows users to collaborate with other users.

Google Sheets

Why connect Python to Google Sheets?

As the volume of data increases, Python proves much more powerful and practical to perform data analysis and machine learning on your Google sheet data to provide actionable intelligence. It does that through the help of powerful libraries and huge online support group.

It is easy to setup and perform data analysis in Google Sheets. However, as the volume of data increases, you want more computational flexibility and power.

Today, Python is the go-to programming language for data analysis and machine learning, thanks to its third party libraries like Pandas, Numpy, Scikit learn and Matplotlib. The presence of an active online support group also helps.

It is easier to draw the power of those libraries and build actionable business intelligence around your data in Python.


How to connect Python to Google Sheets ?

You can connect Python to Google Sheets by creating a service account in Google Cloud Console which allows you to make authorized API calls to the Google Sheets API. Follow the steps below.

1. Create a new project in Google Cloud Console

Log in to Google Cloud Console in your browser. Click on the Menu > IAM & Admin > Create a Project.

Google Cloud Console

Provide a Project name and click Create.

New project in Google Cloud Console

2. Enable Google drive API

Click on Menu > APIs & Services > Enabled APIs & Services.

Google Cloud APIs & Services

Click on + Enable APIS AND SERVICES button in the top middle of the page.

Enable an API in Google Cloud

Search for the Google Drive API and click on it.

Google Drive API

Enable the Google Drive API.

Enable the Google Drive API

Search for the Google Sheets API and enable it.

Screenshot from 2022-06-02 17-58-04.png

Once you enable Google Drive and Google Sheets API, you will be redirected to its page. To start using this API, you have to create credentials.

3. Create a Service account

Click on Create Credentials on the Google Drive API page.

Create credentials for Google Drive API

On the Create Credentials page, fill in the necessary details and click on Done. You will be directed to Service accounts page.

Credentials Page Google Cloud Console

Provide a Service account name and description. Click on CREATE AND CONTINUE.

Service Account page in Google Cloud

You will receive an email on the screen. Copy that email for later use.

Service account email for Google Drive API

On the same page, click on Keys tab. Add Key > Create a new key. Click on JSON to create a private key.

Private key in JSON for Google Drive access

Once the private key is downloaded, rename it to credentials.json for use later to perform OAuth2 authentication with Google APIs.

4. Share the Google Sheet with client email

Create a google sheet to experiment with. Here I have created a To do list.

My to do list in Google Sheets

Click on Share button and share the spreadsheet with the client email you have saved from the previous section.

lambda(11).png


Reading and writing to Google Sheet with Python

You can use the gspread third party library to interact with the sheet.

You have to install the necessary libraries.

pip install gspread
pip install --upgrade google-api-python-client oauth2client

Create a new Python file. Import the following libraries into the file.

import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials

Copy the credentials.json into the same directory as your file and perform authorization.

# defining the scope of the application
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] 

#credentials to the account
cred = ServiceAccountCredentials.from_json_keyfile_name('credentials.json',scope) 

# authorize the clientsheet 
client = gspread.authorize(cred)

Read the data from the spreadsheet using gspread get_all_values() method.

# Provide the Google Sheet Id
gs1 = client.open_by_key('18ZG9iEJN4c2SRdhWxo3o05ch6_TF4r2-7joAGtOeKG0')
ws1 = gs1.sheet1
print(ws1.get_all_values())

You should get the all the rows from the sheets.

Output

[['\n To Do', '', '0/3 completed  '], ['', '', ''], ['✓', 'Date', 'Task'], ['FALSE', '2/6', 'Finish blog on connecting Python to Google Sheets'], ['FALSE', '2/7', 'Go book shopping'], ['FALSE', '2/8', 'Conduct meeting with Jesus']]

You can also write to the Google sheet. For example - The below code create a new worksheet.

# create a new spreadsheet
new_ws = gs1.add_worksheet(title="Trial Worksheet", rows=10, cols=20)

Creating new sheet with gspread in Python

I hope this article provides a clear step-by-step guide to connect to Google Sheets from Python.
In the next article, we will leverage the power of Python and perform some data analysis on the spreadsheet data.

Subscribe to Pylenin

Don’t miss out on the latest issues. Sign up now to get access to the library of members-only issues.
jamie@example.com
Subscribe