Rest, Authentication & Cookies from within Excel

[Don Nalezyty]

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.

https://api.oc.example.com/authenticate/

with this data:

{"password": "acmeadminpassword", "user": "/acme/administrator"}

The response header includes a line that sets the cookie:

Set-Cookie: nimbula={"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]

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]

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]

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