Turn on the API, download an OAuth client ID as JSON file, and create a
Sheets object from it. Use its index access (
__getitem__) to retrieve
SpreadSheet objects by their id, or use
.get() with a sheet URL.
Iterate over the
Sheets object for all spreadsheets, or fetch spreadsheets
by title with the
SpreadSheet objects are collections of WorkSheets, which provide access to the
cell values via spreadsheet coordinates/slices (e.g.
zero-based cell position (e.g.
Save WorkSheets (or all from a SpreadSheet) as CSV files with the
pandas.DataFrames from worksheet with the
This package runs under Python 2.7, and 3.5+, use pip to install:
$ pip install gsheets
Log into the Google Developers Console with the Google account whose spreadsheets you want to access. 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 Other. In the list of your OAuth 2.0 client
IDs click Download JSON for the Client ID you just created. Save the
client_secrets.json in your home directory (user directory).
Another file, named
storage.json in this example, will be created after
successful authorization to cache OAuth data.
On you first usage of
gsheets with this file (holding the client secrets),
your webbrowser will be opened, asking you to log in with your Google account
to authorize this client read access to all its Google Drive files and Google
Create a sheets object:
>>> from gsheets import Sheets >>> sheets = Sheets.from_files('~/client_secrets.json', '~/storage.json') >>> sheets <gsheets.api.Sheets object at 0x...>
Fetch a spreadsheet by id or url:
# id only >>> sheets['1dR13B3Wi_KJGUJQ0BZa2frLAVxhZnbz0hpwCcWSvb20'] <SpreadSheet 1dR13...20 u'Spam'> # id or url >>> url = 'https://docs.google.com/spreadsheets/d/1dR13B3Wi_KJGUJQ0BZa2frLAVxhZnbz0hpwCcWSvb20' >>> s = sheets.get(url) >>> s <SpreadSheet 1dR13...20 u'Spam'>
Access worksheets and their values:
# first worksheet with title >>> s.find('Tabellenblatt2') <WorkSheet 1747240182 u'Tabellenblatt2' (10x2)> # worksheet by position, cell value by index >>> s.sheets['A1'] u'spam' # worksheet by id, cell value by position >>> s.at(row=1, col=1) 1
Dump a worksheet to a CSV file:
>>> s.sheets.to_csv('Spam.csv', encoding='utf-8', dialect='excel')
Dump all worksheet to a CSV file (deriving filenames from spreadsheet and worksheet title):
>>> csv_name = lambda infos: '%(title)s - %(sheet)s.csv' % infos >>> s.to_csv(make_filename=csv_name)
Load the worksheet data into a pandas DataFrame (requires
>>> s.find('Tabellenblatt2').to_frame(index_col='spam') eggs spam spam eggs ...
WorkSheet.to_frame() passes its kwargs on to
gsheets.py – self-containd script to dump all worksheets of a Google Spreadsheet to CSV or convert any subsheet to a pandas DataFrame (Python 2 prototype for this library)
gspread-pandas – Interact with Google Spreadsheet through Pandas DataFrames