(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.
(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:
We send 3 questions each week to thousands of data scientists and analysts preparing for interviews or just keeping their skills sharp. You can sign up to receive the questions for free on our home page.