Connecting to a Google Sheet from Python Script

It is very common when working with data sets in python to save processed data onto a local spreadsheet file. After which you will attached and send such a file to other users in some other locations. In a situation where you want the spreadsheet file to be readily available to those users as you push in process data, then you need to share a cloud hosted spreadsheet such as 'Google Sheet'.

So, in this post I will share with you how you can use python to connect to a 'Google Sheet' located in your Google Drive and push in data right from a python script running on your local machine. This post will guide you on loading data from a local python script into cloud based spreadsheet (Google Sheet).

Let's get started...

Python Google Sheet setup instructions

1)  Configuring Google account
a)  Go to: https://console.cloud.google.com/apis/dashboard and create a new project, give it a name and open it. You can use existing project if you already created one before.

Here my project is named “PySpreadSheetExample”.

b)  Open the project and enable “Google Drive API” and “Google Sheet API” by clicking on ‘Enable API and Services’ button.

You will search for “Google Drive API” and “Google Sheet API” and click on the ‘Enable’ button for each.

c)  Once you have enabled both APIs, then you need to create some credential. From you project dashboard, click on “Credentials” and select “Service Account Key”.

d)  Download the key as JSON file by entering the ‘Service account name and Role’ as seen below.

e)  Open the downloaded JSON file in a text editor and copy the service email. It should look like this: xxxx@pyspreadsheetexample.iam.gserviceaccount.com

f)  Share the Google Sheet you want to connect to with the email above.

g)  At the end remember to activate your cloud account by adding credit card details especially if you are doing this for the first time.

2) Installing python modules
Run this command to install the required python modules: pip install pandas gspread oauth2client PyOpenSSL

Open command prompt and enter the command above as seen below:-

You can use you usual libraries such pandas to manipulate the cloud based google spreadsheet. For more on "Opening a Spreadsheet, Creating a Spreadsheet, Sharing a Spreadsheet, Selecting a Worksheet, Creating a Worksheet, Deleting a Worksheet, Getting a Cell Value, Getting All Values From a Row or a Column, Getting All Values From a Worksheet as a List of Lists, Finding a Cell, Finding All Matched Cells, Cell Object, Updating Cells etc" check out the documentation on GSpread module. It is the core module that directly interacts with your Google Spreasheet.

That is it!