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. Select Compute Engine service default, JSON, hit create.
6. Open up the JSON file, share your spreadsheet with the "[email protected]" 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))