Rest to Excel library

Page Content hide
1 What can you learn here?
2 A library to populate Excel tables with rest query results get it now
3 Here’s a short slide primer for the cRest class
4 Try it out first ?
5 REST explorer
6 Build it in your own project
7 RESTful queries – what are they ?
8 What does rest query look like
9 What has this to do with Excel?
10 Using Rest to lookup
10.1 and gives this result
10.2 How does this work
10.3 For more detail on this topic
10.4 Using cRest directly
10.5 Populating sheets with Rest responses
10.6 Oauth2 and Excel-Rest library
10.7 Making excel tables of the results
10.8 JSON arrays of objects
10.9 Getting data out of scraperWiki
10.10 Fusion data to Excel
10.11 Combining multiple rest queries
10.12 Advanced rest library usage
10.13 Adhoc Rest Queries
10.14 Basic HTTP Authentication from VBA
10.15 Google apps script Rest Library migration
10.16 Summary

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.

  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.

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