XHR request using VBA with JSON response

Question

I am trying to fetch some data from an e-commerce fashion website. When i open a category on website I can see 48 items and a load more button in the end. When i click on that button, i see the next 48 items. What happens in backhand is, when i click that load more button an XHR post request is sent and response is returned in JSON format. I want to automate this search and capture the response data in an excel sheet. I am new to programming and not familiar with advance scripting languages so i am working on VBA. My request is getting submitted but not get the response. My category page is http://www.myntra.com/_ and the link where request is sent is http://www.myntra.com/searchws/search/styleids2. Here is my code :

    Sub post()
    Dim objHTTP As Object
    Dim result As String
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.navigate "about:blank"
    objIE.Visible = True
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "http://www.myntra.com/searchws/search/styleids2"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
   objHTTP.send ("query=(full_text_myntra:(_)AND(statusid:1008))&start=0&rows=500&facet=true&facetField=[]")
   result = objHTTP.responsetext
   objIE.document.Write result

   Set objHTTP = Nothing
  End Sub     

Answer 1

I got quite a few 415 errors when trying to run the query with Postman and it looks like the API was expecting JSON rather than form-urlencoded and the parameters needed to be wrapped in an array so I would check that in your code:

"[{""query"":""(full_text_myntra:(_)AND(statusid:1008))"",""start"":0,""rows"":500,""facet"":true,""facetField"":[]}]"

Additionally, I would recommend using something like Excel-REST (which I made for cases just like this that I was running into) to help with creating the request and handling JSON:

Dim MyntraClient As New RestClient
MyntraClient.BaseUrl = "http://www.myntra.com/"

' With inline JSON
Dim json As String
json = "[{""query"":""(full_text_myntra:(_)AND(statusid:1008))"",""start"":0,""rows"":500,""facet"":true,""facetField"":[]}]"

Dim Response As RestResponse
Set Response = MyntraClient.PostJSON("searchws/search/styleids2", json)

' It's no fun creating json string by hand, instead create it via Dictionary/Collection/Array
Dim SearchParameters As New Dictionary
SearchParameters.Add "query", "(full_text_myntra:(_)AND(statusid:1008))"
SearchParameters.Add "start", 0
SearchParameters.Add "rows", 500
SearchParameters.Add "facet", True
SearchParameters.Add "facetField", Array()

Set Response = MyntraClient.PostJSON("searchws/search/styleids2", Array(SearchParameters))

' Check status, received content, or do something with the data directly
Debug.Print Response.StatusCode
Debug.Print Response.Content
Debug.Print Response.Data("response1")("totalProductsCount")