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.
Page Content
hide
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.