Automatically update spreadsheets and graphs – Part 1

Problem: Automatically update a spreadsheet and its corresponding graphs.

Solutions:
I had a number of solutions or so I thought.
1. Use python and xlwt. xlwt does not work with graphs. Atleast not at the moment.
2. Use python uno. The server running the script would need to have a local version of open office.
3. Use googledocs and python. (Final code here)

So far the 3rd solution is looking promising. I have a sample script and a sample the google doc that I was to edit.

#!/usr/bin/python

import gdata.spreadsheet.service

def _CellsUpdateAction(row,col,inputValue,key,wksht_id):
    entry = spr_client.UpdateCell(row=row, col=col, inputValue=inputValue,
            key=curr_key, wksht_id=curr_wksht_id)
    if isinstance(entry, gdata.spreadsheet.SpreadsheetsCell):
        print 'Updated!'

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' % (i, entry.title.text, entry.content.text)
            # Print this row's value for each column (the custom dictionary is
            # built using the gsx: elements in the entry.)
            print 'Contents:'
            for key in entry.custom:  
              print '  %s: %s' % (key, entry.custom[key].text) 
            print '\n',
        else:
            print '%s %s\n' % (i, entry.title.text)


email = 'user@gmail.com'
password = 'password'

spr_client = gdata.spreadsheet.service.SpreadsheetsService()
spr_client.email = email
spr_client.password = password
spr_client.ProgrammaticLogin()

feed = spr_client.GetSpreadsheetsFeed()
# List the spreadsheets that available.
# Totally unneccessary but just here so that you can see what index to give to input variable below.
_PrintFeed(feed) 
# Get the first one. (Otherwise pick the desired index)
input = 0 
id_parts = feed.entry[0].id.text.split('/')
curr_key = id_parts[len(id_parts) - 1]
print curr_key

feed = spr_client.GetWorksheetsFeed(curr_key)
id_parts = feed.entry[0].id.text.split('/')
curr_wksht_id = id_parts[len(id_parts) - 1]
print curr_wksht_id

# I just did it this way to test to see if it works.
date1 = "2011-01-01"  # Overwrites the existing dates.
date2 = "2011-01-02"  # Overwrites the existing dates.
date3 = "2011-01-03"  # Overwrites the existing dates.
weight1 = "30"
weight2 = "60"
weight3 = "90"
weight4 = "80"

_CellsUpdateAction(2, 2, "Date", curr_key, curr_wksht_id)
_CellsUpdateAction(3, 2, date1, curr_key, curr_wksht_id)
_CellsUpdateAction(4, 2, date2, curr_key, curr_wksht_id)
_CellsUpdateAction(5, 2, date3, curr_key, curr_wksht_id)
_CellsUpdateAction(2, 3, "Weight", curr_key, curr_wksht_id)
_CellsUpdateAction(3, 3, weight1, curr_key, curr_wksht_id)
_CellsUpdateAction(4, 3, weight2, curr_key, curr_wksht_id)
_CellsUpdateAction(5, 3, weight3, curr_key, curr_wksht_id)
_CellsUpdateAction(6, 3, weight4, curr_key, curr_wksht_id)

 

Some of the errors I got trying to get the above script working include

gdata.service.RequestError: {‘status’: 400, ‘body’: ‘We're sorry, a server error occurred. Please wait a bit and try reloading your spreadsheet.’, ‘reason’: ‘Bad Request’}

gdata.service.RequestError: {‘status’: 400, ‘body’: ‘Invalid query parameter value for R0C0.’, ‘reason’: ‘Bad Request’}

Before running code.

And this is the graph after running the code.

Conclusion:
My main objective was updating a spreadsheet and having the graph change as well. So far python + googledocs works very nicely for me. I will upload more code once I get a chance to work on this some more.

Source:
Most of the code came from the spreadsheet.py example in the Google Spreadsheets Data API.

Instructions on how to install Google Spreadsheets Data API for python is available here. The instructions worked for me except that I used sudo python setup.py install to install the package.

Part 2