Excel. Get JSON data from two-step request

by PerryW   Last Updated April 24, 2018 01:01 AM

I need to load data into an excel 2016 worksheet using a REST call that returns JSON formatted data.

No problem there - it's a lot easier than it used to be. My problem is that, to get the data I need to send two requests. The first to get a time-limited authorisation key, the second, within a couple of minutes, using that key to get the data.

Something like this:

https://www.sitename.com/GetSig?user=USERNAME&pw=PASSWORD

That returns:

{
"status": {
    "is_error": false,
    "message": "",
    "request_id": null
},
"data": {
    "signature": "qidXWg8OgxzhhjVRISLt3iMAtXEqOcXxTG2Sg1ppkRI%3D",
    "signature_id": "19"
}
}

I then have to use that to generate:

https://www.sitename.com/GetStuff?usersig=qidXWg8OgxzhhjVRISLt3iMAtXEqOcXxTG2Sg1ppkRI%3D&sigID=19

Which, in turn, returns the data I'm after (grossly simplified here - it's usually a couple of thousand rows and about 50 cols)

{
"status": {
    "is_error": false,
    "message": "",
    "request_id": null
},
"data": {
    "db_output": [
        {
            "Name": "John Doe",
            "DoB": "01-01-1980"
        }
    ]
    }
}

I'm assuming that VBA is the way to go... I can do this stuff easily in Javascript or Python, but haven't really used VBA in years. Any pointers or examples?



Related Questions



Saving Excel sheet as JSON file

Updated September 13, 2017 08:01 AM



Skip json data in Excel import

Updated June 13, 2017 13:01 PM