I'm trying to retrieve a JSON response object through the below query API. When I try to read the responseText in VBA I receive an empty result. However, the exact same request returns correct data from PostMan. Also, the correct data returns from sending the different request bodies. Whenever I try to execute Set Json = JsonConverter.ParseJson(strResponse) and I'm getting the error message Error Parsing JSON: ^ Expecting '{' or '['. Can you please help?

This is VBA code

Dim strUrl As String
Dim reqBody As String
        
    'For search GOSS service API-Step1
    strUrl = "https://gossrepo.ins.dell.com/gossv3/api/reporting/service/getrefid"
        
    'create a method for calling HTTP services
    Set hReq = CreateObject("MSXML2.XMLHTTP")
    
        With hReq
            .Open "POST", strUrl, blnAsync, False
             reqBody = "{""methodType"":extract,""sourceApplication"":DSA,""searchParameter"":[{""conditionType"":term,""key"":global_bu_id,""value"":11},{""conditionType"":wildcard,""key"":customer_num,""value"":[530007546697]},{""conditionType"":range,""key"":order_date,""value"":[{""from"":2021-08-31,""to"":2021-09-09}]},{""conditionType"":sort,""key"":order_date_time,""value"":desc}],""pageSize"":40,""pageNum"":0}"
            .SetRequestHeader "Content-type", "application/json"
            .Send reqBody
            While hReq.ReadyState <> 4
                DoEvents
            Wend
            'wrap the response in a JSON root tag "data" to count returned objects
            strResponse = hReq.ResponseText
            Debug.Print strResponse
        
        End With
    
    
    Set Json = JsonConverter.ParseJson(strResponse)

Updated the fixed with the different post body:

Dim strUrl As String
    Dim reqBody As String
        
    'For search GOSS service API-Step1
    strUrl = "https://gossrepo.us.dell.com/gossv3/api/reporting/service/getdata"
        
    'create a method for calling HTTP services
    Set hReq = CreateObject("MSXML2.XMLHTTP")
    
        With hReq
            .Open "POST", strUrl, blnAsync, False
             reqBody = "{""methodType"":""details"",""sourceApplication"":""DSA"",""pageNum"":0,""pageSize"":300,""searchParameter"":[{""conditionType"":""term"",""key"":""global_bu_id"",""value"":""11""},{""conditionType"":""wildcard"",""key"":""customer_num"",""value"":[""" & ws & """]},{""conditionType"":""range"",""key"":""order_date"",""value"":[{""from"":""" & ws11 & """,""to"":""" & ws12 & """}]},{""conditionType"":""sort"",""key"":""order_date_time"",""value"":""desc""}]}"
            .SetRequestHeader "Content-type", "application/json"
            .Send reqBody
            While hReq.ReadyState <> 4
                DoEvents
            Wend
            'wrap the response in a JSON root tag "data" to count returned objects
            strResponse = hReq.ResponseText
            
            
        End With
    
    
    Set Json = JsonConverter.ParseJson(strResponse)

CodePudding user response：

Probably your request is wrong and you don't get the expected response because of it... Look at the status that's returned (hReq.status and hReq.statusText), I bet it's 400 Bad Request or 500 Internal Error and not 200 Ok. (You could also use an inspecting proxy like Fiddler to look at what exactly you send and receive here.)

I can already see your request body is invalid JSON as it has unquoted strings in it... It's not the exact same as you showed in Postman! That's like the issue (or one of the issues). You have e.g. "methodType": extract, but it has to be "methodType": "extract" (in VBA ""methodType"": ""extract"") - you did it correctly in Postman but wrong in your code.

CodePudding user response：

As mentioned by CherryDT - Your original reqBody had alot of missing quotes and in your updated reqBody, you are missing quotes for order_date and also you quoted pageSize and pageNum value which is supposed to be a number and thus quotes is not required:

Below should give you the same JSON string as what you had in Postman:

reqBody = "{""methodType"":""extract"",""sourceApplication"":""DSA"",""searchParameter"":[{""conditionType"":""term"",""key"":""global_bu_id"",""value"":""11""},{""conditionType"":""wildcard"",""key"":""customer_num"",""value"":[""530007546697""]},{""conditionType"":""range"",""key"":""order_date"",""value"":[{""from"":""2021-08-31"",""to"":""2021-09-09""}]},{""conditionType"":""sort"",""key"":""order_date_time"",""value"":""desc""}],""pageSize"":40,""pageNum"":0}"

One way which has been working well for me so far is:

1. Copy the JSON string from Postman to Notepad
2. Open Replace dialog (Ctrl-H)
3. Enter " in Find What
4. Enter "" in Replace with
5. Click Replace All

Now you can copied the new string back to your VBA editor and it should produce the same output as Postman's.