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 pygsheetsimport pandas as pd#authorizationgc = pygsheets.authorize(service_file='/Users/erikrood/desktop/QS_Model/creds.json')# Create empty dataframedf = pd.DataFrame()# Create a columndf['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:
