Connecting Python to Google Sheets

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.

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.

Create a new project in Google Cloud Console

1. Log in to Google Cloud Console in your browser.

2. Click on the Menu > IAM & Admin > Create a Project.

   

3. Provide a Project name and click Create.

   

Enable Google drive API

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

   

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

   

3. Search for the Google Drive API and click on it.

   

4. Enable the Google Drive API.

   

5. Search for the Google Sheets API and enable it.

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.

Create a Service account

1. Click on Create Credentials on the Google Drive API page.

   

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

   

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

   

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

   

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

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

Share the Google Sheet with client email

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

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

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

Reading and writing to Google Sheet with Python

1. Install the necessary libraries.

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

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

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

3. 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)
   

4. 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']]
   

5. 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)
   

   

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.