Import Data From Google Sheets To Pandas Dataframe — The Easiest Way

Photo by Joshua J. Cotten on Unsplash

Steps:

1. Open the google sheets file.
2. Click on the ‘Share’ option and change the privacy to “Anyone with the link” so that anyone on the internet with this link can view the file.

3. Open your Python IDE. I am using the Google Colab.

4. Now import the required python library. You’ll only need pandas.

import pandas as pd

5. Go to your google sheets tab and copy the google sheet id.

How to find the google sheet id?

Let’s say the link to your file is the following:

https://docs.google.com/spreadsheets/d/1qa6yJkWMJRFG1T56_ZYGi5nm4VVofZNNHxQbveC0PIA/edit#gid=1640708940

The long string between “…/spreadsheets/d/” and “/edit#gid…” is the required google sheet id. In my case it is:

1qa6yJkWMJRFG1T56_ZYGi5nm4VVofZNNHxQbveC0PIA

6. Use the following code. Paste your google sheet id as a string in gsheetid and your sheet’s name in sheet_name. In my case, the data is in Sheet1.

gsheetid = “1qa6yJkWMJRFG1T56_ZYGi5nm4VVofZNNHxQbveC0PIA”
sheet_name = “Sheet1”
gsheet_url = f”https://docs.google.com/spreadsheets/d/{gsheetid}/gviz/tq?tqx=out:csv&sheet={sheet_name}"
df = pd.read_csv(gsheet_url)
df.head()

This code gives me the following output:

Done!