Android Question Update Google spreadsheet from b4a app

[b4auser1]

Is there a library to work with Google spreadsheet from b4a app ?

 

[Erel]

There isn't such a library. However if they provide REST api then it should be possible with HttpUtils2.

 

[Gentry]

Does anyone have sample code for interacting with the google docs REST API? I've implemented the oAuth2 code, but I am struggling with the payload string format and content type required to insert a row into a sheet. Been testing with Google's oAuthPlayground and see how to read data, however when trying to post to the https://spreadsheets.google.com/feeds/list/{key}/{worksheetId}/private/full path, I just get "Content-Type application/json isnot a valid input type."

I am trying payload strings like {"username" : "user", "email": "[email protected]"} which conforms to the column name (lowercased) structure of the Insert Row command.

I know this isn't directly a B4A question, but I am hoping someone has been down this path already. Here's my code sample. Using B4A v3.0

Dim hj As HttpJob
url = "https://spreadsheets.google.com/feeds/list/"&Main.SheetKey&"/"&WorksheetID&"/Private/full?access_token="&AccessToken"
param = "{"&QUOTE&"username"&QUOTE&" : "&QUOTE&username&QUOTE&", "&QUOTE&"email"&QUOTE&" : "&QUOTE&EmailAdr&QUOTE&"}"
hj.Initialize("UpdateTask",Me)
Log("param="&param)
hj.PostString(url, param)

Thank you! (1st question, Love the opportunity B4A brings!)

 

[Erel]

This is a json string. You should use JSONGenerator from the JSON library to build it.

In v3.80 you can do it with this code:

Dim jg As JSONGenerator
jg.Initialize(CreateMap("username": username, "email": EmalAdr))
Log(jg.ToString)

You are probably missing a request header (text/json maybe).

 

[Gentry]

Ok, using v3.8b1 now and the JSONGenerator. Now i'm running into this error:

Content-Type application/x-www-form-urlencoded is not a valid input type.
java.lang.Exception: Sub jobdone signature does not match expected signature.

I think I need to force the request headers into a compatible type, so I am trying this, however this does not effect the error output.

Sub RegisterUser
    Dim hj As HttpJob
    url = "https://spreadsheets.google.com/feeds/list/"&Main.SheetKey&"/"&WorksheetID&"/Private/full?access_token="&AccessToken
    Dim jg As JSONGenerator
    jg.Initialize(CreateMap("username": username.Text, "email": EmailAdr.Text))
    Log(jg.ToString)
    hj.Initialize("UpdateTask",Me)
    hj.GetRequest.InitializePost2(url,jg.ToString.GetBytes("UTF8")) 
    hj.GetRequest.SetHeader("Content-type", "application/atom+xml")
    hj.GetRequest.SetHeader("GData-Version", "3.0")
    hj.GetRequest.SetHeader("charset","UTF-8")
    hj.GetRequest.SetHeader("type","entry")
    hj.GetRequest.SetContentType("application/atom+xml")
    Log(url)
    hj.PostString(url,jg)
End Sub

Am I looking at this correctly?

Thanks.

 

[Gentry]

I discovered that the spreadsheet service prefers Atom+xml data, and was able to successfully post a new row to the spreadsheet, using the oAuthPlayground (using the XML string generated in the B4A app.) But I am still getting the error:

Content-Type application/x-www-form-urlencoded is not a valid input type.
java.lang.Exception: Sub jobdone signature does not match expected signature.

Here is my updated code.

Sub RegisterUser
    Dim hj As HttpJob
    url = "https://spreadsheets.google.com/feeds/list/"&Main.SheetKey&"/"&WorksheetID&"/Private/full" '?access_token="&AccessToken
    Dim xb As XMLBuilder
    xb = xb.create("entry").attr("xmlns","http://www.w3.org/2005/Atom").attr("xmlns:gsx","http://schemas.google.com/spreadsheets/2006/extended")
    xb = xb.e("gsx:timestamp").text(DateTime.Date(DateTime.Now)).up
    xb = xb.e("gsx:username").text(username.Text).up
    xb = xb.e("gsx:email").text(EmailAdr.Text).up
    Log(xb.asString)
    hj.Initialize("UpdateTask",Me)
    hj.GetRequest.InitializePost2(url,xb.asString.GetBytes("UTF8"))
    'hj.GetRequest.SetHeader("Content-type", "application/atom+xml")
    hj.GetRequest.SetHeader("GData-Version", "3.0")
    hj.GetRequest.SetHeader("Authorization", "OAuth "&AccessToken)
    hj.GetRequest.SetContentType("application/atom+xml")
    hj.PostString(url,xb.asString)
End Sub

So it seems that something is forcing the content type to be application/x-www-form-urlencoded, even though I am setting the Content Type in the request header (two ways, just in case.)

Any ideas?

Gentry

 

[Gentry]

I was able to pull a trace from fiddler thru the AVD emulator and found the transaction that is being sent thru my B4A app. It looks like the SetHeader values are NOT being sent.

POST https://spreadsheets.google.com/feeds/list/----spreadsheetkeyhere----/od8/Private/full HTTP/1.1
Content-Length: 236
Content-Type: application/x-www-form-urlencoded
Host: spreadsheets.google.com
Connection: Keep-Alive

<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended"><gsx:timestamp>05/16/2014</gsx:timestamp><gsx:username>myusername</gsx:username><gsx:email>[email protected]</gsx:email></entry>

So, am I doing it wrong?

 

[DonManfred]

Asuming the headers where correct it should be this way i suppose

