Excel-jSon-Excel in one line of code

Excel-jSon-Excel in one line of code

On the ramblings site, I set myself the challenge to write some classes that could be invoked to convert an entire Worksheet to jSon, or alternatively, to consume a webservice and populate and entire worksheet in one line of code

These examples, and the classes they use can be downloaded from the ramblings site, and are in included in cDataSet.xlsm. 
Here we go
Get a jSon response from a web service and populate a worksheet with the result.
    Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject
    dSet.populateJSON jo.deSerialize( _
        cb.httpGET( _
            “http://dl.dropbox.com/u/14196218/files/jSon1.html” _
            ) _
        ).Child(“cDataSet”), Range(“json1!$a$1”)

create and print a jSon string from a worksheet.

    Dim dSet As New cDataSet    Debug.Print dSet.populateData( _
Range(“jSon2!$a$1”), , , , , , True _
).jObject.Serialize(True)

The jSon string both created and consumed looks like this

  {
   “cDataSet”:[      {
         “Customer”:”Acme”,
         “Contact”:”john”,
         “Total”:”100″,
         “Country”:”US”
      },
      {
         “Customer”:”Smiths”,
         “Contact”:”fred”,
         “Total”:”460″,
         “Country”:”UK”
      },
      {
         “Customer”:”Jones”,
         “Contact”:”joe”,
         “Total”:”24″,
         “Country”:”US”
      },
      {
         “Customer”:”Renault”,
         “Contact”:”Marie”,
         “Total”:”536″,
         “Country”:”FRANCE”
      },
      {
         “Customer”:”Schneider”,
         “Contact”:”Hans”,
         “Total”:”1334″,
         “Country”:”Germany”
      }
    ]
 }

The Excel sheet that gets created looks like this

 

Hopefully you will see how that works from some of the other jSon examples on the ramblings site, or you can ask me on the forum
About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


five × 2 =