Reading and writing to Google Spreadsheets using Python

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

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

    5. Create credentials
  5. Select Compute Engine service default, JSON, hit create.
  6. Open up the JSON file, share your spreadsheet with the "XXX-compute@developer.gserviceaccount.com" email listed.
  7. 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:


py_gsheet_output