Adding to the rest library

Adding more content to the rest library get it now

Now that we have a generalized mechanism to easily populate Excel tables from Rest queries in Rest to Excel library, let’s take a look at extending the known list of rest apis, or make one off rest calls.
Try it out first ?These are enhancements to Data Manipulation Classes so they have been included in cDataSet.xlsm, which is downloadable from Download Complete Projects.

Library structure

The ‘library is actually just a few lines of code in the restLibrary module that define how to deal with a given rest request and its response. Here are some entries

Public Function createRestLibrary() As cJobject
    ' this creates the restlibrary as a jSon object
    Dim cj As cJobject
    Set cj = New cJobject
    cj.init Nothing, "restLibrary"
    
    With cj
        With .add("google patents")
            .add "restType", erSingleQuery
            .add "url", "https://ajax.googleapis.com/ajax/services/search/patent?v=1.0&rsz=8&q="
            .add "results", "responseData.results"
            .add "treeSearch", False
            .add "ignore", vbNullString
        End With
        With .add("twitter")
            .add "restType", erSingleQuery
            .add "url", "http://search.twitter.com/search.json?q="
            .add "results", "results"
            .add "treeSearch", False
            .add "ignore", vbNullString
        End With
        With .add("google books by isbn")
            .add "restType", erQueryPerRow
            .add "url", "https://www.googleapis.com/books/v1/volumes?q=isbn:"
            .add "results", "Items"
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With
        With .add("yahoo geocode")
            .add "restType", erQueryPerRow
            .add "url", "http://where.yahooapis.com/geocode?flags=J&location="
            .add "results", "resultset.results"
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With
        With .add("imdb by title")
            .add "restType", erQueryPerRow
            .add "url", "http://www.imdbapi.com/?tomatoes=true&t="
            .add "results", vbNullString
            .add "treeSearch", False
            .add "ignore", vbNullString
        End With
        With .add("itunes movie")
            .add "restType", erSingleQuery
            .add "url", "http://itunes.apple.com/search?entity=movie&media=movie&term="
            .add "results", "results"
            .add "treeSearch", False
            .add "ignore", vbNullString
        End With
        With .add("google finance")
            .add "restType", erQueryPerRow
            .add "url", "http://www.google.com/finance/info?infotype=infoquoteall&q="
            .add "results", "crest"
            .add "treeSearch", True
            .add "ignore", vbLf & "//"
        End With
        With .add("whatthetrend")
            .add "restType", erSingleQuery
            .add "url", "http://api.whatthetrend.com/api/v2/trends.json"
            .add "results", "trends"
            .add "treeSearch", False
            .add "ignore", vbNullString
        End With
        With .add("tweetsentiments")
            .add "restType", erQueryPerRow
            .add "url", "http://data.tweetsentiments.com:8080/api/analyze.json?q="
            .add "results", "sentiment"
            .add "treeSearch", False
            .add "ignore", vbNullString
        End With
        With .add("topsy histogram")
            .add "restType", erQueryPerRow
            .add "url", "http://otter.topsy.com/searchhistogram.json?period=30&q="
            .add "results", "response"
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With
        With .add("topsy count")
            .add "restType", erQueryPerRow
            .add "url", "http://otter.topsy.com/searchcount.json?q="
            .add "results", "response"
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With
        With .add("tweetsentiment topics")
            .add "restType", erQueryPerRow
            .add "url", "http://data.tweetsentiments.com:8080/api/search.json?topic="
            .add "results", ""
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With
        With .add("tweetsentiment details")
            .add "restType", erSingleQuery
            .add "url", "http://data.tweetsentiments.com:8080/api/search.json?topic="
            .add "results", "results"
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With

            
    End With
    Set createRestLibrary = cj

End Function

Twitter rest walkthrough

There are 2 types of queries current implemented, as illustrated in the following code

Private Function whichType(t As erRestType) As String
    Select Case t
        Case erSingleQuery
            whichType = " single query that can return multiple rows"
        Case erQueryPerRow
            whichType = " a single column provides the query data for each row"
        Case Default
            Debug.Assert False
    End Select
End Function

The twitter example is of type erSingleQuery which means that a single query will return multiple rows that will populate the designated sheet. It is possible, but unlikely, to change the behavior of a given library entry through arguments to the restquery function. A typical call would be

Dim cr As New cRest
    Set cr = restQuery(sheetName, libEntry, queryString)

but there are some other optional behavior changing arguments.

Public Function restQuery(sName As String, _
                    Optional sEntry As String = vbNullString, _
                    Optional sQuery As String = vbNullString, _
                    Optional sQueryColumn As String = vbNullString, _
                    Optional sRestUrl As String = vbNullString, _
                    Optional sResponseResults As String = vbNullString, _
                    Optional bTreeSearch As Boolean = True, _
                    Optional bPopulate As Boolean = True, _
                    Optional bClearMissing As Boolean = True, _
                    Optional complain As Boolean = True _
                    ) As cRest

So the first step is to set the default behavior for the query type, and check that any behavior changes asked for were meant, and setup where to find the API and deal with the response. 

This is saying that the twitter case is a singleQuery, and setting the URL of the rest query ( you can find this by searching around), that the data portion of the response can be found in the branches under ‘results’, and that any field names requested to be extracted can be found at the top level (in other words don’t go searching down a deeper level). We’ll look at a response format shortly.

To add something to the library, you only need to add a few lines like this to createRestLibrary().

With .add("twitter")
            .add "restType", erSingleQuery
            .add "url", "http://search.twitter.com/search.json?q="
            .add "results", "results"
            .add "treeSearch", False
            .add "ignore", vbNullString
        End With

