How to populate Excel from jSon

As covered in jSon there are many ways to get jSon data into excel. You may be tempted to try to use Excel JSON conversion or get data from google docs. These techniques are for very specific use cases – with the jSon in a specific format for transferring between sheets.
Normally, the reason you are using jSon in the first place is because you are getting data from a web service of some sort. This means you will want to be using Rest to Excel library. Here are the steps involved in doing that

Quick start

The Rest to Excel library provides 2 different kinds of population operations (see Adding to the rest library). For this quick start we are going to look at “singleQuery”. This is where a single query leads to the population of multiple rows. For simplicity in this section, I will deal only with data that is an array of key/value pairs, one level deep, (for more complicated scenarios see  Adding to the rest library you will find all you need in the restLibrary and restLibraryExamples modules in the downloadable cDataSet.xlsm

Do a query, populate a sheet. 

This is the most likely scenario. You want to do a rest query, and populate a sheet with the resultant jSON. 

• Create an entry in the restlibrary. It will look something like this. You will need to tweak the URL and results parameters at least. In the example below, the portion of jSON containing results will have something like this { … ‘fooson’:[ .. data … ] }, and you will probably pass some variable parameter to this library entry

With .add("foobar")
            .add "restType", erSingleQuery
            .add "url", "http://somehere.com/someURL?someparameter="
            .add "results", "fooson"
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With

• Create an empty sheet, in this case “fooSheet” and put some column headings matching the name of the json fields you want to extract. Only these fields will be populated.
• Run this code (note that .tearDown mehod is quite new – it just cleans up after the query – so if you are running an old version of cDataSet, it might be worthwhile downloading the latest

generalQuery("foosheet", "foobar", "somequeryparameter", True, True).tearDown

Populate a sheet when you already have the jSon

Now let’s look at the case where you already have the jSon from somewhere, and you want to populate a sheet. Follow all the same instructions as if you were going to do a query for creating a sheet with column names and adding an entry to the rest library. The “url” doesn’t even need to be valid if you are never going to execute it. 

Let’s say your jSon data looks like this

  testString = "{'fooson':" & _
"[ {'name':'first', 'id':0, 'decimal':7.8901, 'string':'foobar', 'date':'02/07/2013'}," & _
"{'name':'second', 'id':1, 'string':'bob', 'date':'12/25/2013', 'decimal':9.87654 }," & _
"{'name':'third', 'id':2, 'string':'snafu', 'decimal':1.2345, 'date':'07/04/2013'}," & _
"{'name':'fourth', 'id':3, 'date':'12/25/2012'} ]}"

Your sheet will be populated by executing the code below. In this case, no query is executed – the JSON string is passed through and processed as if it has been returned by a query

restQuery("foosheet", "foobar", , , , , , , , , True, testString).tearDown

Manually populating a sheet

In this case, we want to manually take care of populating the sheet. Whether the data came from a query or from some already known jSon it doesn’t really matter. In this example, we don’t need to pre-populate the names of the required columns in the output sheet, since we are just going to take all the data and get the data headers from the keys of the data. 

Dim cj As cJobject, rout As Range, ds As cDataSet, ca As cJobject, job As cJobject
    With restQuery("foosheet", "foobar", , , , , , False, , , True, testString)
        ' clear the sheet
        wholeSheet("foosheet").Cells.ClearContents
        ' add the headings by getting every single key in the top level array
        For Each job In .datajObject.children
            For Each cj In job.children

                    ' if we dont already have this heading then add it
                    If rout Is Nothing Then
                        Set rout = firstCell(wholeSheet("foosheet"))
                        rout.value = cj.key
                    Else
                        If (cleanFind(makeKey(cj.key), rout) Is Nothing) Then
                            Set rout = rout.Resize(, rout.columns.count + 1)
                            lastCell(rout).value = cj.key
                        End If
                    End If

            Next cj
        Next job

        ' now make a dataset of the headings
        Set ds = New cDataSet
        ds.populateData rout
        
        'add the data values
        For Each job In .datajObject.children
            For Each cj In job.children

                    With ds.headingRow.exists(cj.key)
                        .where.Offset(job.childIndex).value = cj.value
                    End With

            Next cj
        Next job

        ' clear up dataset
        ds.tearDown
        
        ' clear up restquery
        .tearDown
    End With

For much more information and more complex cases see jSon and Rest to Excel library