Page 1 of 1
Google spreadsheets API - updating a list row
Posted: Tue Aug 23, 2011 9:44 am
by Dougy
Hi,
I have been successful in accessing my Google spreadsheet data, putting it into a DataGrid, editing the data, but now I need to "put" it back into the original Google spreadsheet by "updating a list row". (I retrieved it as a "list-based feed").
I am unsure as to what format (how??) the data needs to be "PUT" back to the spreadsheet.
The Google Spreadsheet API reads:
"To update the contents of an existing row, first you retrieve the row you want to update, then you modify it as desired, and then you send a PUT request, with the updated row in the message body, to the row's edit URL."
Does the entire original row (XML Entry) need to be PUT in XML format? Or what??
Any help very much appreciated! Thanks.
Re: Google spreadsheets API - updating a list row
Posted: Thu Aug 25, 2011 9:10 am
by Mark
Hi,
Yes, you need to figure out the correct XML format and POST a complete XML document to a URL.
Kind regards,
Mark
Re: Google spreadsheets API - updating a list row
Posted: Thu Aug 25, 2011 10:06 pm
by Dougy
Hi Mark
Thanks for your reply.
You suggest I POST the XML document, however the "Updating a list row" documentation on the Google Spreadsheet API says I should use PUT.
What should it be?
Also, do I need to create a Query first to get *just* the row I want to update, or can I post (put?) the updated row data using the row's Edit URL that I have from the list-based feed data (there is an Edit URL for every row/XML entry in the list-based feed)?
Kind regards
Doug.
Re: Google spreadsheets API - updating a list row
Posted: Fri Aug 26, 2011 12:57 pm
by Mark
Hi Doug,
Could you post a link to that particular page of the API documentation?
Kind regards,
Mark
Re: Google spreadsheets API - updating a list row
Posted: Fri Aug 26, 2011 1:41 pm
by Dougy
Sure, Mark. Here's the link to the Google Docs Spreadsheet API:
http://code.google.com/apis/spreadsheet ... readsheets
An update:
I've been able to get an individual ROW from my Spreadsheet (as well as a list-based feed), edit the ROW, and now want to send it back to my Spreadsheet in Google Docs.
However, when I try the following code using an HTTP Header consisting of "if-match: *", it gives me an error: "Error 415: Unsupported Media Type"
Code: Select all
# Take the changed data from the DataGrid and send it back to the Google Spreadsheet
local myEntry
put revXMLText(myTree) into myEntry
XMLError theresult
put myEntry into tDatatoPost
put "https://spreadsheets.google.com/feeds/list/txUMXoSIm6E9UGHCZFs6Spg/od6/private/full/b8lvi/1icclpp834" into curlURL
set the httpheaders to field "HEADER-ROW"
put tDatatoPost into URL curlURL
put the result into tResult
if tResult is not empty then
answer "Error: " & the result
else
# Continue execution
end if
-- reset the HTTP Headers
set the httpheaders to empty
Any ideas what the 415 Unsupported Media Type Error is actually referring to in my code or header?
Regards, Doug.
Re: Google spreadsheets API - updating a list row
Posted: Fri Aug 26, 2011 1:52 pm
by Mark
Doug, I can't click that link
Your script has the same problem. Maybe that's a reason why it doesn't work? I'll have a look at your script while you fix the links...
Mark
Re: Google spreadsheets API - updating a list row
Posted: Fri Aug 26, 2011 2:14 pm
by Mark
Doug,
When you retrieve data from GS, do you receive it in XML format, then load it "into" the XML external, change it and get it back from the external again? I bet Google doesn't like a) the format produced by the external and b) the Windows Latin or MacRoman text encoding. Try changing the XML data directly by script, rather then using that external. Before editing, you might want to convert to into ASCII:
Code: Select all
put uniDecode(uniEncode(myUTF8XMLData,"UTF8"),"English") into myPlainTextXMLData
and when you're done editing, convert it back:
Code: Select all
put uniDecode(uniEncode(myPlainTextXMLData),"UTF8") into myUTF8XMLData
I hope this helps.
What do you have in fld "HeaderRow"?
Mark
Re: Google spreadsheets API - updating a list row
Posted: Mon Aug 29, 2011 11:26 am
by Dougy
Hi Mark
In fld "Header-Row" I have:
If-match: *
but I've also tried including the authentication header info which is:
Content-type: application/atom+xml
Authorization: GoogleLogin auth=(auth token in here...)
If-match: *
Also, Yes, I first retrieve the data from GS as XML then use Livecode's XML commands, etc. to load it into an XMLTree.
Next I put the XMLTree data into a DataGrid, update/edit the data manually within the DataGrid (this is what the user of the application will be doing), then put the updated data back into the XMLTree.
Finally, (and this is the part that doesn't work) I post the XMLTree back to GS using a PUT statement and cURL.
The XMLTree that I send back to GS looks EXACTLY the same as the original XML data I retrieved from GS in the first place.
Also, I have tried just posting back the original data from GS, but this gives me the same error(s), either an HTTP 400 error or a 415 error, depending on the header I use.
I have yet to try your idea of editing the XML as ASCII data - this is next on my list!
Any more thoughts?
Re: Google spreadsheets API - updating a list row
Posted: Wed Jan 04, 2012 3:54 am
by teacherguy
Re: Google spreadsheets API - updating a list row
Posted: Wed Jan 04, 2012 11:51 am
by Mark
Hi,
I think that example uses a fairly diffrent approach, since it doesn't seem to use XML data.
Actually, I was waiting for Dougy to report whether editing the XML manually (or by script) rather than using the external solved his problem (I have a feeling it would).
Best,
Mark
Re: Google spreadsheets API - updating a list row
Posted: Sun Jan 22, 2012 4:32 pm
by robbiefowlerGAIPLH
Thats sweet