Looking around the web it seemed fairly simple to work with Google Python API to update a spreadsheet. Installation is super straightforward (with Python 2.7.3 at least), just download and run installer. Using it… not so much.
Accessing and reading data from a spreadsheet seems to be fully working, but once you start to try other stuff you bump into many problems. I wanted to just create a new spreadsheet (or worksheet) and feed text data into it but had to adapt my way through to get it working. Although it’s in version 3, there are some serious limitations to it. Such as:
[ol]
[li]There’s no support yet to delete spreadsheets.[/li][li]Error messages are quite cryptic, like “500 internal server error”.[/li][li]Client.InsertRow when used only on a brand new worksheet gives a ‘400 bad request’ error, but works if the worksheet has a header row created by hand on the web UI.[/li][li]Deleteworksheet, althought it exists, always gives an ‘Internal server’ error[/li][li]InsertRow and update cell are called on the client, to update the spreadsheet online on each command, so if you are adding 2000 rows it might take a while. Making an instance of a cell or row feed, updating it locally and sending it to the server might work, but I haven’t tried it. I’m not sure there is a way to send a feed actually.[/li][li]There are many authentication methods, and although all except OAuth2 are deprecated, only OAuth1 and ClientLogin works with the old spreadsheet service framework, the only one I was able to use.[/li][li]Official documentation is scarce. Seems like there’s better support for Java and .NET API’s, while Python’s lagging behind.[/li][/ol]
Mind you, this is just a quick run to try out the API. Things could have been working better if I used OAuth2 instead of ClientLogin, but I didn’t have time right now to meddle more into this stuff. I’m not developing an app, just trying to quickly use spreadsheets with Python, so I’m not even sure if it’s worth going through all the bureocracy of registering a “client secret”. Anyway, without further ado, here’s the code (puns intended):
import gdata.spreadsheet.service
def getSpreadsheetKeys(sheetName="eventIDMap", worksheetName="Sheet1"):
'''
returns client, spreadsheet key and worksheet key from
given names with an empty worksheet
'''
# connects to the service
client = gdata.spreadsheet.service.SpreadsheetsService()
client.ssl = True
client.email = 'your@gmail.com'
client.password = 'password'
client.ProgrammaticLogin()
# goes through the list of spreadsheets available in [email]your@gmail.com[/email] google drive
# looking for one named with sheetName
spreadsheetFeed = client.GetSpreadsheetsFeed()
sheet = None
for spreadsheet in spreadsheetFeed.entry:
if spreadsheet.title.text == sheetName:
sheet = spreadsheet
# if no sheet is found it would create one. if client.CreateResource worked...
#if sheet == None:
#sheet = gdata.data.Resource(type='spreadsheet', title=sheetName)
#sheet = client.CreateResource(sheet)
# gets sheet key. it's actually in the url if you open it on a browser.
sheetKey = sheet.id.text.rsplit('/', 1)[1]
# gets a list of worksheets present in the spreadsheet
worksheetFeed = client.GetWorksheetsFeed(sheetKey)
for worksheetEntry in worksheetFeed.entry:
if worksheetEntry.title.text == worksheetName:
#worksheetEntry.title.text = ( "deleteme_" + ''.join(random.choice(string.ascii_lowercase + string.digits) for x in range(3)) )
#client.UpdateWorksheet(worksheetEntry)
#client.DeleteWorksheet(worksheetEntry) # this gives a 500 Internal Server Error
#worksheet = client.AddWorksheet(worksheetName, 4000, 5, sheetKey)
worksheet = worksheetEntry
else:
worksheet = client.AddWorksheet(worksheetName, 4000, 5, sheetKey)
# gets worksheet key, also found in the url
worksheetKey = worksheet.id.text.rsplit('/', 1)[1]
return client, sheetKey, worksheetKey
main():
# gather stuff you want to send to the spreadsheet into
# a list or something, this case eventTable
client, sheetKey, worksheetKey = getEventIDSpreadsheetKeys()
# what happens here is that for each entry in eventTable
# a dictionary is created. each label in the dictionary
# correspond to the names in the first row on the worksheet
# so A1 is eventid, B1 is eventname, and so on.
for event in eventTable:
eventDict = {'eventid':event[0],
'eventname':event[1],
'state':event[2],
'asd':event[3],
'wada':event[4],
'anothercollumn':event[5],
}
client.InsertRow(rowDict, sheetKey, worksheetKey)
The biggest hurdle with the example above is that the spreadsheet “eventIDMap” must exist in your Google Drive and the first row must have the exact same elements as the dictionary, and they need to be in lower case. There’s no way to add that first row, not with import gdata.spreadsheet.service anyway.
Conclusion is, with Python, if you want to add data to a spreadsheet, it might be easier to output a csv file from whatever source you have and import it into a spreadsheet. On the other hand, reading data from a spreadsheet can be a couple of lines code.
