I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

In Getting lists as a restquery I showed how to get JSON back by querying the scriptDB directly. Here’s how to consume that in VBA. This example is general purpose – it can be used for any scriptdb, and will adapt as required. It uses Rest to Excel library and can be downloaded in the latest cDataSet.xlsm workbook.

The restlibrary entry

As with all restlibrary examples, we need to create an entry in the restlibrary – it looks like this
        With .add("blister")
            .add "restType", erRestType.erSingleQuery
            .add "url", "https://script.google.com/a/macros/mcpher.com/s/AKfycbzhzIDmgY9BNeBu87puxMVUlMkJ4UkD_Yvjdt5MhOxR1R6RG88/exec?type=jsonp&source=scriptdb&module=blister&library="
            .add "results", "results"
            .add "treeSearch", True
            .add "ignore", vbNullString
            .add "append", "&query="
        End With

The code

Public Sub testBlister()
    Dim q As String, jo As cJobject, ds As cDataSet, joc As cJobject, job As cJobject
    Set jo = New cJobject

    With restQuery("blister", "blister", InputBox("name of library"), _
            , , , , False, , , , , , , , , _
            jo.init(Nothing).add("package").add("name", InputBox("list name")).root.serialize)
        Set ds = .dset
        With .jObject
            If .toString("status.code") = "good" Then
                ' create the headings
                For Each job In .child("results.1.package.keys").children
                    With firstCell(ds.headingRow.where)
                        .Offset(0, job.childIndex - 1).value = job.value
                    End With
                Next job
                ' now the data
                For Each job In .child("results.1.package.items").children
                    For Each joc In job.children
                        With firstCell(ds.headingRow.where)
                            .Offset(joc.childIndex, job.childIndex - 1).value = joc.value
                        End With
                    Next joc
                Next job
            Else
                MsgBox ("error getting data " & .serialize)
            End If
        End With
        .teardown
    End With
End Sub
It will ask for the name of the library (probably “blister”) and the listName – in this case below “airlines”, and produce something that starts like this.
Obviously this could be easily consumed by anything that can interpret JSON
All comments, suggestions, assistance, good lists are welcome as I develop this capability. You can get me on Google plus, Twitter or this forum.