Day: April 14, 2019

Python And Google Sheets

Python And Google Sheets

I was nearly banging my head against the wall earlier last week trying to get Python and Google Sheets to work together. I wanted to be able to interact with Google Sheets using Python for some data manipulation and visualization. This post will hopefully be a concise explanation of what did and didn’t work for me.

First of all, the only code that worked for me I found courtesy of Erik Rood. The link is a pretty complete walk through of how I set up Python to work with Google Sheets. I’ll summarize it here and try to cater more to a beginner audience.

(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’create_credentials_screen
  5. Select Compute Engine service default, JSON, hit create.
  6. Open up the JSON file, share your spreadsheet with the “XXX-compute@developer.gserviceaccount.com” 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

#service_file="blank" change blank to match the path to the json file you downloaded from Google APIs

gc=pygsheets.authorize(service_file='C:\Code\GooglePy\creds.json')

#at this point you are set up to work with the api. Everything after this is #a mix of Pandas and pretty vanilla python.

#creates an empty dataframe
df=pd.DataFrame()

#populates your dataframe with a column.
df['name']=['John','Steve','Sarah']

#sh=gc.open('blank') replace blank with the name of the sheet that you want #to work with.

sh=gc.open('Python')

#selects the first workbook in your google sheet named Python
wks=sh[0]

#populates google sheets with the dataframe, starting at cell b2
wks.set_dataframe(df,(1,1))

#wks.get_value(a1,FORMATTED_VALUE)

#reading data with python is pretty simple too. there are a variety of calls #that you can make some include get_values, and get_row. What you need to #know about reading data is Google has a weird way of referring to cells in #a sheet. If you search A1 notation 

print(wks.get_values('A1','B2'))
print(wks.get_row(1))

(3) Done – Check your sheet for the data we pushed in the above step

Some of the code utilizes the Pandas library,. If you aren’t familiar with Pandas I found Kaggle to be a great educational resource on the topic. Alternatively, you can check out the documentation. Additionally, you might find this link to information about A1 notation useful for reading data.

Now, so far the code that you have seen really isn’t doing much but it should set you up to start writing a useful program. Thanks for reading and hopefully you are ready to roll with Python and Google Sheets. For more information on how to build an application with Google Sheets check out their API documentation.