Home > python > Automatically update spreadsheets and graphs – Part 1

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.
Graph before running the code.

And this is the graph after running the code.
Graph after running 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

Advertisements
Categories: python Tags: , ,
  1. LABUK
    May 9, 2011 at 7:28 pm

    Hi, it seems we were working on the same problem at the same time. I found this post by googling the error

    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’}

    However, I am a programming novice. What does this error mean and what action can I take to correct it?

    thanks

    • May 10, 2011 at 6:19 am

      Hi, Looks like the data you are requesting for is wrong. I got the same error when the spreadsheet key was wrong. Have you tried running the sample code? If that works then try the code in my post. This part -> _PrintFeed(feed) will show you a list of all the documents you have.

      • LABUK
        May 11, 2011 at 4:12 pm

        ok, your code worked for me. this gives me a lift to my next tasks, thanks a lot.

  2. October 5, 2011 at 5:19 pm

    how do can I insert data into the next empty row? Thanks, great script

    • October 6, 2011 at 9:08 am

      Hi,
      This part _CellsUpdateAction(2, 2, “Date”, curr_key, curr_wksht_id) inserts data into the cell on the second row in the second column. The data itself is Date. The curr_key and curr_wksht_id are obtained from line 41 and 46 respectively.

      So to insert data into the next empty row you need to know where that cell is and just use the _CellsUpdateAction() function as shown. Also have a look at Part 2 of this post. It gets data from a csv file.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: