Adding to the rest library

What can you learn here

Public versus Private
Variable visibility
External references

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


Twitter rest walkthrough

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


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


but there are some other optional behavior changing arguments.


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().


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


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


Finally we initialize a cRest class


and then execute it


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

try it yourself and see.



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


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


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

returns this response


From this we can deduce


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


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


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.


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