Google Spreadsheets API

A simple Python wrapper for the Google Spreadsheeta API.

Features

An object oriented interface for Worksheets
Supports List Feed view of spreadsheet rows, represented as dictionaries
Compatible with Google App Engine
Supports oauth

Requirements

Before you get started, make sure you have:

virtualenv venv
source venv/bin/activate
pip install -e git+git@github.com:jbruce12000/python-google-spreadsheet.git#egg=google_spreadsheet
pip install -r venv/src/google-spreadsheet/requirements.txt (that will install gdata, httplib2 and oauth2client)
setup oauth2 for your app https://console.developers.google.com/apis/library
- pick web app
- set the redirect uri to http://localhost:8080/
- grab the client_id and client_secret
put the client_id and client_secret in a local json formatted file named ./client_secrets.json like this...

{ "web": { "client_id": "XXXXXXXXX", "client_secret": "XXXXXXXX", "redirect_uris": ["http://localhost"], "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://accounts.google.com/o/oauth2/token" } }
the spec is here... https://developers.google.com/api-client-library/python/guide/aaa_client_secrets if you care
the first time you run this, it will open a browser and have you verify it is ok for this app to access your google account. after that, it'll be cached in a local file named ./creds.dat.

Usage

List Spreadsheets and Worksheets:

>>> from google_spreadsheet.api import SpreadsheetAPI
>>> api = SpreadsheetAPI()
>>> spreadsheets = api.list_spreadsheets()
>>> spreadsheets
[('MyFirstSpreadsheet', 'tkZQWzwHEjKTWFFCAgw'), ('MySecondSpreadsheet', 't5I-ZPGdXjTrjMefHcg'), 
('MyThirdSpreadsheet', 't0heCWhzCmm9Y-GTTM_Q')]
>>> worksheets = api.list_worksheets(spreadsheets[0][1])
>>> worksheets
[('MyFirstWorksheet', 'od7'), ('MySecondWorksheet', 'od6'), ('MyThirdWorksheet', 'od4')]

Please note that in order to work with a Google Spreadsheet it must be accessible to the user who's login credentials are provided. The GOOGLE_SPREADSHEET_SOURCE argument is used by Google to identify your application and track API calls.

Working with a Worksheet:

>>> from google_spreadsheet.api import SpreadsheetAPI
>>> api = SpreadsheetAPI()
>>> sheet = api.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> rows = sheet.get_rows()
>>> len(rows)
18
>>> row_to_update = rows[0]
>>> row_to_update['name'] = 'New Name'
>>> sheet.update_row(row_to_update)
{'name': 'New Name'...}
>>> row_to_insert = rows[0]
>>> row_to_insert['name'] = 'Another Name'
>>> row = sheet.insert_row(row_to_insert)
{'name': 'Another Name'...}
>>> sheet.delete_row(row)
>>> sheet.delete_all_rows()

Advanced Queries:

>>> from google_spreadsheet.api import SpreadsheetAPI
>>> api = SpreadsheetAPI()
>>> sheet = api.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> rows = sheet.get_rows(query='name = "Joe" and height < 175')

Or filter in memory:

>>> sheet = api.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> filtered_rows = sheet.get_rows(
        filter_func=lambda row: row['status'] == "READY")

Sort:

>>> sheet = api.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> rows = sheet.get_rows(order_by='column:age', reverse='true')

Batch Update:

This is experimental. This takes starting x,y (upper left) and
ending x,y (lower right) coordinates of a box in the spreadsheet
and fills it with data in the given list of lists.  cells in the
box, but not in the data are blanked out.  This operation is fast
and is much better than deleting rows and adding rows.

This example would populate two rows with 3 populated columns.
All cells in the 4th column and all cells in rows 3 to 10 will
be blanked out because there is no data for them.

>>> sheet = api.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> sheet.batch((2,1),(10,4),[['row1','a','1'],['row2','b','2']])

That's it.

For more information about these calls, please consult the Google Spreadsheets API Developer Guide.

Tests

To run the test suite please follow these steps:

Make sure Nose is installed: (pip install nose)
Create a local file named: test_settings.py with the following variables set to the relevant values: GOOGLE_SPREADSHEET_USER, GOOGLE_SPREADSHEET_PASSWORD, GOOGLE_SPREADSHEET_SOURCE, GOOGLE_SPREADSHEET_KEY, GOOGLE_WORKSHEET_KEY, COLUMN_NAME, COLUMN_UNIQUE_VALUE
Run nosetests

License