eduardo simioni

Tag: spreadsheet

google python api, review and sample code

by on Jan.04, 2013, under python, visualization

Looking around the web it seemed fairly simple to work with Google Python API to update a spreadsheet. But alas, it is not. Installation is super straightforward (with Python 2.7.3 at least), just download and run installer. Using it is another world.

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:

  • There’s no support yet to delete spreadsheets.
  • Error messages are quite cryptic, like “500 internal server error”.
  • 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.
  • Deleteworksheet, althought it exists, always gives an ‘Internal server’ error
  • 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.
  • 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.
  • Official documentation is scarce. Seems like there’s better support for Java and .NET API’s, while Python’s lagging behind.

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 your@gmail.com 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 not found 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.

Leave a Comment :, , , , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!