JSON API to excel

[djgenesis13]

Hello guys,

I have a json api which uses a token in the format of https://someapi.com/pubapi.php?token=XXXXXXXXXX&mode=list which returns a json result.

I get the token from https://someapi.com/pubapi.php?get_token=get_token in the form of {"token":"XXXXXXXXXX"}

Using this online tool https://json-csv.com/ I am able to translate the json results insto csv and then import the csv to an excel file but it takes a lot of time to do this repeatedly manually step by step as every 15 minutes I have to get a new token.

I want to create a vba which

1)Calls https://someapi.com/pubapi.php?get_token=get_token to get the token
2)Uses the token in the json request https://someapi.com/pubapi.php?token=XXXXXXXXXX&mode=list
3)Get the results and Insert them in a specific excel sheet of my workbook (ex. "jsonresults")

 

[djgenesis13]

Like this:

Public Sub getData()

    Dim Movie As Object
    Dim scriptControl As Object

    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://www.omdbapi.com/?t=frozen&y=&plot=short&r=json", False
        .send
        Set Movie = scriptControl.Eval("(" + .responsetext + ")")
        .abort
        With Sheets(2)
            .Cells(1, 1).Value = Movie.Title
            .Cells(1, 2).Value = Movie.Year
            .Cells(1, 3).Value = Movie.Rated
            .Cells(1, 4).Value = Movie.Released
            .Cells(1, 5).Value = Movie.Runtime
            .Cells(1, 6).Value = Movie.Director
            .Cells(1, 7).Value = Movie.Writer
            .Cells(1, 8).Value = Movie.Actors
            .Cells(1, 9).Value = Movie.Plot
            .Cells(1, 10).Value = Movie.Language
            .Cells(1, 11).Value = Movie.Country
            .Cells(1, 12).Value = Movie.imdbRating
        End With
    End With
    
End Sub

Which I quite like the syntax of, but if you want a better supported (and probably faster method), consider returning the results in xml and parsing that:
http://www.omdbapi.com/?t=frozen&y=&plot=short&r=xml

This works great and seams simpler (I am looking for something straight forward and simple). What if the json query returned multiple results. In this case multiple movies with their info with the below systax:

{"movie_results":[{"Title":"Frozen","Year":"2013","Rated":"PG","Released":"27 Nov 2013","Runtime":"102 min","Genre":"Animation, Adventure, Comedy","Director":"Chris Buck, Jennifer Lee","Writer":"Jennifer Lee (screenplay), Hans Christian Andersen (inspired by the story \"The Snow Queen\" by), Chris Buck (story), Jennifer Lee (story), Shane Morris (story), Dean Wellins (additional story)","Actors":"Kristen Bell, Idina Menzel, Jonathan Groff, Josh Gad","Plot":"When the newly crowned Queen Elsa accidentally uses her power to turn things into ice to curse her home in infinite winter, her sister, Anna, teams up with a mountain man, his playful reindeer, and a snowman to change the weather condition.","Language":"English, Icelandic","Country":"USA","Awards":"Won 2 Oscars. Another 69 wins & 55 nominations.","Poster":"http://ia.media-imdb.com/images/M/MV5BMTQ1MjQwMTE5OF5BMl5BanBnXkFtZTgwNjk3MTcyMDE@._V1_SX300.jpg","Metascore":"74","imdbRating":"7.6","imdbVotes":"372,962","imdbID":"tt2294629","Type":"movie","Response":"True"},{"Title":"James Bond","Year":"1999","Rated":"N/A","Released":"N/A","Runtime":"N/A","Genre":"N/A","Director":"Baiju Kottarakkara","Writer":"N/A","Actors":"Indrans, Janardanan, Kalabhavan Mani, Premkumar","Plot":"Five friends in hiding after their local business goes bankrupt stumble upon a baby that changes their lives.","Language":"Malayalam","Country":"India","Awards":"N/A","Poster":"N/A","Metascore":"N/A","imdbRating":"5.0","imdbVotes":"111","imdbID":"tt0255268","Type":"movie","Response":"True"},{"Title":"Lord of the Rings","Year":"1990","Rated":"N/A","Released":"N/A","Runtime":"N/A","Genre":"Adventure, Fantasy","Director":"N/A","Writer":"Scott Bennie (manual), J.R.R. Tolkien (novel)","Actors":"Robin Atkin Downes","Plot":"While Frodo and Sam, now accompanied by a new guide, continue their hopeless journey towards the land of shadow to destroy the One Ring, each member of the broken fellowship plays their ...","Language":"English","Country":"USA","Awards":"N/A","Poster":"N/A","Metascore":"N/A","imdbRating":"7.1","imdbVotes":"132","imdbID":"tt0154789","Type":"game","Response":"True"}]}

 

[Kyle123]

What's the url to get multiple results?

 

[Kyle123]

The search returns a different object, but this should give you the general idea, just loop through the results

http://www.omdbapi.com/?s=james&y=&plot=short&r=json

Public Sub getData()

    Dim Movie As Object
    Dim results As Object
    Dim scriptControl As Object
    Dim x As Long: x = 1

    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://www.omdbapi.com/?s=james&y=&plot=short&r=json", False
        .send
        Set results = scriptControl.Eval("(" + .responsetext + ")").Search
        .abort
        With Sheets(1)
            For Each Movie In results
                .Cells(x, 1).Value = Movie.Title
                .Cells(x, 2).Value = Movie.Year
                .Cells(x, 3).Value = Movie.imdbID
                .Cells(x, 4).Value = Movie.Poster
                x = x + 1
            Next Movie
        End With
    End With
    
End Sub