Reading google sheets Into a Pandas Dataframe with gspread and OAuth2

Do you know if you can read a google sheet into pandas? I was asked this by a colleague a few days ago. I remember about a year ago I had worked with a python library called gspread, and I remember the api being quite straight forward. Since it had been a while, I did a quick google search on the subject. There were a lot of good tutorials on this topic but to my surprise none of them used gspread. Since using gspread is relatively simple, I wrote this tutorial in the hopes that it may be useful to someone who wants to work with google sheets but doesn’t want to trudge through a lot of documentation.

Start by installing gspread:

pip install gspread

Step 1: Credentials

Setting up the credentials might be the most time-consuming part of the process. It can also get confusing.

First, navigate to the Google developers console. You can select ‘create project’ or select an already existing one:

I’ll create a new project called “ForMedium”. After you hit the create button, refresh your browser and it will appear in the console pictured above.

Under APIs & Services select “Credentials”:

Select Create credentials and then “Service account key” from the dropdown menu:

Select project > owner under the “Role” dropdown menu and name your project whatever you’d like. I called mine “mediumtut”

Make sure you’ve selected JSON as the file format and then hit “Create”:

This should promptly download a json file. Move this json file into your working directory. At this point make sure you’ve installed Oauth2 and PyOpenssl. You can find these steps specified on this page of the gspread documentation.

pip install --upgrade oauth2client

and

pip install PyOpenSSL

Step 2: Share your google sheet

Inside the json file you just downloaded, there will be a key call ‘client_email’. You must share the google sheet you want to work with to the email address specified by ‘client_email’.

Step 3: Reading the sheet into a dataframe

Now that we have set up our credentials and shared our sheet with the client email, we are good to go. Here is an example of what the code might look like:

“NYC subway data” is the name of the spreadsheet I chose to use for this example. You will replace it with the name of the spreadsheet you want to use. Make sure you also replace the json file with the one you downloaded in step 1. The ‘get_all_values()’ method reads in the sheet as a list of lists. That’s all the code you need to read a google sheet into a dataframe! Surprisingly simple right?

Again, this code and the instructions are very similar to what can be found on the gspread documentation. However, it seems like enough people are having problems setting this up, so I hope this article simplified things a little. If you have any questions, leave them in the comments and I will be sure to get back to you! As always, thanks so much for reading!!

Consider Donating (buymeacoffee.com)