since this is held as a cJObject you can serialize that back as jSon.

?createRestLibrary().child("Twitter").serialize(True)
  {
   "twitter":{
      "restType":"1",
      "url":"http://search.twitter.com/search.json?q=",
      "results":"results",
      "treeSearch":"False",
      "ignore":""
   }
 }

so this function will warn of any request change to that default behavior and abandon if it’s some kind of calling error.

Private Function abandonType(sEntry, qType As erRestType, targetType As erRestType) As Boolean
    If qType <> targetType Then
        abandonType = Not (vbYes = MsgBox(sEntry & " is normally " & _
                whichType(targetType) & _
                " but you have specified " & _
                whichType(qType) & ": try anyway?", vbYesNo))
    Else
        abandonType = False
    End If
End Function

Finally we initialize a cRest class

Set cr = New cRest
        Set cr = cr.init(sResults, qType, .HeadingRow.Exists(sQueryColumn), _
                    , dSet, bPopulate, sUrl, bClearMissing, bTreeSearch, complain)

and then execute it

Set cr = cr.execute(sQuery)

Finding out the available fields in a response

If you have correctly identifed the Rest URL, you can just enter it into a browser with a test query and you will get some result.

Obviously you can search around for the API documentation, but it usually only takes a few moments to figure out which columns you need, and how to find them.

Here is a snippet of the response to http://search.twitter.com/search.json?q=excel

try it yourself and see.

Response

{"completed_in":0.087,"max_id":138696554059010048,"max_id_str":"138696554059010048","next_page":"?page=2&max_id=138696554059010048&q=excel","page":1,"query":"excel","refresh_url":"?since_id=138696554059010048&q=excel","results":[{"created_at":"Mon, 21 Nov 2011 19:13:30 +0000","from_user":"DaspetArancha","from_user_id":308114420,"from_user_id_str":"308114420","from_user_name":"Arancha Daspet","geo":null,"id":138696554059010048,"id_str":"138696554059010048","iso_language_code":"es","metadata":{"result_type":"recent"},"profile_image_url":"http://a3.twimg.com/profile_images/1641772688/image__66__normal.jpg","source":"web","text":"Esto del nuevo gobierno es un asco... no dejo de rechazar ofertas de trabajo y sigo sin entender el Excel !!! #necesitoayudainformatica","to_user_id":null,"to_user_id_str":null},{"created_at":"Mon, 21 Nov 2011 19:13:08 +0000","from_user":"AllenDiirks","from_user_id":393955623,"from_user_id_str":"393955623","from_user_name":"Allen Diirks","geo":null,"id

From this we can deduce that the data we need are in this kind of structure

"results":[ {} , {} , {} ... ]

This is a very simple, flat response, so all data items can be found at the first level. For something more complicated we can set bTreeSearch = True , which will indicate the the data item could be somewhere deeper in the tree. In our example we have created an Excel sheet with names corresponding to the data fields we want to extract.

Setting column names that correspond to the field names in the response,will indicate that you want those filled up. Any names not recognized will be ignored. This means that you can use this code to partially fill in a table.

Yahoo Geocode walkthrough

Elsewhere on this site I covered more specific ways of geocoding for each of Ovi, Google, Yahoo and Bing. Now that I have it, I could have used this Rest library to do this, and I may go back and update those at a future date. The library entry for yahoo geocode looks like this

With .add("yahoo geocode")
            .add "restType", erQueryPerRow
            .add "url", "http://where.yahooapis.com/geocode?flags=J&location="
            .add "results", "resultset.results"
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With

The main difference between this and the twitter example, is that we are going to use each row in our input dataset as a separate query, picking up the query data from the contents of the sheet. The input sheet looks like this

I have reused some test data from one of the Data Driven Mapping applications examples, and selected a few items from the yahoo response for columns to populate.

This Yahoo query http://where.yahooapis.com/geocode?flags=J&location=buckingham%20palace

returns this response

{"ResultSet":{"version":"1.0","Error":0,"ErrorMessage":"No error","Locale":"us_US","Quality":90,"Found":1,"Results":[{"quality":90,"latitude":"51.501171","longitude":"-0.142460","offsetlat":"51.501171","offsetlon":"-0.142460","radius":100,"name":"Buckingham Palace","line1":"Buckingham Palace","line2":"London","line3":"SW1A 1","line4":"United Kingdom","house":"","street":"","xstreet":"","unittype":"","unit":"","postal":"SW1A 1","neighborhood":"","city":"London","county":"Greater London","state":"England","country":"United Kingdom","countrycode":"GB","statecode":"ENG","countycode":"LND","uzip":"SW1A 1","hash":"","woeid":22474234,"woetype":20}]}}

From this we can deduce

sResults = "resultset.results"
            bTreeSearch = True

The Yahoo response provides an overall envelop of resultset, and within that, the actual results are in another envelope called results. Together this gives us where to find the data, namely

resultset.results

Since resultset.results is itself an array (because ambiguous queries can result in multiple results), then the field latitude, for example, is not actually at the top level (resultset.results.latitude), but would need an array reference such as

resultset.results.1.latitude.

All this unpleasantness can easily be avoided by just setting bTreeSearch = True since this will find ‘latitude’ wherever it is, and we get this result, all nicely geocoded.

Summary

Adding your own frequently used rest queries is pretty straightforward then. All you have to do is

• find the Rest URl , try it out, and understand what the response looks like
• think of a name
• add it to the restLibrary module along with the 4 lines of code you can tweak from other entries
• Why not submit it to our forum for inclusion in the library so that others can benefit from your research.

Now let’s look at Adhoc Rest Queries or continue reading about Rest to Excel Library here