How To Use Google Spreadsheets With Google App Engine, Part 3
Sorry for the long delay (first post, second post)! I just started school again (senior year!), so the last few weeks have been packing, finding an apartment, moving in, choosing classes, actually going to class, et cetera et cetera.
Anyway, the following examples are really just pretty wrappers for functionality included in the raw Spreadsheets API, but they’ll give you something concrete to work from. They are pretty much just copy-pasted from my code, but I had to reformat them a bit for the blog. As always, take a good, hard look before using them in your system.
Blazing Batch Modifications
The biggest change from the last post is the ability to cache modification orders and execute them in batches. This obviously is handy for conserving bandwidth, CPU cycles, and even time, and is included in the actual Spreadsheets API.
def setCells(cellDict):
wsID = 'yourWorksheetID'
destURL = 'http://spreadsheets.google.com/feeds/cells/'+wsID+'/1/private/full/batch'
postStr = ''
for (rowNum, colNum), content in cellDict.items():
postStr += 'http://spreadsheets.google.com/feeds/cells/'+\
wsID+'/1/private/full/R'+str(rowNum)+'C'+str(colNum)+' '
postStr += ' '
urlfetch.fetch(url=destURL, method=urlfetch.POST, payload=postStr, \
headers={'Content-Type' : 'application/atom+xml', 'GData-Version' : '3.0', \
'Authorization' : 'AuthSub token="yourAuthSubToken"', 'If-Match' : '*'})
Be sure to make cellDict a dictionary object of the form (rowNum, colNum) -> content, that is, a tuple going to some string-cast-able content. And make sure to change yourWorkSheetID and yourAuthSubToken.
Blissful Batch Reads
It would be awfully asymmetrical (and impractical) to leave out batch reads, so here is some sample code to that end. Again, this code only wraps the Spreadsheets API’s own batch read functionality.
def getCells(minRow, maxRow, minCol, maxCol):
wsID = 'yourWorksheetID'
destURL = 'http://spreadsheets.google.com/feeds/cells/'+ \
wsID+'/1/private/full?min-row='+str(minRow)+'&max-row='+ \
str(maxRow)+'&min-col='+str(minCol)+'&max-col='+str(maxCol)
retStr = urlfetch.fetch(url=destURL, method=urlfetch.GET, \
headers={'Content-Type' : 'application/atom+xml', \
'GData-Version' : '3.0', 'Authorization' : \
'AuthSub token="yourAuthSubToken"', 'If-Match' : '*'}).content
gsOccurrences = re.findall(r"", retStr)
retDict = {}
for occ in gsOccurrences:
row = int(re.findall(r"(?<=row=').*?(?=')", occ)[0])
col = int(re.findall(r"(?<=col=').*?(?=')", occ)[0])
content = re.findall(r"(?<=inputValue=').*?(?=')", occ)[0]
retDict[(row, col)] = content
return retDict
This will return a dictionary of the same format used as input in the setCells function. Again make sure to change yourWorkSheetID and yourAuthSubToken.
Final Thoughts
I've now been using the above code in production for about three weeks, and it really works well. Whenever I want an update on SearchEkko I can just check a fast-loading, real-time-updating Google Spreadsheet -- much handier than the default App Engine administrative interface. Hope it helps you out!