I am trying to update or insert cells into a google spreadsheet, this is my first time using the google api and python, so i have probably done something noddy.. but i cant see it for the life of me.
Currently my code updates existing cells but does not insert new cells,(blank cells stay blank) when i comment out "batchRequest.AddInsert(newCell)" but when its left in I get a returned status code 500 and a heap of xml, that is little help.
The code(part)
def setRowEntries(self, cohort_key=None):
cohort = self.GetCohort(cohort_key)
gd_client = gdata.spreadsheet.service.SpreadsheetsService()
gd_client.email = email
gd_client.password = password
gd_client.source = 'YOUR_APP_NAME'
# LOGIN
try:
# log in
gd_client.ProgrammaticLogin()
except socket.sslerror, e:
logging.error('Spreadsheet socket.sslerror: ' + str(e))
return False
# KEY FOR SHEET
key = '0At**************************Gc'
# LOOKUP FOR WORKSHEET KEY
GID_TABLE = {
'android_users_by_week_by_registration' : 'od6'
}
wksht_id =''
wksht_id = GID_TABLE[cohort_key]
# create a cells feed and batch request
cells = gd_client.GetCellsFeed(key, wksht_id)
batchRequest = gdata.spreadsheet.SpreadsheetsCellsFeed()
#TODO SET TITLE
#resize worksheet
# cells.col_count = len(cohort[0])
# cells.row_count = len(cohort)
rowcursor = 0
for myrow in cohort:
colcursor = 0
#print ("row ::"+str(myrow))
for mycell in myrow:
#print ("cell::"+str(mycell))
found = 0
#print ("try and place"+str(rowcursor)+','+str(colcursor))
for myentry in cells.entry:
if ((int(myentry.cell.row) == int(rowcursor+1)) and (int(myentry.cell.col) == int(colcursor+1))):
print "updating "+myentry.cell.text+" to "+str(mycell)
myentry.cell.inputValue = str(mycell)
batchRequest.AddUpdate(myentry)
found = 1
if not found:
print "inserting "+str(mycell)+" at Cell "+ str(rowcursor+1)+'_'+str(colcursor+1)
newCell = gdata.spreadsheet.SpreadsheetsCell()
newCell.cell = gdata.spreadsheet.Cell(inputValue=str(mycell), text=None, row=str(rowcursor+1), col=str(colcursor+1))
print newCEll.inpu
batchRequest.AddInsert(newCell)# the broken part
colcursor = colcursor + 1
rowcursor = rowcursor + 1
updated = gd_client.ExecuteBatch(batchRequest, cells.GetBatchLink().href)
if updated:
print "Sucessfull!"+str(updated)
else:
print "Failed!"
The Error(part)
type="text">Internal Error</ns0:content><ns1:id xmlns:ns1="http://schemas.google.com/gdata/batch">1626</ns1:id><ns0:title type="text">Error</ns0:title><ns1:status code="500" reason="Internal Error" xmlns:ns1="http://schemas.google.com/gdata/batch" /><ns1:operation type="insert" xmlns:ns1="http://schemas.google.com/gdata/batch" /><ns0:updated>2012-10-17T09:39:18.946Z</ns0:updated></ns0:entry><ns0:entry><ns0:id>1627</ns0:id><ns0:content type="text">Internal Error</ns0:content><ns1:id xmlns:ns1="http://schemas.google.com/gdata/batch">1627</ns1:id><ns0:title type="text">Error</ns0:title><ns1:status code="500" reason="Internal Error" xmlns:ns1="http://schemas.google.com/gdata/batch" /><ns1:operation type="insert" xmlns:ns1="http://schemas.google.com/gdata/batch" /><ns0:updated>2012-10-17T09:39:18.946Z</ns0:updated></ns0:entry><ns0:entry><ns0:id>1628</ns0:id><ns0:content type="text">Internal Error</ns0:content><ns1:id xmlns:ns1="http://schemas.google.com/gdata/batch">1628</ns1:id><ns0:title type="text">Error</ns0:title><ns1:status code="500" reason="Internal Error" xmlns:ns1="http://schemas.google.com/gdata/batch" /><ns1:operation type="insert" xmlns:ns1="http://schemas.google.com/gdata/batch" /><ns0:updated>2012-10-17T09:39:18.946Z</ns0:updated></ns0:entry><ns0:entry><ns0:id>1629</ns0:id><ns0:content type="text">Internal Error</ns0:content><ns1:id xmlns:ns1="http://schemas.google.com/gdata/batch">1629</ns1:id><ns0:title type="text">Error</ns0:title><ns1:status code="500" reason="Internal Error" xmlns:ns1="http://schemas.google.com/gdata/batch" /><ns1:operation type="insert" xmlns:ns1="http://schemas.google.com/gdata/batch" /><ns0:updated>2012-10-17T09:39:18.946Z</ns0:updated></ns0:entry><ns0:entry><ns0:id>1630</ns0:id><ns0:content
I found the google api docs very hard to understand and examples were few and too narrowly scoped. If you could let me know what im doing wrong that would be great
Reason became clear when viewing the xml in chrome , Insert not supported on batch., looks like new cells have to go in via a worksheet feed.