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.In
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.
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 dont 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
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
Here's the code for getting the data and converting it as necessary
Here's the parser.
You can get me on Google plus, Twitter or this forum.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > excel to json and back >