Populating sheets with Rest reponses

What can you learn here ?
  • Using cDataSet
  • Using cRest
  • Using cJobject

Putting the Rest response on the sheets get it now

Now that we have the tools to generate existing  or adhoc rest queries, let's look at how the data gets into the given excel Sheet.

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.

Use DataSets

As in most of the projects on this site we use Data Manipulation Classes to abstract the data from the Excel structure, and How to use cJobject will come in handy to understand the concept of dealing with jSon.  All of these, including the Rest to Excel library are implemented inside the cDataset.xlsm downloadable workbook, along with examples of their use.

Walkthrough of populating sheet and executing rest queries.

Here is the code executed by the restQuery procedure to deal set up the abstraction and execute the query

    ' lets get the data
    Set dSet = New cDataSet
    With dSet.populateData(wholeSheet(sName), , sName, , , , True)
        ' 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
        ' alsmost there
        Set cr = New cRest
        Set cr = cr.init(sResults, qType, .HeadingRow.Exists(sQueryColumn), _
                    , dSet, bPopulate, sUrl, bClearMissing, bTreeSearch, complain)
        If cr Is Nothing Then
            If complain Then MsgBox ("failed to initialize a rest class")
        Else
            Set cr = cr.execute(sQuery)
            If cr Is Nothing Then
                If complain Then MsgBox ("failed to execute " & sQuery)
            Else
                Set restQuery = cr
            End If
        End If
    End With

Create a new dataset based on the sheetName
 With dSet.populateData(wholeSheet(sName), , sName, , , , True)

Make sure that the the given queryColumn exists
        If qType = erQueryPerRow Then
            If Not .HeadingRow.Validate(True, sQueryColumn) Then Exit Function
        End If

Use the cRest class to setup a query, or a series of queries based on the parameters from the rest library, and given as arguments
        Set cr = New cRest
        Set cr = cr.init(sResults, qType, .HeadingRow.Exists(sQueryColumn), _
                    , dSet, bPopulate, sUrl, bClearMissing, bTreeSearch, complain)

Execute that query against the dataSet, and fill it up
        If cr Is Nothing Then
            If complain Then MsgBox ("failed to initialize a rest class")
        Else
            Set cr = cr.execute(sQuery)
            If cr Is Nothing Then
                If complain Then MsgBox ("failed to execute " & sQuery)
            Else
                Set restQuery = cr
            End If
        End If

Summary

The use of Data Manipulation Classes eliminates that messy business of working with Excel Ranges and so on, and since the column headings are set up to match the required fields in the rest query response, there is not much to do here. The real work happens in the cRest class.

In the meantime why not join our forum, follow the blog or follow me on twitter 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 Advanced rest library usage





Comments