Quick Links

Other stuff

Site owners

  • Bruce Mcpherson

Services‎ > ‎Desktop Liberation‎ > ‎jSon‎ > ‎

Rest to Excel library

What can you learn here ?
  • Library of Rest Calls
  • Populate excel tables
  • Add your own

A library to populate Excel tables with rest query results get it now

I found myself writing similar code to deal with the growing number of Rest queries on this site such as Get Data from Google BooksIntegrating Excel with Maps and EarthGet Data From Google Docs and a whole lot of other Web Services. It occurred to me that some small enhancements of How to use cJobject and of How to use cDataSet, along with the library idea in Regular Expressions could result in a useful library and class object that could be used to process any Rest queries without having to bother with any repetitive coding in the future. 

This section introduces a new class; cRest, a new module restLibrary, and another module restLibraryExamples showing how to use these. If you are interested in the Google Apps Script version of this for Google Docs, take a look at GAS Rest Library migration

Now the rest Excel library can handle  XML as well as JSON

Here's a short slide primer for the cRest class

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, or see this blog entry for a quick summary, or get started quickly with How to populate Excel from jSon

REST explorer

You can use the Rest Results Explorer to research your targeted API and help decide whihc data fields you want to import to your worksheet.

Build it in your own project

You can find which modules and classes are needed here Modules for Rest-Excel

RESTful queries - what are they ?

Well you can read the detail here, but in simple terms, you provide a URL including a query and some xml or jSon is returned containing the response. The objective of this cRest class is to provide a simple way to do that and populate excel with the results, and of the library is to create a repository for frequently used Rest query and response details you can build into any projects that might need to retrieve such data. 

What does rest query look like

Here is a query to return  a page of tweets about excel
http://search.twitter.com/search.json?q=excel

and the beginning portion of the result (it's too long to reproduce the whole thing here) looks like
{"completed_in":0.181,"max_id":138606689405501441,"max_id_str":"138606689405501441","next_page":"?page=2&max_id=138606689405501441&q=excel","page":1,"query":"excel","refresh_url":"?since_id=138606689405501441&q=excel","results":[{"created_at":"Mon, 21 Nov 2011 13:16:24 +0000","from_user":"nycaccountjob","from_user_id":69004177,"from_user_id_str":"69004177","from_user_name":"nycaccountjob","geo":null,"id":138606689405501441,"id_str":"138606689405501441","iso_language_code":"en","metadata":

What has this to do with Excel?

Well it would be nice if you could just populate a sheet with the results of such queries without worrying about the details. The rest library and cRest class is for exactly that.

This is how simple it is
Public Sub testTwitter()
    generalQuery "tweets", "twitter", _
        InputBox(prompt:="Enter your twitter search query", _
             Title:="twitter API query:results to the tweets worksheet")
    
End Sub

This will ask you what your query is, and populate a worksheet called tweets with the results, as below

Using Rest to lookup

The previous example was the type of query that one search term produces and indeterminate number of results. These are interpreted and written to the requested sheets. Another type would be where the query data is in the datasheet, and you need a query for each line in the input table. You saw this type of query in GeoCoding and Get Data from Google Books. Implementing these same examples using the restLibrary just needs this code to populate the worksheet named geocoding, using the yahoo geocode library entry, where the input address column contains the data that needs to be geocoded

Public Sub testYahooGeocode()
    
    generalDataSetQuery "geocoding", "yahoo geocode", "input address"

End Sub

and gives this result

and a Google books example, where the isbnq worksheet is populated using the 'google books by isbn' library entry, using the isbn column as the source of the query  is

Public Sub testBooks()

    generalDataSetQuery "isbnq", "google books by isbn", "isbn"
    
End Sub

and gives this result


How does this work

Clearly the above needs some setup. 
  1. If it is a 'known query' (one already in the restLibrary), the Url for that type of query is already known so you can refer to it by its entry name. If not, then you can either add one to the library, or pass the URL as an argument. More about that later. 
  2. These queries can return a lot of data. Much of it could be irrelevant for you. To select which data you want, then you need to simply name the columns to match the names in the query reponse data. See Adding to the rest library for how to do that.
  3. In the examples given, I have provided a simplified calling interface. There are a couple of deeper levels you can go to if you want to use some of the more advanced options. More about that later too. For the moment here are the 2 generalized simple interfaces.
For the type of query where one query returns multiple rows, like the twitter example.
and where each row contains input for the query, for example geoCoding.


Each share a function for reporting results,


The entire rest library is defined as a single cJobject - here is an excerpt. You can add your own by modifying createRestLibrary. The complete code is in this Gist and below

You can see that in each case, restQuery is called with slightly different argument structure and searches the restlibrary created above..