Python to Google Sheets (2024)

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