Reading Google Spreadsheets in Python

I wanted to read a Google Spreadsheet with Python, using the Google Data APIs. Google Spreadsheets are a quick and dirty way for non-technical users to maintain a table of data inputs for a program or system. For example, I use Google Spreadsheets to drive our Christmas card list and typeset envelopes. See the full code on Github: [1]

I was unable to find a concise code example, so I wrote one up.

Preliminaries, configure these for your needs:

username        = 'somebody@gmail.com'
passwd          = 'pa$$word'
doc_name        = 'My spreadsheet'

This assumes you have the Google Data API Python library installed on your system (from here).

import gdata.docs
import gdata.docs.service
import gdata.spreadsheet.service
import re, os

# Connect to Google
gd_client = gdata.spreadsheet.service.SpreadsheetsService()
gd_client.email = username
gd_client.password = passwd
gd_client.source = 'payne.org-example-1'
gd_client.ProgrammaticLogin()

Now that we're connected, we query the spreadsheet by name, and extract the unique spreadsheet and worksheet IDs.

q = gdata.spreadsheet.service.DocumentQuery()
q['title'] = doc_name
q['title-exact'] = 'true'
feed = gd_client.GetSpreadsheetsFeed(query=q)
spreadsheet_id = feed.entry[0].id.text.rsplit('/',1)[1]
feed = gd_client.GetWorksheetsFeed(spreadsheet_id)
worksheet_id = feed.entry[0].id.text.rsplit('/',1)[1]

rows = gd_client.GetListFeed(spreadsheet_id, worksheet_id).entry

At this point, you have a row iterator which will yield rows for the spreadsheet. This example will print everything out, keyed by column names:

for row in rows:
    for key in row.custom:
        print " %s: %s" % (key, row.custom[key].text)
    print