Google spreadsheets API - updating a list row

Bringing the internet highway into your project? Building FTP, HTTP, email, chat or other client solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
Dougy
Posts: 15
Joined: Sat Jul 01, 2006 7:37 pm
Location: New Zealand

Google spreadsheets API - updating a list row

Post by Dougy » Tue Aug 23, 2011 9:44 am

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.
LC 6.6.3, AnimationEngine 5.1
BeatSpeak & EVPoint available for iOS devices on the App Store.

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Google spreadsheets API - updating a list row

Post by Mark » Thu Aug 25, 2011 9:10 am

Hi,

Yes, you need to figure out the correct XML format and POST a complete XML document to a URL.

Kind regards,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Dougy
Posts: 15
Joined: Sat Jul 01, 2006 7:37 pm
Location: New Zealand

Re: Google spreadsheets API - updating a list row

Post by Dougy » Thu Aug 25, 2011 10:06 pm

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.
LC 6.6.3, AnimationEngine 5.1
BeatSpeak & EVPoint available for iOS devices on the App Store.

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Google spreadsheets API - updating a list row

Post by Mark » Fri Aug 26, 2011 12:57 pm

Hi Doug,

Could you post a link to that particular page of the API documentation?

Kind regards,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Dougy
Posts: 15
Joined: Sat Jul 01, 2006 7:37 pm
Location: New Zealand

Re: Google spreadsheets API - updating a list row

Post by Dougy » Fri Aug 26, 2011 1:41 pm

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.
Last edited by Dougy on Sat Aug 27, 2011 4:03 am, edited 1 time in total.
LC 6.6.3, AnimationEngine 5.1
BeatSpeak & EVPoint available for iOS devices on the App Store.

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Google spreadsheets API - updating a list row

Post by Mark » Fri Aug 26, 2011 1:52 pm

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
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Google spreadsheets API - updating a list row

Post by Mark » Fri Aug 26, 2011 2:14 pm

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
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Dougy
Posts: 15
Joined: Sat Jul 01, 2006 7:37 pm
Location: New Zealand

Re: Google spreadsheets API - updating a list row

Post by Dougy » Mon Aug 29, 2011 11:26 am

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?
LC 6.6.3, AnimationEngine 5.1
BeatSpeak & EVPoint available for iOS devices on the App Store.


Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Google spreadsheets API - updating a list row

Post by Mark » Wed Jan 04, 2012 11:51 am

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
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

robbiefowlerGAIPLH
Posts: 13
Joined: Fri Dec 09, 2011 12:12 pm

Re: Google spreadsheets API - updating a list row

Post by robbiefowlerGAIPLH » Sun Jan 22, 2012 4:32 pm

Thats sweet

Post Reply

Return to “Internet”