VBA to parse data from web API

Question

Public Sub IMPORTMESTER()

Dim xTOK As String
Dim URL As String
Dim httpREQ As Object
Dim JSON As Object
Dim xLINE As Variant

xTOK = "bdj62bzknriy3dd9g561on2xl2"

URL = "https://api.smartsheet.com/2.0/sheets/7352150637471620"

Set httpREQ = CreateObject("MSXML2.XMLHTTP.6.0")
With httpREQ
    .Open "GET", URL, False
    .setRequestHeader "Authorization", "Bearer " & xTOK
    .setRequestHeader "Content-Type", "application/json"
    .Send
End With

xLINE = httpREQ.ResponseText

MsgBox ("Complete!")

End Sub

So, Ive returned data I need, but I tried several methods to parse it and paste in excel, but without success. Here is the part of responsetext:

"cells":[{"columnId":2400415921792900,"value":"MWP08","displayValue":"MWP08"},{"columnId":6904015549163396,"value":"A-WP-80301D5D10C00","displayValue":"A-WP-80301D5D10C00"},{"columnId":1274516014950276,"value":"MWP0830W27V50KD","displayValue":"MWP0830W27V50KD"},{"columnId":5778115642320772,"value":"WP08 30W,120-277VAC,Ra70 5000K Clear lens,Dark bronze","displayValue":"WP08 30W,120-277VAC,Ra70 5000K Clear lens,Dark bronze"},{"columnId":3526315828635524,"value":"image002.png","displayValue":"image002.png","formula":"=SYS_CELLIMAGE(\"image002.png\",\"vDOY-InMRamvhitNGotKzb\",35,52,\"image.png\")","image":{"id":"vDOY-InMRamvhitNGotKzb","height":35,"width":52,"altText":"image002.png"}},{"columnId":8029915456006020},{"columnId":711566061528964,"value":1884.0,"displayValue":"1884","linkInFromCell":{"status":"INACCESSIBLE","sheetId":4533800614029188,"rowId":null,"columnId":null,"sheetName":"MLC-Inventory扣减(2019)"}},{"columnId":2963365875214212,"value":"https://mesterleds.com/wp-content/uploads/2017/12/WP01-45W70W.png","displayValue":"https://mesterleds.com/wp-content/uploads/2017/12/WP01-45W70W.png"},{"columnId":7466965502584708},{"columnId":1837465968371588},{"columnId":6341065595742084},{"columnId":4089265782056836},{"columnId":8592865409427332},{"columnId":430091084818308,"value":175.0,"displayValue":"175"},{"columnId":4933690712188804},{"columnId":2681890898503556},{"columnId":7185490525874052},{"columnId":1555990991660932},{"columnId":6059590619031428}]},{"id":7080298036914052,"rowNumber":3,"siblingId":2576698409543556,"expanded":true,"createdAt":"2019-01-31T00:06:35Z","modifiedAt":"2019-02-18T16:56:50Z",

Each row of table I need starts with:"cells';[{" while I only need "displayValue": for columns!

I tried several solutions and suggestions from various threads from StackOverflow but... no luck!

Below is desired output: Final excel format (unneccessary columns hidden)

Possible duplicate of [Parsing JSON in Excel VBA](https://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba) — Pᴇʜ, Feb 22 '19 at 13:24
^^ that link is useful and widely cited but due to the particular problem faced by the OP there, it excludes some of the more commonly used json parsing libraries. Can you post a pastebin.com link to the full response text and indicate what the output structure should look like and we can then add to the suggested list of reference questions. — QHarr, Feb 22 '19 at 13:44
@Peh, this one of the threads Ive tried to used in mine case, but without success. Before posting a question, I've searched trough archives, but now I'm clueless what I'm doing wrong — Mr K, Feb 22 '19 at 13:45
^ well you could provide us with something more descriptive than no luck. — QHarr, Feb 22 '19 at 13:47
Take a look at [Tim Hall's JSON Parser](https://github.com/VBA-tools/VBA-JSON). And there are other parsers floating around. — Ron Rosenfeld, Feb 22 '19 at 13:51
@QHarr, here is the link to pastebin: `https://pastebin.com/z9w7rQtr` — Mr K, Feb 22 '19 at 13:57
The json is incomplete. It has been cut off at the end, And do you only want the displayValue values? — QHarr, Feb 22 '19 at 14:01
@QHarr, yes, end is cut cause it is quite large, but this is format in general! And yes, I would need only displayValue values — Mr K, Feb 22 '19 at 14:04

QHarr · Accepted Answer · 2019-02-22T14:48:37.607

If only after displayValue you can use the following with jsonconverter.bas. You add the .bas to your project and then VBE > Tools > References> Add a reference to Microsoft Scripting Runtime.

Option Explicit

Public Sub IMPORTMESTER()

    Dim xTOK As String
    Dim URL As String
    Dim httpREQ As Object
    Dim json As Object
    Dim xLINE As Variant

    xTOK = "token"
    URL = "https://api.smartsheet.com/2.0/sheets/7352150637471620"

    Set httpREQ = CreateObject("MSXML2.XMLHTTP.6.0")
    With httpREQ
        .Open "GET", URL, False
        .setRequestHeader "Authorization", "Bearer " & xTOK
        .setRequestHeader "Content-Type", "application/json"
        .send
    End With

    xLINE = httpREQ.responseText
    Set json = JsonConverter.ParseJson(xLINE)("rows")
    Dim item As Object, nextitem As Object, i As Long
    For Each item In json
       For Each nextitem In item("cells")
            i = i + 1
           ActiveSheet.Cells(i, 1) = nextitem("displayValue")
        Next
    Next
End Sub

The item you want is nested within the json where {} is a dictionary, and [] is a collection.

nothing returned. I did tried similar solution except I didnt use ROWS, so I thought I was wrong over there as I guess I don't have proper understanding about structure — Mr K, Feb 22 '19 at 14:15
check the immediate window with Ctrl + G. And make sure to insert your token. — QHarr, Feb 22 '19 at 14:15
And yes, now I see it. Thank you @QHarr, reallty helped it a lot! — Mr K, Feb 22 '19 at 14:32

VBA to parse data from web API

1 Answers1