Populating sheets with Rest responses

Page Content hide

1 What can you learn here?

2 Putting the Rest response on the sheets get it now

3 Use DataSets

4 Summary

What can you learn here?

Using cDataSet
Using cRest
Using cJobject

What can you learn here

Public versus Private
Variable visibility
External references

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

<pre>' 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</pre>

Create a new dataset based on the sheetName

<pre>With dSet.populateData(wholeSheet(sName), , sName, , , , True)</pre>

Make sure that the the given queryColumn exists

<pre>If qType = erQueryPerRow Then
            If Not .HeadingRow.Validate(True, sQueryColumn) Then Exit Function
        End If</pre>

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

<pre>Set cr = New cRest
        Set cr = cr.init(sResults, qType, .HeadingRow.Exists(sQueryColumn), _
                    , dSet, bPopulate, sUrl, bClearMissing, bTreeSearch, complain)</pre>

Execute that query against the dataSet, and fill it up

<pre> 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</pre>

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 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