JSON data can go many levels down. Whereas the cJobject is designed to mimic javaScript’s capability to deal with this, eventually you will want to populate a two dimensional table in Excel with some data.
Consider this simple example
{ “Id”: “a’, “names”:[“x”,”y”,”z”]}
What would your output table look like?
| id | name |
| a | x |
| a | y |
| a | z |
or this…
| id | name |
| a | x,y,z |
or this..
| id | name.1 | name.2 | name.3 |
| a | x | y | z |
The excel rest library will deal with the 2nd and 3rd examples automatically, simply by naming the columns as shown. However the cases can get much more complex, especially when there are multiple arrays in a single row. This is also implemented in the Google Apps Script Version of these functions.
Array of objects
Another example of increasing complexity is when there are arrays of objects. Look at this example
{ “Id”: “a’, “names”:[{“father”:”x”,”mother”:y”},{“father”:”u”,”mother”:v”}]}
How would the crest library know how to layout this data?
Naming the columns like this would work
| id | names.1.father | names.2.father | names.1.mother | names.2.mother |
| a | x | u | y | v |
But the comma separated example would not, since the object names is not an array of values that could be represented in a comma separated list, but is an area of objects which could indeed lead to other deeper objects. Not only that but you dont necessarily know how many there are and a comma separated list might be a better solution if only it could be expressed.
..syntax
Here’s a trick .. if you name your columns names..father and names..mother you’ll get this. This takes care of not knowing how many there are and creates a comma seperated list in a single column
| id | names..father | names..mother |
| a | x,u | y,v |
For more about json continue reading here or for more about Rest to Excel Library continue reading here