Formated JSON

Skip to first unread message

Oct 6, 2013, 1:08:27 PM10/6/13
I try to convert the JSON with the following code
Sub toto()
Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject

.populateJSON jo.deSerialize( _
.httpGET( _
"" _
) _
).child("series"), Range("Feuil1!$a$1")
End Sub

I can get the data "series" but I'd also get the "data" data and I can not do it by changing the code.

Below the JSON file
{"series":[{"label":"Fibaro Plug - Watts","Id":50,"data":[[1380987292000,5],[1380987301000,13],[1380987441000,13],[1380987565000,7],[1380987693000,8],[1380987817000,4],[1380987940000,7],[1380988064000,4],[1380988187000,12],[1380988310000,8],[1380988433000,6],[1380988558000,8],[1380988683000,12],[1380988806000,11],[1380988931000,7],[1380989054000,7],[1380989177000,7],[1380989301000,11],[1380989425000,6],[1380989549000,13],[1380989673000,7],[1380989797000,7],[1380989921000,9],[1380990045000,10],[1380990172000,10],[1380990298000,10],[1380990422000,13],[1380990545000,7],[1380990668000,4],[1380990803000,5],[1380990943000,12],[1380991068000,7]],"pointsRet":425,"pointsTot":7379,"min":0,"max":316}],"procTime":2.96,"min":1380987292,"max":1381073692}

Could you help me?

Bruce Mcpherson

Oct 8, 2013, 4:56:48 AM10/8/13
populateJSON is only for use with JSON that have been specifically formatted by the cdataset,serialize() method, as described in 

What you need here is a rest/excel query. The code below should do what you need. I'm assuming that there can be more than one label/id so ive made them data fields rather than headings . if not the case then its a small change.

Also im assuming that your first data value is a unix date- the default behavior for numbers that look like unix dates is to convert them to excel dates- which is what will happen here.

Private Sub ptp()

    Dim sUrl As String, jor As cJobject, joc As cJobject, job As cJobject, r As Range
    With restQuery("Feuil1", , , , sUrl, , , False, , , True)
        Set r = firstCell(.dSet.headingRow.where)
        For Each job In .jObject.child("series").children
            For Each jor In job.child("data").children
                ' assume heading becomes additional data fields
                With r.Offset(jor.childIndex - 1)
                    .Offset(, 0).value = job.child("label").value
                    .Offset(, 1).value = job.child("id").value
                    For Each joc In jor.children
                        .Offset(, joc.childIndex - 1 + 2).value = joc.value
                    Next joc
                End With
            Next jor
        Next job
    End With
End Sub

You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
For more options, visit

Philippe PIOT

Oct 15, 2013, 4:14:05 PM10/15/13
Thank you for the code. It works fine :-)

2013/10/8 Bruce Mcpherson <>

You received this message because you are subscribed to a topic in the Google Groups "Excel Liberation" group.
To unsubscribe from this topic, visit
To unsubscribe from this group and all of its topics, send an email to
Reply all
Reply to author
0 new messages