Rest, Authentication & Cookies from within Excel

322 views
Skip to first unread message

Don Nalezyty

unread,
Oct 18, 2013, 12:33:30 PM10/18/13
to excel-r...@googlegroups.com
I'm trying to get metering data from a RESTful service via excel and parse the data directly into spreadsheets.

I've figured out the basics working through your examples and even am able to get JSON data into a spreadsheet if I manually copy the JSON string from a simple REST client in a cell in another spreadsheet, but I'm having problems getting the data directly via generalQuery.  I'm getting an authentication error.

The API of the REST service I'm using requires a user to authenticate via a POST that returns no data, but does pass back a cookie in the response header.  I'm able to do this via a simple rest client in a browser as the browsers handle this automatically, but I'm a bit lost how to do this in excel.  I've looked at the Cookies and Excel section of your website, but it's not really clarified it for me.

I need help to figure out how to do an authentication POST call from within Excel and store the cookie. Then I need to attach the cookie to the next restQuery to GET data and also ensure that the new cookie value from the response header is stored and used for the next query, and so forth...  

The POST call to get the cookie is fairly simple, although I can't share the system specifics.
with this data:
{"password": "acmeadminpassword", "user": "/acme/administrator"}

The response header includes a line that sets the cookie:
Set-Cookienimbula={"insert  oreo cookie stuffing here"}

After authenticating, every call updates the cookie, which expires in an hour if no further calls are made, at which point re-authentication is required.

Any help or direction you can provide to get me going would be greatly appreciated.

Thanks,
Don





Bruce McPherson

unread,
Oct 18, 2013, 2:31:23 PM10/18/13
to excel-r...@googlegroups.com
The very last argument to rest query is available to put post data in. If there is anything there it will use post rather than get.

Try that first and see what comes back.

Not sure at this point without thinking about it how to handle the cookie..will ponder

Sent from my iPad
--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Don Nalezyty

unread,
Oct 21, 2013, 12:47:50 PM10/21/13
to excel-r...@googlegroups.com
I'm still getting an authentication error when I use POST, but it is slightly different than what I was seeing before.  The error reads, " a certificate is required to complete client authentication."

I'm thinking this is likely an issue on the server, as I just discovered if I try to browse directly to the API URL, I get a warning about the certificate.  I'll dig into this with the SA and see if I can get this bit sorted.  Keep me posted if you have any further thoughts on the cookie issue.

Thanks,
Don

Bruce Mcpherson

unread,
Oct 31, 2013, 10:36:13 AM10/31/13
to excel-r...@googlegroups.com
Don

you can encourage POST to not complain about certificates with code that looks like this (see setoption line below) - it only seems to work with v6 of the serverxml object

Public Function httpPost(fn As String, _
        Optional data As String = vbNullString, Optional isjson As Boolean = False) As String
    pHtml = fn
    Dim v As Variant, ohttp As MSXML2.ServerXMLHTTP60
   
    Set ohttp = New MSXML2.ServerXMLHTTP60
    With ohttp
        .setOption 2, .getOption(2) - SXH_SERVER_CERT_IGNORE_CERT_DATE_INVALID
        .Open "POST", pHtml, False
        If isjson Then
            .SetRequestHeader "Content-Type", "application/json"
        Else
            .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        End If
        
        .Send data
        storeStuff ohttp
    End With
    
    httpPost = pText
    Set ohttp = Nothing
End Function

Your cookie data will be returned in the headers, which you can extract like this
Private Sub storeStuff(o As Object)
    With o
        pStatus = .status
        pText = .ResponseText
        pResponseHeaders = .GetAllResponseHeaders()
    End With
End Sub

Future accesses would setrequest header Cookie to the data returned in pResponseHeaders

I havent implemented this yet (I dont have something to test against), but in theory it should work like that

Bruce




Reply all
Reply to author
Forward
0 new messages