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 scenarious see  Adding to the rest libraryYou 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


		


  • 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


		


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



		


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



		


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. 



		


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