VBA: count items in JSON response string / trello get Cards count in a List

Question

I'm using VBA-web (https://vba-tools.github.io/VBA-Web/) to access trello api, to get cards in a list

My function looks like that:
Public Function CountCardsinList(ListId As String) As Integer
    WebHelpers.EnableLogging = False


    Dim TrelloClient As New WebClient
    TrelloClient.BaseUrl = "https://api.trello.com/1/"

    Dim Request As New WebRequest
    Request.Format = WebFormat.Json
    Request.ResponseFormat = Json

    Request.Resource = "lists/{ListId}/cards"

    Request.AddUrlSegment "ListId", ListId

    Request.AddQuerystringParam "key", TrelloAPIKey
    Request.AddQuerystringParam "token", TrelloAPIToken
    Request.AddQuerystringParam "filter", "open"


    Dim Response As WebResponse
    Set Response = TrelloClient.Execute(Request)

    If Response.StatusCode = WebStatusCode.Ok Then
    Debug.Print Response.Content        '


    'Response.Data("idList").Count

        Debug.Print "CountCardsinList =>>> " & Response.Content
        CountCardsinList = Response.Data("idList").Count

    Else
        MsgBox Response.StatusDescription, vbCritical, "Error " &                   Response.StatusCode
        CountCardsinList = ""

    End If

    Debug.Print "CountCardsinList =>>> " & Response.Content
    'Set CountCardsinList = Request


End Function

I receive correct JSON reply from the api:

[{
    "id": "584e798dd570ae187b293e5b",
    "checkItemStates": null,
    "closed": false,
    "dateLastActivity": "2016-12-30T09:24:57.531Z",
    "desc": "",
    "descData": null,
    "idBoard": "57873ba94794058756fa0a96",
    "idList": "57873bb3a725f734089702b2",
    "idMembersVoted": [],
    "idShort": 90,
    "idAttachmentCover": null,
    "manualCoverAttachment": false,
    "idLabels": ["57873ba984e677fd3683bef8"],
    "name": "card name / other stuff",
    "pos": 1999.9923706054688,
    "shortLink": "izoqvWJk",
    "badges": {
        "votes": 0,
        "viewingMemberVoted": false,
        "subscribed": false,
        "fogbugz": "",
        "checkItems": 0,
        "checkItemsChecked": 0,
        "comments": 0,
        "attachments": 0,
        "description": false,
        "due": "2016-12-26T11:00:00.000Z",
        "dueComplete": false
    },
    "dueComplete": false,
    "due": "2016-12-26T11:00:00.000Z",
    "idChecklists": [],
    "idMembers": ["54f0cc079bf18f2798dda8bd"],
    "labels": [{
        "id": "57873ba984e677fd3683bef8",
        "idBoard": "57873ba94794058756fa0a96",
        "name": "Urgent",
        "color": "red",
        "uses": 14
    }],
    "shortUrl": "https://trello.com/c/vfvfdvdfv",
    "subscribed": false,
    "url": "https://trello.com/c/fdvfdvdfv/cfvdfv"
},
{
    "id": "5832c2fa7f55fe5637d972ea",
    "checkItemStates": null,
    "closed": false,
    "dateLastActivity": "2016-12-30T09:25:09.222Z",
    "desc": "",
    "descData": null,
    "idBoard": "57873ba94794058756fa0a96",
    "idList": "57873bb3a725f734089702b2",
    "idMembersVoted": [],
    "idShort": 80,
    "idAttachmentCover": null,
    "manualCoverAttachment": false,
    "idLabels": ["57873ba984e677fd3683bef6"],
    "name": "other card name",
    "pos": 2023.9922790527344,
    "shortLink": "XhUPgcsD",
    "badges": {
        "votes": 0,
        "viewingMemberVoted": false,
        "subscribed": false,
        "fogbugz": "",
        "checkItems": 0,
        "checkItemsChecked": 0,
        "comments": 0,
        "attachments": 0,
        "description": false,
        "due": "2016-12-30T15:00:00.000Z",
        "dueComplete": false
    },
    "dueComplete": false,
    "due": "2016-12-30T15:00:00.000Z",
    "idChecklists": [],
    "idMembers": ["54fdbe1a8ecdf184596c7c07"],
    "labels": [{
        "id": "57873ba984e677fd3683bef6",
        "idBoard": "57873ba94794058756fa0a96",
        "name": "Medium",
        "color": "yellow",
        "uses": 1
    }],
    "shortUrl": "https://trello.com/c/XhdfvdfvUPgcsD",
    "subscribed": false,
    "url": "https://trello.com/c/XhUPgcsfdvdffvD/
"

But I cannot correctly count idList -> and I'm trying to get number of cards in a list, by using Response.Data("idList").Count

Any information how to do it proper way? or which is the best way to parse JSON data?

Answer 1

General:

Your JSON isn't properly closed. I added }] to the end to close and placed in cell A1 of activesheet (as I don't have the API info). I then read that in from the cell as if it were response text.

---

Process:

I then used JSONConverter to parse this string from the sheet. This requires you to also add a reference to Microsoft Scripting Runtime via VBE > Tools > References.

The returned object is a collection of dictionaries. I test each dictionary for the existence of an idList key and if present add 1 to the variable itemCount, which keeps track of how many idLists there are.

---

Code:

Public Sub GetInfoFromSheet()
    Dim jsonStr As String, item As Object, json As Object, itemCount As Long
    jsonStr = [A1]
    Set json = JsonConverter.ParseJson(jsonStr)

    For Each item In json                        'collection
        If item.Exists("idList") Then itemCount = itemCount + 1
    Next item
    Debug.Print "idList count: " & itemCount
End Sub