pygspreadsheets.py - pygspreadsheets

‹r9

r10

Source path: svn/ trunk/ release/ pygspreadsheets.py

#!/usr/bin/python
"""
Python wrapper for Google Spreadsheets

http://code.google.com/p/pygspreadsheets/
"""

import urllib
import urllib2
import httplib
import datetime
import time
import os.path
import xml.dom.minidom
from xml.dom import minidom
from urlparse import urlparse

class SmartRedirectHandler(urllib2.HTTPRedirectHandler):     
    def http_error_301(self, req, fp, code, msg, headers):  
        result = urllib2.HTTPRedirectHandler.http_error_301( 
            self, req, fp, code, msg, headers)              
        result.status = code                                 
        return result                                       
    def http_error_201(self,req,fp,code,msg,headers):
        print "this is OOOK"
        return 1
    def http_error_302(self, req, fp, code, msg, headers):   
        result = urllib2.HTTPRedirectHandler.http_error_302(
            self, req, fp, code, msg, headers)              
        result.status = code                                
        gsessionid = headers.dict['location'].split('?')[1].split('=')[1] 
        return gsessionid



class gSpreadsheet:
    global gsessionid
    worksheets = {}
    sid = ""
    lsid = ""
    auth = ""
    gses=""

    def getAuth(self,email,password):
        url = "https://www.google.com/accounts/ClientLogin?Email=%s&Passwd=%s&source=pamela-fox-ss&service=wise" % (email, password)
        data = urllib2.urlopen(url).read().split('\n')
        self.sid  = data[0].split('=')[1]
        self.lsid = data[1].split('=')[1]
        self.auth = data[2].split('=')[1]	
        print "Authorized" 

    def getSpreadsheetsList(self):
        authString = "GoogleLogin auth=%s" % (self.auth)
        authurl = "http://spreadsheets.google.com/feeds/spreadsheets/private/full"	
        headers = {
            'User-Agent': 'mine',
            'Authorization': authString,
            'Content-Type': 'application/atom+xml'
                }

        httplib.HTTPConnection.debuglevel = 1 #!
        req = urllib2.Request(authurl)
        req.add_header('Authorization',authString)
        opener = urllib2.build_opener(SmartRedirectHandler)
        data = ''
        try:
          data = opener.open(req)
          data = data.read()
          dom = xml.dom.minidom.parseString(data)

          entries=dom.getElementsByTagName('entry')
          spreadsheets = []

          for entry in entries:
            dentry={}
            dentry['id']=entry.getElementsByTagName('id')[0].firstChild.data
            dentry['updated']=entry.getElementsByTagName('updated')[0].firstChild.data
            dentry['title']=entry.getElementsByTagName('title')[0].firstChild.data
            try: dentry['content']=entry.getElementsByTagName('content')[0].firstChild.data
            except AttributeError: dentry['content']=''
            dentry['linkFeedHref']=entry.getElementsByTagName('link')[0].getAttribute('href')
            dentry['authorName']=entry.getElementsByTagName('author')[0].getElementsByTagName('name')[0].firstChild.data
            dentry['authorEmail']=entry.getElementsByTagName('author')[0].getElementsByTagName('email')[0].firstChild.data

            spreadsheets.append(dentry)

          return spreadsheets

        except urllib2.URLError, e:
          print "Request: " + req
          print "Error: " + e.read()


    def getWorksheets(self, linkFeedHref):
        authString = "GoogleLogin auth=%s" % (self.auth)
        authurl = linkFeedHref	

        headers = {
            'User-Agent': 'mine',
            'Authorization': authString,
            'Content-Type': 'application/atom+xml'
                }

        httplib.HTTPConnection.debuglevel = 1 #!
        req = urllib2.Request(authurl)
        req.add_header('Authorization',authString)
        opener = urllib2.build_opener(SmartRedirectHandler)
        data = ''
        try:
          data = opener.open(req)
          data = data.read()
          dom = xml.dom.minidom.parseString(data)

          entries=dom.getElementsByTagName('entry')
          worksheets = []
          for entry in entries:
            dentry={}
            dentry['id']=entry.getElementsByTagName('id')[0].firstChild.data
            dentry['updated']=entry.getElementsByTagName('updated')[0].firstChild.data
            dentry['title']=entry.getElementsByTagName('title')[0].firstChild.data
            dentry['listFeedHref']=entry.getElementsByTagName('link')[0].getAttribute('href')
            dentry['cellsFeedHref']=entry.getElementsByTagName('link')[1].getAttribute('href')
            dentry['colCount']=entry.getElementsByTagName('gs:colCount')[0].firstChild.data
            dentry['rowCount']=entry.getElementsByTagName('gs:rowCount')[0].firstChild.data
            worksheets.append(dentry)
        
          # print worksheets
          return worksheets

        except urllib2.URLError, e:
          print "Request: " + req
          print "Error: " + e.read()



    def getWorksheetListFeed(self,listFeedHref,query='',projection='full'):
        authString = "GoogleLogin auth=%s" % (self.auth)
        authurl = listFeedHref.replace('full',projection)
        if(query != ''):
          authurl = authurl + '?sq=' + urllib.quote(query)

        headers = {
            'User-Agent': 'mine',
            'Authorization': authString,
            'Content-Type': 'application/atom+xml'
                }

        httplib.HTTPConnection.debuglevel = 1 #!
        req = urllib2.Request(authurl)
        req.add_header('Authorization',authString)
        opener = urllib2.build_opener(SmartRedirectHandler)
        data = ''
        try:
          data = opener.open(req)
          data = data.read()
          dom = xml.dom.minidom.parseString(data)
          entries=dom.getElementsByTagName('entry')
          rows = []
          for entry in entries:
            dentry={}
            for child in entry.childNodes:
	    
              try: dentry[child.nodeName]=entry.getElementsByTagName(child.nodeName)[0].firstChild.data
              except AttributeError: dentry[child.nodeName]=''
            rows.append(dentry)

          return rows

        except urllib2.URLError, e:
            print  req
            print "Error: " + e.read()


    def getWorksheetCellsFeed(self,cellsFeedHref,query=''):
        authString = "GoogleLogin auth=%s" % (self.auth)
        authurl = cellsFeedHref	
        if(query != ''):
          authurl = authurl + '?' + urllib.quote(query)

        headers = {
            'User-Agent': 'mine',
            'Authorization': authString,
            'Content-Type': 'application/atom+xml'
                }

        httplib.HTTPConnection.debuglevel = 1 #!
        req = urllib2.Request(authurl)
        req.add_header('Authorization',authString)
        opener = urllib2.build_opener(SmartRedirectHandler)
        data = ''
        try:
          data = opener.open(req)
          data = data.read()

          dom = xml.dom.minidom.parseString(data)

          entries=dom.getElementsByTagName('entry')
          rows = []
          for entry in entries:
            dentry={}
            for child in entry.childNodes:
              try: dentry[child.nodeName]=entry.getElementsByTagName(child.nodeName)[0].firstChild.data
              except AttributeError: dentry[child.nodeName]=''
            dentry['row'] = entry.getElementsByTagName('gs:cell')[0].getAttribute('row')
            dentry['col'] = entry.getElementsByTagName('gs:cell')[0].getAttribute('col')
            dentry['selfHref'] = entry.getElementsByTagName('link')[0].getAttribute('href')
            dentry['editHref'] = entry.getElementsByTagName('link')[1].getAttribute('href')
	    dentry['inputVal'] = entry.getElementsByTagName('gs:cell')[0].getAttribute('inputValue')	
            rows.append(dentry)


          return rows

        except urllib2.URLError, e:
          print  req
          print "Error: " + e.read()


    def addRow(self,listFeedHref,rowData):
        authString = "GoogleLogin auth=%s" % (self.auth)
        authurl = listFeedHref	
        headers = {
            'User-Agent': 'mine',
            'Authorization': authString,
            'Content-Type': 'application/atom+xml'
                }

        entryS = """<?xml version='1.0' ?>
        <entry xmlns='http://www.w3.org/2005/Atom'
        xmlns:gsx='http://schemas.google.com/spreadsheets/2006/extended'>"""
        entryD = ""
        for x in rowData.keys():
            entryD = entryD + "<gsx:" + x + ">" + rowData[x] + "</gsx:" + x + ">" 
        entryE = "</entry>"
        entry = entryS + entryD + entryE
        httplib.HTTPConnection.debuglevel = 1 #!
        req = urllib2.Request(authurl,entry,headers)
        #req.add_header('Authorization',authString)
        opener = urllib2.build_opener(SmartRedirectHandler)
        data = ''
        try:
            data = opener.open(req)
        except urllib2.URLError, e:
            print  req
            print "Error: " + e.read()

    def addCell(self,cellsFeedHref,cellRow,cellCol,value):
        authString = "GoogleLogin auth=%s" % (self.auth)
        authurl = cellsFeedHref	
        headers = {
            'User-Agent': 'mine',
            'Authorization': authString,
            'Content-Type': 'application/atom+xml'
                }

        entry = """<?xml version='1.0' ?>
        <entry xmlns='http://www.w3.org/2005/Atom'
        xmlns:gs='http://schemas.google.com/spreadsheets/2006'>
        <gs:cell row='%s' col='%s' inputValue='%s' /> 
        </entry> """ % (cellRow,cellCol,value)

        httplib.HTTPConnection.debuglevel = 1 #!
        req = urllib2.Request(authurl,entry,headers)
        #req.add_header('Authorization',authString)
        opener = urllib2.build_opener(SmartRedirectHandler)
        data = ''
        try:
            data = opener.open(req)
        except urllib2.URLError, e:
            print  req
            print "Error: " + e.read()

    def updateCell(self,cellsFeedHref,cellRow,cellCol,value):
        authString = "GoogleLogin auth=%s" % (self.auth)
        authurl = cellsFeedHref

        entry = """<?xml version='1.0' ?>
        <entry xmlns='http://www.w3.org/2005/Atom'
        xmlns:gs='http://schemas.google.com/spreadsheets/2006'>
        <gs:cell row='%s' col='%s' inputValue='%s' /> 
        </entry> """ % (cellRow,cellCol,value)

        httplib.HTTPConnection.debuglevel = 0 #!
        o = urlparse(cellsFeedHref)
        h = httplib.HTTPConnection(o[1])
        h.putrequest('PUT',o[2])
        h.putheader('User-Agent','mine')
        h.putheader('Authorization',authString)
        h.putheader('Content-Type','application/atom+xml')
        h.putheader('Content-Length',str(len(entry)) )
        h.endheaders()
        h.send(entry)
	return h.getresponse().read()

