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.to_csv(make_filename="sheet1.csv") s.sheets.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.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