Dan Petrow’s Blog

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.

Starting Over

Starting Over

Starting over the blog, this time with purpose.

When I made this website I wanted to own the Google search results for my name. I am happy to say that I accomplished my goal of owning my search results.

Recently, I have considered why I have this blog other than to own my search results. I think my main reason for keeping this blog is to put myself out there online.

Why would I want to put myself out there online? When my name gets searched, I want returned an insightful piece about me. I hope the result reveals what I do, what I’m up to.

If people searching me know what I do and what I’m up to they could possibly help. I could possibly make a connection I wouldn’t have otherwise. I might even be able to use my experience to help them.

The reason for this post is really clarifying for myself why I am writing this. Now that I have asked myself why five times I feel pretty strong that I should be sharing updates of what I am currently doing and what obstacles I’m facing. These updates will definitely be a form of goal setting and used for accountability.

I think that I should also be sharing pictures, videos and text about who I am and generally what I am doing that is interesting in my life. This all sounds very obvious but I think it is helpful.

Now that I have clarified my goals for posting I can pretty easily template my future post. Generally, my posts should fall into one of the following categories; First, what am I about? Second, what am I doing today? Third, what am I doing this week.

What am I about?

Here is an introduction to my life for those not familiar, I am a married guy who has been travelling with my wife Jen for Air Force Pilot training. I have been keeping busy as a stay at home husband for the last year because of our travelling but I’m not cut out for that lifestyle.

Impatiently, I’m waiting to end our scheduled travels and put some roots down in Manchester, Connecticut. I will begin work at Turtle Ledge Farms, Hampton, Connecticut within the next week or two and I will also begin looking for a second job to bring in extra money for the house Jen and I are in the process of buying.

What am I up to today?

Today, I haven’t got a lot accomplished. Jen woke up late to work and rushed out with the car so didn’t make it to the laundromat. However, the laundry will be dropped off when I get back to the bus. Then I will hit the gym where I will run for an hour. I will enjoy riding the motorcycle home.

What am I up to this week?

I am going to say goodbye to Arkansas. I’ve had a pretty good time in Arkansas over the last seven months but I’m ready to go. I’ll be stopping back in Illinois for my little brother’s birthday. Then I’m buying a new vehicle. After that I’ll be shoving off for Hampton, Connecticut. Once I get there I will be setting up camp. Finally, I’ll start work at the farm and look for a second job near Manchester.

To you reading this, if know someone hiring near Manchester, let me know how to apply!