Connect your Jupyter Notebook to Google Spread Sheet


This is Jing, a data analyst with great passion for data science and big data technology. As a data analyst, working with Excel or Google Spread Sheet is sometimes inevitable, considering the reality that a lot your colleagues are more comfortable with seeing data or results in Excel or Google Spread Sheet. This blog gonna to show you how to connect your Jupyter Notebook to Google Spread Sheet to make your workflow more automatic.


Before we start, I assume you already have Jupyter Notebook installed on you computer. 

Let’s begin!

Step 0 : Create a project and Enable Google Drive API

Create a project for your application in Google Cloud Console if you do not have one. In this blog, I created a project called “user deletion”, which I used in another blog Get Google Analytics data to your Jupyter Notebook by using Analytics Reporting API

Go to APIs & Service -> Library, find the Google Drive API and enable it.

Step 1 : Create a Service Account for your Jupyter Notebook

Go to APIs & Services -> Credentials -> Create credentials -> Service account key -> New service account, named it as “Google_Sheet” -> Select role as “Service Account User” for this account -> select “JSON”  -> click on “Create”. Download it, rename it as “service_account_GS.json” and put it in a folder you are comfortable with.

Step 2 : Create a Google Sheet and share it with Service Account E-mail

Open the “service_account_GS.json” you’ve just gotten. Find your “client_email”

Share the Google Sheet you just created with this “client_email”

Get the Sheet ID from the URL to the sheet

Step 3: Install the libraries

Open your terminal, type in the following command

pip install gspread oauth2client df2gspread

Step 4: Open your Jupyter Notebook

See the Notebook template here

Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.

Now you should see the Google Sheet is updated with the data you uploaded!

Congratulations! We are done here!

Now you should have successfully connect your Jupyter Notebook to a Google Sheet! Find more information about Google Spread Sheet at df2gspread

This is not something amazing, but hopefully will make your work a little bit easier! This is Jing, thanks for reading my blog! Feel free to commend and ask questions! I will get back to you as much as possible.

4 thoughts on “Connect your Jupyter Notebook to Google Spread Sheet

  1. Thanks for sharing!

    Can you please elaborate on the name of the file that you are using to load the data? I am asking this command line:

    df = pd.read_csv(‘client_ids.csv’,index_col = False)

    I seems that the aim here is to load a Google Spreadsheet data into Jupyter notebook. Should the name be our google spreadsheet name? or a link connecting it to the spreadsheet?


  2. is there any other way to create credentials when i don’t have permission to?
    message: “You don’t have permission to create credentials
    Required permission(s): One of serviceusage.apiKeys.create, clientauthconfig.clients.create, and iam.serviceAccountKeys.create”


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s