Connecting Python to Google Sheets

Connecting Python to Google Sheets

A visual guide to connect with Google Sheets using the Google Cloud Console.

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.

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.

    Google Cloud Console

  3. Provide a Project name and click Create.

    New project in Google Cloud Console

Enable Google drive API

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

    Google Cloud APIs & Services

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

    Enable an API in Google Cloud

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

    Google Drive API

  4. Enable the Google Drive API.

    Enable the Google Drive API

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

Create a Service account

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

    Create credentials for Google Drive API

  2. 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

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

    Service Account page in Google Cloud

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

    Service account email for Google Drive API

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

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 Step 4 of previous section.

lambda(11).png

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)
    

    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.

Did you find this article valuable?

Support Lenin Mishra by becoming a sponsor. Any amount is appreciated!