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 Books, Integrating Excel with Maps and Earth, Get 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
http://www.slideshare.net/slideshow/embed_code/24023223
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 testBooks() generalDataSetQuery "isbnq", "google books by isbn", "isbn" End Sub
and gives this result
How does this work
Clearly the above needs some setup.
- 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.
- 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.
- 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.
Public Function generalQuery(sheetName As String, _ libEntry As String, queryString As String, _ Optional breport As Boolean = True, _ Optional queryCanBeBlank As Boolean = False, _ Optional appendQuery As String = vbNullString) As cRest Set generalQuery = generalReport( _ restQuery(sheetName, libEntry, queryString, , , , , , , , queryCanBeBlank, , , , , , appendQuery), breport) End Function
and where each row contains input for the query, for example geoCoding.
Public Function generalDataSetQuery(sheetName As String, _ libEntry As String, colName As String, _ Optional breport As Boolean = True, _ Optional queryCanBeBlank As Boolean = False, _ Optional appendQuery As String = vbNullString, _ Optional collectionNeeded As Boolean = True) As cRest Set generalDataSetQuery = generalReport( _ restQuery(sheetName, libEntry, , colName, _ , , , , , , , , , , , , appendQuery, collectionNeeded), breport) End Function
Each share a function for reporting results,
Public Function generalReport(cr As cRest, breport As Boolean) As cRest If cr Is Nothing Then MsgBox ("failed to get any data") Else If breport Then MsgBox (cr.jObjects.Count & " items retrieved ") End If End If Set generalReport = cr End Function
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
Public Function restQuery(Optional sheetName As String = vbNullString, _ 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, _ Optional queryCanBeBlank As Boolean = False, _ Optional sFix As String = vbNullString, _ Optional user As String = vbNullString, _ Optional pass As String = vbNullString, _ Optional append As Boolean = False, _ Optional stampQuery As String = vbNullString, _ Optional appendQuery As String = vbNullString, _ Optional collectionNeeded As Boolean = True, _ Optional postData As String = vbNullString, _ Optional resultsFormat As erResultsFormat = erUnknown) As cRest ' give it a known name, and somewhere to put the result ' in the case where 1 query returns multiple rows, sQuery is the query contents ' where 1 column contains the query for each row, sQueryColumn contains the name of the column Dim qType As erRestType, sUrl As String, sResults As String, sEntryType As erRestType, sc As cCell Dim dset As cDataSet, cr As cRest, sIgnore As String, cj As cJobject, cEntry As cJobject, job As cJobject Dim libAppend As String, _ libAccept As String, bWire As Boolean, crIndirect As cRest, _ rPlace As Range, bAlwaysEncode As Boolean, timeout As Long, oa As cOauth2 libAppend = vbNullString libAccept = vbNullString Dim UA As cUAMeasure Set UA = registerUA("restQuery_" & sEntry) timeout = 0 ' this is now a library object Set cEntry = getRestLibrary() If Not (sQuery = vbNullString Xor sQueryColumn = vbNullString) Then If Not queryCanBeBlank Then MsgBox ("you must provide one of either query contents or a query column name") Exit Function End If End If If Not (sEntry = vbNullString Xor sRestUrl = vbNullString) Then MsgBox ("you must provide one of either a known library entry or a rest URL") Exit Function End If ' based on whether a column name or a query argument was supplied If sQuery = vbNullString And Not queryCanBeBlank Then qType = erQueryPerRow Else qType = erSingleQuery End If ' get the characteristics from the crest library If sEntry = vbNullString Then sUrl = sRestUrl sResults = sResponseResults Set cj = New cJobject Else Set cj = cEntry.childExists(sEntry) If (cj Is Nothing) Then MsgBox (sEntry & " is not a known library entry") Exit Function End If sEntryType = cj.child("restType").toString sUrl = cj.child("url").toString sResults = cj.child("results").toString bTreeSearch = cj.child("treeSearch").toString = "True" sIgnore = cj.child("ignore").toString bAlwaysEncode = False If Not cj.childExists("timeout") Is Nothing Then timeout = cj.child("timeout").value If Not cj.childExists("alwaysEncode") Is Nothing Then bAlwaysEncode = cj.child("alwaysEncode").value If Not cj.childExists("append") Is Nothing Then libAppend = cj.child("append").toString If Not cj.childExists("accept") Is Nothing Then libAccept = cj.child("accept").toString If Not cj.childExists("wire") Is Nothing Then bWire = cj.child("wire").value If resultsFormat = erUnknown And _ Not cj.childExists("resultsFormat") Is Nothing Then resultsFormat = cj.child("resultsFormat").value If Not cj.childExists("indirect") Is Nothing Then If cj.child("indirect").toString <> vbNullString Then ' now need to go off and execute that indirection - this could be recursive Set crIndirect = restQuery("", cj.child("indirect").toString, sEntry, , , , False) If crIndirect Is Nothing Then Exit Function sUrl = crIndirect.jObject.children("results").child("1.mystuff.publish").toString & sUrl End If End If If complain Then If abandonType(sEntry, qType, sEntryType) Then Exit Function End If End If If resultsFormat = erUnknown Then resultsFormat = erJSON Set cr = New cRest ' first we need to do oauth if its needed Set job = cj.childExists("authtype") If Not job Is Nothing And sFix = vbNullString Then If job.value = erOAUTH2 Then ' need to authorize and get token Set oa = getGoogled(cj.child("authScope").value) If (oa Is Nothing) Then Exit Function Else MsgBox ("Dont understand authtype " & CStr(job.value)) Exit Function End If End If ' lets get the data Application.Calculation = xlCalculationManual If (sheetName <> vbNullString) Then Set dset = New cDataSet If (InStr(1, sheetName, "!") > 0) Then Set rPlace = Range(sheetName) Else Set rPlace = wholeSheet(sheetName) End If If (IsEmpty(rPlace.Cells(1, 1))) Then rPlace.Cells(1, 1).value = "crest" With dset.populateData(toEmptyBox(rPlace)) ' ensure that the query column exists if it was asked for If qType = erQueryPerRow Then If Not .headingRow.validate(True, sQueryColumn) Then Exit Function End If If stampQuery <> vbNullString Then If Not .headingRow.validate(True, stampQuery) Then Exit Function Set sc = .headingRow.exists(stampQuery) End If ' alsmost there Set cr = cr.init(sResults, qType, .headingRow.exists(sQueryColumn), _ , dset, bPopulate, sUrl, bClearMissing, _ bTreeSearch, complain, sIgnore, user, pass, append, sc, _ libAppend & appendQuery, libAccept, bWire, collectionNeeded, _ bAlwaysEncode, timeout, postData, resultsFormat, oa) End With Else Set cr = cr.init(sResults, qType, , _ , , False, sUrl, , _ bTreeSearch, complain, sIgnore, user, pass, append, sc, _ libAppend & appendQuery, libAccept, bWire, collectionNeeded, _ bAlwaysEncode, timeout, postData, resultsFormat, oa) End If If cr Is Nothing Then If complain Then MsgBox ("failed to initialize a rest class") Else Set cr = cr.execute(sQuery, sFix, complain) If cr Is Nothing Then If complain Then MsgBox ("failed to execute " & sQuery) Else Set restQuery = cr End If End If UA.postAppKill.tearDown Application.Calculation = xlCalculationAutomatic End Function
For more detail on this topic
and also here
Summary
There is a lot more discussion to be had on this topic. In the meantime why not join our forum to ensure you get updates when they are available. You can also submit anything you want considered for publication on this site (for example if you add interesting Rest calls to the restLibrary) to our forum. Now let’s look at Adding to the rest library