Reading and writing to Google Spreadsheets using Python

July 2019

(1) Google Drive API, credentials

In order to follow along, you're going to need a spreadsheet. Head over to Google Sheets create one. Put a few dummy columns in there so you can programatically access it later.

Next, you'll need to create a service account and OAuth2 credentials from the Google API Console. Follow the steps below to enable the API and grab your credentials.

Head over to the Google API Console.
Create a new project by selecting My Project -> + button
SearchInput for 'Google Drive API', enable it.
Head over to 'Credentials' (sidebar), click 'Create Credentials' -> 'Service Account Key'

Select Compute Engine service default, JSON, hit create.
Open up the JSON file, share your spreadsheet with the "XXX-compute@developer.gserviceaccount.com" email listed.
Save the JSON file wherever you're hosting your project, you'll need to load it in through Python later.

(2) Connecting Python to Google Sheets, writing a dataframe

First, you'll need to install pygsheets, which allows us to actually read/write to the sheet through Python. Once that's installed, you're all set. Here's an example of importing the credentials and writing some dummy data to the sheet using a Pandas dataframe:

import pygsheets
import pandas as pd
#authorization
gc = pygsheets.authorize(service_file='/Users/erikrood/desktop/QS_Model/creds.json')

# Create empty dataframe
df = pd.DataFrame()

# Create a column
df['name'] = ['John', 'Steve', 'Sarah']

#open the google spreadsheet (where 'PY to Gsheet Test' is the name of my sheet)
sh = gc.open('PY to Gsheet Test')

#select the first sheet 
wks = sh[0]

#update the first sheet with df, starting at cell B2. 
wks.set_dataframe(df,(1,1))

(3) Done - Check your sheet for the data we pushed in the above step

Voila! The spreadsheet you created should now be populated with your dataframe: