Getting "Run TIme Error 13: Type Mismatch" while parsing a JSON using JSONConverter

Microsoft Office 365 - Excel Macros

JSON:

{
   "expand":"schema,names",
   "startAt":0,
   "maxResults":1000,
   "total":8,
   "issues":[
      {
         "expand":"operations,versionedRepresentations,editmeta,changelog,renderedFields",
         "id":"10011",
         "self":"http://192.168.99.100:8080/rest/api/2/issue/10011",
         "key":"TES-12",
         "fields":{
            "issuetype":{

            },
            "components":[

            ],
            "timespent":null,
            "customfield_10101":[
               "com.atlassian.greenhopper.service.sprint.Sprint@1d4aa0ee[id=2,rapidViewId=1,state=FUTURE,name=Facebook March 2020 Release,startDate=2020-05-30T20:22:00.000Z,endDate=2020-06-18T20:22:00.000Z,completeDate=,activatedDate=,sequence=2,goal=]"
            ],
            "customfield_10102":"TES-1"
         }
      }
   ]
}

Code written to extract "customfield_10101" and store the values in String variable

Dim Json As Object
Set Json = JsonConverter.ParseJson(.responseText)
Dim tempName As Variant
tempName = Json("issues")(1)("fields")("customfield_10101")(1)

Getting in last line of the code

Run Time Error 13

Solution

The part of your code that does the parsing is correct. The problem seems to be that you're not passing the json correctly to the JsonConverter.ParseJson().

I am not sure whether you are getting this JSON as a response to some HTTP request that you're sending to a webpage or you just have it stored somewhere and you're just trying to parse it.

If in fact you're getting the json as a response to an HTTP request, then you need to refer to this request explicitly. So, for the sake of demonstration, I will assume there is an HTTP request object named req somewhere in your code. In that case your code should look like so:

Set Json = JsonConverter.ParseJson(req.responseText)

If you're using a With-End With block to send the request, as it's usually the case, then your code would look like this:

With req
    '.open ...
    ' ...code related to the request...
    '.send...

    Set Json = JsonConverter.ParseJson(.responseText)
End With

If there are no requests in your code and you just have the json stored somewhere as a string and you need to parse it then all you have to do is pass that string to JsonConverter.ParseJson().

So for example, if the json string is stored in cell A1 of Sheet1 all you need is this:

Dim sht As Worksheet
Dim Json As Object
Dim tempName As Variant
Set sht = ThisWorkbook.Worksheets("Sheet1")
Set Json = JsonConverter.ParseJson(sht.Range("A1").Value)
tempName = Json("issues")(1)("fields")("customfield_10101")(1)