Excel VBA macro using iTunes search API - fastest way to query and parse JSON results
I am trying to create an Excel page from iTunes request data. Angry Birds app example: my request would look like this: https://itunes.apple.com/lookup?id=343200656&country=AL check Albania iTunes https://itunes.apple.com/lookup?id=343200656&country=DZ check Algeria ITunes ... 150 more stores
My question is the most efficient way to execute this query and parse it. I know how to access the xmlhttp request. Please enlighten me as to the best way to do this. I've read some documentation for VB-JSON, Json.net, CDataSet, fastJSON but can't figure out how to get started with these tools. Does anyone have more VBA code examples, pulling JSON, or a way to explain the use of these frameworks for newb?
Dim innerHTML As Object
Dim myText As String
JsonCheck = ""
Set innerHTML = CreateObject("Microsoft.XMLHTTP")
With innerHTML
.Open "GET", iTunesAPI_link, False
.send
myText = .responsetext
End With
Set innerHTML = Nothing
If InStr(myText, ":0") = 20 Then 'no results found
result = "Down"
ElseIf InStr(myText, "Your request produced an error.") = 46 Then 'link error
result = HTMLCheck(human iTunes link)
Else 'found the app
result = call function which parses myText for desired fields
Endif
source to share
Here's a basic approach using scriptcontrol:
Sub Tester()
Dim json As String
Dim sc As Object
Dim o
Set sc = CreateObject("scriptcontrol")
sc.Language = "JScript"
json = HttpGet("https://itunes.apple.com/lookup?id=343200656&country=AL")
'some json property names may be keywords in VBA, so replace with
' something similar....
json = Replace(json, """description""", """description_r""")
Debug.Print json
sc.Eval "var obj=(" & json & ")" 'evaluate the json response
'add some accessor functions
sc.AddCode "function getResultCount(){return obj.resultCount;}"
sc.AddCode "function getResult(i){return obj.results[i];}"
Debug.Print sc.Run("getResultCount")
Set o = sc.Run("getResult", 0)
Debug.Print o.kind, o.features, o.description_r
End Sub
Function HttpGet(url As String) As String
Dim oHTML As Object
Set oHTML = CreateObject("Microsoft.XMLHTTP")
With oHTML
.Open "GET", url, False
.send
HttpGet = .responsetext
End With
End Function
Codo has a proven approach: Excel VBA: Parsed JSON Object Loop
source to share
I had a similar problem with a Salesforce REST API request and found out that working with JSON through ScriptControl
was unmanageable. I used the following library to parse and convert to JSON and it worked great for me: https://code.google.com/p/vba-json/ .
Dim JSON As New JSONLib
Dim Parsed As Object
Set Parsed = JSON.parse(jsonValue)
Debug.Print Parsed("resultCount")
Debug.Print Parsed("results")(0)
Using this library, I then wrapped up some common functionality for making web requests that I think will help you: https://github.com/timhall/Excel-REST
Using these libraries your code will look something like this:
Dim iTunesClient As New RestClient
iTunesClient.BaseUrl = "https://itunes.apple.com/"
Dim Request As New RestRequest
Request.Format = json
Request.Resource = "lookup"
Request.AddQuerystringParam "id", "343200656"
Request.AddQuerystringParam "country", "AL"
Dim Response As RestResponse
Set Response = iTunesClient.Execute(Request)
' => GET https://itunes.apple.com/lookup?id=343200656&country=AL
If Response.StatusCode = 200 Then
' Response.Data contains converted JSON Dictionary/Collection
Debug.Print "Result Count: " & Response.Data("resultCount")
Dim i As Integer
For i = LBound(Response.Data("results")) To UBound(Response.Data("results"))
Debug.Print "Result " & i & ": " & Response.Data("results")(i)
Next i
Else
Debug.Print "Error: " & Response.Content
End If
source to share