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)

^^ 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
– QHarr, Commented Feb 22, 2019 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
– Mr K, Commented Feb 22, 2019 at 13:45
^ well you could provide us with something more descriptive than no luck. — QHarr
– QHarr, Commented Feb 22, 2019 at 13:47
Take a look at Tim Hall's JSON Parser. And there are other parsers floating around. — Ron Rosenfeld
– Ron Rosenfeld, Commented Feb 22, 2019 at 13:51

QHarr · Accepted Answer · 2019-02-22 14:48:37Z

2

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.

edited Feb 22, 2019 at 14:48

answered Feb 22, 2019 at 14:09

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr K Over a year ago

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

QHarr Over a year ago

check the immediate window with Ctrl + G. And make sure to insert your token.

Mr K Over a year ago

And yes, now I see it. Thank you @QHarr, reallty helped it a lot!

Collectives™ on Stack Overflow

VBA to parse data from web API

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related