XML to JSON with VBA

In Delegating xml to json conversion to GAS I showed how you could get Google Apps Script to convert XML to JSON by posting the XML and getting back the JSON response – using VBA to illustrate. 

Here’s a native VBA version. Again we’ll be using cjobject (see  How to use cJobject) to hold the JSON representation of an XML object. In the example given here, we’ll do a query to an API and automatically detect if it is JSON or XML. If it’s XML, we’ll convert it to JSON. In either case – a cJobject is the result. 

The test

Like in Delegating xml to json conversion to GAS, first off we’ll use the open weather API. One thing i noticed is that this API the XML format returns a different dataset than the JSON format – strange but true.

Here’s all we need. I’m stringifying the returned object to JSON to be able to print the result.


Although this returns XML (as shown in Delegating xml to json conversion to GAS), getAndMakeJobjectAuto() will convert it as required, giving this result. It’s not so good as the real JSON result, since all attributes are considered to be strings in XML, but perfectly usable, and actually better than the Google Apps Script version, since we don’t have those trailing Text elements observed in Delegating xml to json conversion to GAS


This time we’ll call the JSON version of the API


which gives us this


Handling Arrays

In JSON an array is clearly identified [..]. In XML, not so much. Consider this


It’s intuitively obvious that names is an array of name objects. The simple rule is that the converter will assume this is an array if child object element node names repeat. So the above example gets converted to 


The code

Here’s the code for getting the data and converting it as necessary


Here’s the parser.






Continue reading about Rest to Excel Library here.