The Rest to Excel library is a set of classes and modules that simplify the populating of Excel sheets with data from RestFul APIs. Google Apps Script of course already has plenty of much better ways to deal with this but,  but the objective here is to manage the transition from Excel to Google Docs, and to be able to use the same library definitions and code for both platforms.

Required conversions

In addition to the Rest to Excel library and associated examples and modules, we also need the Data manipulation Classes in Google Apps Script, already converted to GAS as part of the Roadmapper migration , and a bunch of utilities such as Optimizing access to sheet values , VBA collection in javaScript and various other hacks covered in From VBA to Google Apps Script.

The big conversion though is going to be the cJobject class, which was implemented in VBA to allow for Excel JSON conversion

cJobject approach

The complex part of the cJobject implementation in VBA was the jSon parser. I won’t need to reimplement that, since GAS has inbuilt jSon management (it is javaScript after all), but I still want to be able to use cJobject, since all the code that will be converted over will be using that object.

Since the GAS jSon parser is all about populating native javaScript objects with jSON and visa versa, then a useful trick would be to be able to covert cJobjects to and from native javaScript objects. That way, all the existing code that uses cJobject can co-exist and still allow for the use of the inbuilt GAS capabilities. 

cJobject to Native

Since the cJobject, is after all, an attempt to make VBA look like javaScript whilst handling recursive objects, the conversion is really not too complex. Let’s take a look at how to covert a cJobject to a native javaScript object heirachy. It is of course recursive, given the unstructured depth – but it is rather concise and works very nicely.

Serializing a cJobject

This is now a cinch. Just convert it to a javaScript native object as above, and use GAS jSon functions to stringify it. Nice, but we don’t have the beautifier available as I had implemented in VBA. 

Native to cJobject

Coming the other way – creating a cJobject from a javaScript native object structure is a little more tricky, but still the same recursive approach, and is even more concise.

Deserializing a cJobject from a jSon string

It’s simply converting jSon to a native javaScript object, then converting that native object to a cJobject as above. 

Transitioning is covered more comprehensively in my my book, Going Gas – from VBA to Apps script.

Continue reading about VBA to Apps Script here