Connect Google Sheets with Python

Reading and writing CSVs in Python can be exhausting. At the end of the project, you may have 20 CSVs in your repository. I highly recommend for anyone starting to use Python for marketing to learn how to connect Google Sheets with Python

With Python, you can process your Google Sheets in more complex ways, create robust visualizations, and use them to program your scripts.

Let’s get started on connecting Google Sheets with Python.

Create a Google Cloud Console

The first step is to create a Google Cloud Console. I will do a brief intro in here, but I recommend you go to this article to see the full explanation of setting up Google Cloud Console.

Next, you want to click “Get started for free” and Sign in with your Google account.

You want to agree to the Terms of Service and enter a credit card. Though the Custom Search API does have limits, Google states they will not charge you unless you give permission. Additionally, they will give you a $300 credit for free.

Enable the Sheets API

Once you access the Cloud Console, search for “Google Sheets”.

Click Enable API and you can move on to our next step of requesting an API Key.

You will then see an API Key and a Client Secret JSON. You want to copy that API Key and insert it into your Python file called “api_key”. You want to then download the Client Secret as JSON.

Find it in your Downloads and then insert it into your PyCharm project with the name “client.json”.

Import Google Sheet Python Libraries

At the top of any Python file, you want to import your libraries. If you’re not accustomed to using Python, libraries are modules of code that are open source and usable for your projects. It’s like borrowing code from Python developers that know all the crazy nooks and crannies of Python.

The libraries we need for connecting to Google Sheets are gspread and oauth2client. These are two Google libraries. GSpread is the library that will allow you to connect to Google Sheets. Oauth2client allows you to hook into a Google account with a user’s permission.

Before you import these libraries into your file, you will need to install them using PIP. PIP is a Python installation library. You can learn how to install PIP in this article on how to setup Python.

Once you have PIP installed on your computer, you want to open up your terminal and type

pip install gspread oauth2client

This should install the two libraries onto your computer so you can import them into the top of your file.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

Read a Google Sheets

In order to read a sheet from Google Sheets, you will need to first take a look at your “client.json”. 

In your “client.json”, you will find a “client-email”. This is important as you will need to share your Google sheets with that email when you want to read that Google sheet. I’d recommend sharing your entire drive to the email so you don’t have to add it constantly. 

We will be making a new function called “read_google_sheet”. We will pass into it the two parameters “sheet_name” and “sheet_index”. The goal of this function is to accomplish the following:

1. Gain access to the Google Drive through OAuth.
2. Create a Google Sheets Client object
3. Open the Sheet by Name
4. Get the worksheet by Sheet Index
5. Get all records as JSON
6. Normalize the JSON as a DataFrame
7. Return the DataFrame

def read_google_sheet(sheet_name, sheet_index):
    # use creds to create a client to interact with the Google Drive API
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)

    client = gspread.authorize(creds)

    # Find a workbook by name and open the first sheet
    # Make sure you use the right name here.
    sheet = client.open(sheet_name).get_worksheet(sheet_index)

    # Extract and print all of the values
    list_of_hashes = sheet.get_all_records()

    df = json_normalize(list_of_hashes)
    return df
print(read_google_sheet("Your Sheet Name", 0))

Write to a Google Sheets

Now that you have read the Google Sheet, you may want to reformat the data and then write it to a new file. You can do that by creating a “Write to Google Sheet” function. 

We will define a new function called “write_to_google_sheet” that takes in two parameters: a dataframe and a title for the sheet.

The goals are to:

1. Gain access to the Google Drive
2. Create a Google Sheet Client
3. Create a new sheet with the Title passed in
4. Turn the dataframe to a CSV using the “to_csv” function
5. Import the CSV using the “import_csv” function
6. Share the sheet with your real email account as an owner.

def write_to_google_sheet(dataframe, title):
    # use creds to create a client to interact with the Google Drive API
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('parsers/import_parser/client_secret.json', scope)

    client = gspread.authorize(creds)

    # Find a workbook by name and open the first sheet
    # Make sure you use the right name here.


    spreadsheet = client.create(title=title)
    client.import_csv(spreadsheet.id, dataframe.to_csv(index=False))
    spreadsheet.share("kevin@scriptsformarketers.com", perm_type='user', role='writer')

You now have all the tools to connect Google Sheets to your Python file!