- Library of Rest Calls
- How to add new entries
- How to query from data

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
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