Predictive Hacks

The easiest way to connect python with google sheets

connect python with google sheets

The best way to do it is by using the library gsheets but first, we need some preparation.

Let’s install the library using pip

pip install gsheets

Then you have to log in to Google Developers Console. Create (or select) a project and enable the Drive API and Sheets API under Google Apps APIs. Go to the Credentials for your project and create New credentials > OAuth client ID > of type Desktop app. You now can download the client secret by pressing the download button. Save it to your working directory and rename it as client_secrets.json.

google sheets with python

Connect python with google sheets

Now we can work with python.

import pandas as pd
import numpy as np
from gsheets import Sheets

sheets = Sheets.from_files('client_secrets.json', '~/storage.json')

url = 'url_of_google_sheet'
s = sheets.get(url)

If you run it for the first time, it will open the browser and ask you to log in with your Google account to authorize this client read access to all its Google Drive files and Google Sheets.

For our example, we are trying to connect a google sheet containing 2 tabs. You can get the data from it by saving each tab to a CSV file and then read them or you can get directly the values with pandas DataFrames.

#the first method is by saving the data into CSV files
#the number here represents the first and the second tab of the sheet
s.sheets[0].to_csv(make_filename="sheet1.csv")
s.sheets[1].to_csv(make_filename="sheet2.csv")

pd.read_csv('sheet1.csv')

#the second method is by reeading the values directly with pandas 
pd.DataFrame(s.sheets[0].values())

     A  B
0    1  a
1    2  b
2    3  c
3    4  a
4    5  b
5    6  c
6    7  a
7    8  b
8    9  c
9   10  a
10  11  b
11  12  c
12  13  a
13  14  b
14  15  c
15  16  a
16  17  b
17  18  c
18  19  a

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Leave a Comment

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore

data science journey
Miscellaneous

My Journey as a Data Science Blogger

Μy Background My Studies Back in 2001, I entered university to study Statistics. During my first year, I ran my