Sub RegisterUser
    Dim hj As HttpJob
    url = "https://spreadsheets.google.com/feeds/list/"&Main.SheetKey&"/"&WorksheetID&"/Private/full?access_token="&AccessToken
    Dim jg As JSONGenerator
    jg.Initialize(CreateMap("username": username.Text, "email": EmailAdr.Text))
    Log(jg.ToString)
    hj.Initialize("UpdateTask",Me)
    'hj.GetRequest.InitializePost2(url,jg.ToString.GetBytes("UTF8"))
    hj.PostString(url,jg)
    hj.GetRequest.SetHeader("GData-Version", "3.0")
    hj.GetRequest.SetHeader("Authorization", "OAuth "&AccessToken)
    hj.GetRequest.SetContentType("application/atom+xml")
    Log(url)
End Sub

job.GetRequest-commands must be AFTER PostString, PostFile and so on...

 

[Gentry]

Thank you Manfred! Sort of counter-intuitive, like Ready, Fire, Aim, but that got me to a successful transaction. (used the Atom-XML version, not the JSON version for anyone else keeping track, also had to use private (not Private) in the URL, case sensitive and all.

So, now my last remaining challenge, that the httpjob is still expecting something different.

java.lang.Exception: Sub jobdone signature does not match expected signature.

I presume this is due to something invalid being returned in the response. Here is the fiddler trace for the successful response:

HTTP/1.1 201 Created
Content-Type: application/atom+xml; charset=UTF-8; type=entry
Expires: Fri, 16 May 2014 15:03:35 GMT
Date: Fri, 16 May 2014 15:03:35 GMT
Cache-Control: private, max-age=0, must-revalidate, no-transform
Vary: Accept, X-GData-Authorization, GData-Version
GData-Version: 3.0
ETag: "RlkXIG0sMit7ImA9Xk9aRhRT"
Location: https://spreadsheets.google.com/feeds/list/-----worksheetkeyhere------/od8/private/full/d180g
Content-Location: https://spreadsheets.google.com/feeds/list/-----worksheetkeyhere------/od8/private/full/d180g
Transfer-Encoding: chunked
Set-Cookie: NID=67=PZoOO1HyUJRijcgtXdFUNkn10YbwatVRZm_TOBKqNyb6M3yicTBgxCr1ROuTUPbaNe6lbR7m7Q1Z5yEtnOjWEo1hD9fELaBPG_vNC0XSNCk3tY04Y8a8lJWq0qyidTS9IH93Gt-aIrsCirkkzMyBNEhE;Domain=.google.com;Path=/;Expires=Sat, 15-Nov-2014 15:03:35 GMT;HttpOnly
P3P: CP="This is not a P3P policy! See http://www.google.com/support/accounts/bin/answer.py?hl=en&answer=151657 for more info."
X-Content-Type-Options: nosniff
X-Frame-Options: SAMEORIGIN
X-XSS-Protection: 1; mode=block
Server: GSE
Alternate-Protocol: 443:quic

472
<?xml version='1.0' encoding='UTF-8'?><entry xmlns='http://www.w3.org/2005/Atom' xmlns:gsx='http://schemas.google.com/spreadsheets/2006/extended' xmlns:gd='http://schemas.google.com/g/2005' gd:etag='&quot;RlkXIG0sMit7ImA9Xk9aRhRT&quot;'><id>https://spreadsheets.google.com/feeds/list/-----worksheetkeyhere------/od8/d180g</id><updated>2014-05-16T15:03:35.603Z</updated><app:edited xmlns:app='http://www.w3.org/2007/app'>2014-05-16T15:03:35.603Z</app:edited><category scheme='http://schemas.google.com/spreadsheets/2006' term='http://schemas.google.com/spreadsheets/2006#list'/><title>5/16/2014</title><content>username: myusername, email: [email protected]</content><link rel='self' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/list/-----worksheetkeyhere------/od8/private/full/d180g'/><link rel='edit' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/list/-----worksheetkeyhere------/od8/private/full/d180g'/><gsx:timestamp>5/16/2014</gsx:timestamp><gsx:username>myusername</gsx:username><gsx:email>[email protected]</gsx:email></entry>
0

See anything sticking out?

Thanks for looking!

 

[DonManfred]

the jobdone sub should have this signature if you are using the httputils2

Sub JobDone(job As HttpJob)
End Sub

 

[Gentry]

Another good catch! This activity is using the sample code provided with the oAuth2 example - GmailContacts - which included a local HttpUtil (not 2) code library. Once I removed the local code references and tweaked it to use httpUtils2, All is Good! Successful transaction, no errors!

Thanks so much for your help on this!
Gentry

 

[JDarter]

Another good catch! This activity is using the sample code provided with the oAuth2 example - GmailContacts - which included a local HttpUtil (not 2) code library. Once I removed the local code references and tweaked it to use httpUtils2, All is Good! Successful transaction, no errors!

Thanks so much for your help on this!
Gentry

Gentry,

Can you post a small code sample or tutorial on what you learned about the requirements for getting B4A to communicate correctly with google spreadsheets? It seems you've paved the way and I could definitely benefit from your experience. Thanks in advance.

Jeff

 

[fredo]

+1

 

[techknight]

I know this thread is old, but what ever came of this? And does it still work? any libraries? Our inventory system is in google sheets and I am trying to find a way to interface with it using my own app and a barcode scanner.

 

[dian]

Interesting topic..
Cloud database using google sheet.

 

[Erel]

This is an old thread. It should be simple to access Google Sheets with the relatively new GoogleOAuth2 class.

 

[techknight]

Hmm... Is there a tutorial on how to access Sheets with GoogleOAuth2? I found the generic thread but it doesn't go into detail on how to achieve this. I searched the forum and the 2nd thread is this one, so literally going in circles.

I know AppSheets is pretty good with interfacing with Google Sheets, but I need to make it a bit more compact and simple interface wise than what AppSheets can do.

 

[Erel]

You should start a new thread for this discussion.