# execute tests
if __name__ == '__main__':
  ''' execute module directly to perform tests'''

  # getting account info
  import accountInfo
  username = accountInfo.info['username']
  password = accountInfo.info['password']

  # initializing new spreadsheet obj    
  a = gSpreadsheet()

  # logging into Google account
  a.getAuth(username,password)

  # getting a list of all spreadsheets in account
  ss = a.getSpreadsheetsList()
  
  # finding the href of spreadsheet with title 'test1' 
  linkFeedHref=''
  for spreadsheet in ss:
    if spreadsheet['title']=='test1':
      linkFeedHref = spreadsheet['linkFeedHref']
  
  # get list of worksheets in selected spreadsheet
  sw = a.getWorksheets(linkFeedHref)

  # get list feed for first worksheet in selected spreadsheet - query is optional parameter!
  print "listFeedHref for worksheet 0:" + sw[0]["listFeedHref"]
  swl = a.getWorksheetListFeed(sw[0]["listFeedHref"],"ipm>4 and hours<2")

  # get cell feed for first worksheet in selected spreadsheet
  print "cellsFeedHref for worksheet 0:" + sw[0]["cellsFeedHref"]
  swc = a.getWorksheetCellsFeed(sw[0]["cellsFeedHref"])

  # adding cell to first worksheet in selected spreadsheet in row 13, col 5
  a.addCell(sw[0]["cellsFeedHref"],"12","5","5006")

  # adding row to first worksheet in selected spreadsheet (gets added to end)
  a.addRow(sw[0]["listFeedHref"], {"hours":"1","ipm":"50","items":"60","name":"Liz B"})     

  # editing contents of cell entry #37 with value 666
  editHref = swc[37]["editHref"]
  cRow = swc[37]["row"]
  cCol = swc[37]["col"]
  a.updateCell(editHref, cRow, cCol, "666")