What can you learn here?
- 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
<pre>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</pre>
Twitter rest walkthrough
There are 2 types of queries current implemented, as illustrated in the following code
<pre>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</pre>
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
<pre>Dim cr As New cRest
Set cr = restQuery(sheetName, libEntry, queryString)</pre>
but there are some other optional behavior changing arguments.
<pre>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</pre>
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().
<pre>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</pre>
since this is held as a cJObject you can serialize that back as jSon.
<pre>?createRestLibrary().child("Twitter").serialize(True)
{
"twitter":{
"restType":"1",
"url":"http://search.twitter.com/search.json?q=",
"results":"results",
"treeSearch":"False",
"ignore":""
}
}</pre>
so this function will warn of any request change to that default behavior and abandon if it’s some kind of calling error.
<pre>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</pre>
Finally we initialize a cRest class
<pre>Set cr = New cRest
Set cr = cr.init(sResults, qType, .HeadingRow.Exists(sQueryColumn), _
, dSet, bPopulate, sUrl, bClearMissing, bTreeSearch, complain)</pre>
and then execute it
<pre>Set cr = cr.execute(sQuery)</pre>
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
<pre>{"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":"<a href="http://twitter.com/">web</a>","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</pre>
From this we can deduce that the data we need are in this kind of structure
<pre>"results":[ {} , {} , {} ... ]</pre>
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
<pre>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</pre>
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
<pre>{"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}]}}</pre>
From this we can deduce
<pre>sResults = "resultset.results"
bTreeSearch = True</pre>
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
<pre>resultset.results</pre>
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
<pre>resultset.results.1.latitude.</pre>
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