A Command Line Interface to Google Spreadsheets

by Brian K. Jones

A few people who read my blog are already aware that I was working on a little pet project to develop a command line interface to a Google Spreadsheets document that is being used by some of the administrative operations team at Python Magazine. The long and short of it is that I finally had about two hours to work on it yesterday, and got my little utility to the point where it's actually useful to me: I can now add rows to the spreadsheet without opening a browser.

What's even better is that it's not specific to any particular spreadsheet. You can use it for any Google Spreadsheet you want. This code is 99% cut-n-pasted from Google's examples and docs. The rest is the result of a bit of poking and prodding to figure out how to get at data elements that aren't documented and I couldn't find examples for. So I wanted to post it here so anyone could have it. It requires you to install the Google Data Python Client.

Save this to a file, execute it, and it'll prompt you for your gmail account credentials, then show you all of the spreadsheets you have access to. Pick one, then pick a worksheet within that spreadsheet, and it'll dump the contents, or (if you use the -a option) it'll prompt you to fill in values for each column of a new row.

This is quick and dirty code, not production quality, and I probably do some un-Pythonic things in there somewhere, but a buddy told me he was having some issues with Google's documentation too, so I figured that making more code available for people to read might help.


#!/usr/bin/env python

from xml.etree import ElementTree
except ImportError:
from elementtree import ElementTree
import gdata.spreadsheet.service
import gdata.service
import atom.service
import gdata.spreadsheet
import atom
import getpass
import string
from optparse import OptionParser

parser = OptionParser()
parser.add_option("-a", "--addrow", action="store_true", dest="addrow", default=False)
(options, args) = parser.parse_args()

gd_client = gdata.spreadsheet.service.SpreadsheetsService()
gd_client.email = raw_input('\nEmail: ')
gd_client.password = getpass.getpass()
gd_client.source = 'pymag-test-1'

def PromptForSpreadsheet(gd_client):
# Get the list of spreadsheets
feed = gd_client.GetSpreadsheetsFeed()
input = raw_input('\nSelection: ')
return feed.entry[string.atoi(input)].id.text.rsplit('/', 1)[1]

def PrintFeed(feed):
for i, entry in enumerate(feed.entry):
if isinstance(feed, gdata.spreadsheet.SpreadsheetsCellsFeed):
print '%s %s\n' % (entry.title.text, entry.content.text)
elif isinstance(feed, gdata.spreadsheet.SpreadsheetsListFeed):
print '%s %s %s\n' % (i, entry.title.text, entry.content.text)
print '%s %s\n' % (i, entry.title.text)

def PromptForWorksheet(gd_client, key):
# Get the list of worksheets
feed = gd_client.GetWorksheetsFeed(key)
input = raw_input('\nSelection: ')
return feed.entry[string.atoi(input)].id.text.rsplit('/', 1)[1]

def ListGetAction(gd_client, key, wksht_id):
# Get the list feed
feed = gd_client.GetListFeed(key, wksht_id)
return feed

def AddRow(columnfeed, spreadsheet, worksheet):
# take the columnfeed.entry object and prompt for a value for each column
# Build a dict from the resulting column:value pairs.
dict = {}
for key in columnfeed.entry[0].custom.keys():
dict[key] = raw_input("%s: " % key)
gd_client.InsertRow(dict, spreadsheet, worksheet)

spreadsheet_id = PromptForSpreadsheet(gd_client)
worksheet_id = PromptForWorksheet(gd_client, spreadsheet_id)
columnfeed = ListGetAction(gd_client, spreadsheet_id, worksheet_id)
if options.addrow:
AddRow(columnfeed, spreadsheet_id, worksheet_id)
for attr, val in enumerate(columnfeed.entry):
for key in val.custom.keys():
print "%s: %s" % (key, val.custom[key].text)
print "\n"


2008-08-03 08:33:45

the backslashes are lost. For newbies may turn out strange.