Reading and writing to Google Spreadsheets using Python


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

Connecting Python to Google Sheets

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


Copyright © Erik Rood 2017