So this means that I needed to store any object (not just stringifiable ones) as the value of a key/value pair in a cJobject.
For the sake of this example, we'll populate a sheet and store some Excel Sheet objects in a cJobject. Normally I don't need to do this since I always use data abstraction when interacting with sheets (see How to use cDataSet) and that has built in methods for converting to and from cJobjects and JSON, but for the sake of an example, let's try this. We'll use the test data from cJobject deep dive and write it to a sheet.
First the headings - we'll call the columns the same thing as the keys of the JSON data.
Now the data
That correctly gives us this
But there's no guarantee that the properties of each of the objects in the array are each in the same order if they came from some external JSON source, so we need to refine things a little.
Now we'll create a new cJobject in which we store the Excel Range object at which we stored each column heading. Each property in this cJobject will have a key of the column name. Like this we can retrieve the address of the column header later when we process the data
When we use the .add method, cJobject will notice that its being asked to store an object rather than a value and treat it specially internally. Here's what headings looks like when stringified.
Of course an Excel range is not stringifiable to JSON, so instead it reports its type against the value. Note that you can store any kind of object or custom class - even another cJobject. It will treat them all the same way.
There are a few methods specially for dealing with objects stored this way -
These are equivalent
When you use .add it replaces the current property value, or creates a new one as appropriate. Objects are the same as regular values. This examples will all create or replace the properties mentioned
So now we have the ranges that we stored the columns names in, and all the properties of that object are open to us.
Let's take a look
That means we can use the keys in the data to find the correct column to store each data value against by referencing the range object stored in the cJobject
which gives us the correct result
To test, we'll shuffle the order of one of the objects in the test data like this
In our first example, the data for mary would have ended up in the wrong column, but since we are now storing the column range keyed by property name, we'll always end up in the right place regardless of the incoming order. And here's the result
Using cJobject means that I can get many of the benefits of classes without bothering to make a class, as well as JSON and recursion by default for every data structure and the ability to create objects on the fly.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > excel to json and back > cJobject deep dive >