The easiest way to 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.

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