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.

Head over to the Google API Console. Create a new project by selecting My Project -> + button Search 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 ))