There are still many APIS that only respond in XML. Although Excel has XML native it’s still a pain to deal with, and I really wanted to abstract the data format from the processing of it.
That means I need to be able to get XML into a cJobject, which today only understands how to parse JSON. There is a native xml to cjobject parser here, but here’s an alternative quick and dirty approach that can be used, not only for Excel but for any xml-JSON conversion.
GAS XML service
The idea here is that if an API (or anything else) returns some XML, we can just post it to Google Apps Script and let it take care of the conversion for you. I’ve made this simple script public so anyone can use it with no authentication required. Just POST some XML data to https://script.google.com/macros/s/AKfycbziYOdWjNFtUR_TTQU-GiMYkan2h5ZDtaqeWIsYUAKEa6irjzNa/exec and it will return JSON.
It’s not perfect (arrays and text fields are a little half assed), but it’s not bad for a start. Im using Xml.parse rather than XmlService.parse, so I’ll need to update that sometime in the future since Xml is being shut down at some point too.
Assuming that you are familiar with the cJobject and cBrowser classes on this site (you can get it from cDataSet.xlsm workbook at Downloads, or by using gistThat to pull them into your workbook from the web), here’s how to use them to end up with structured data that started as XML but can be used with all the cJobject orientated stuff on this site.
I provide a simple single function (it’s in the restLibrary module). Given a URL that returns XML, it will fetch that, then go off to Google Apps Script to convert it to JSON, and then parse the JSON into a cJObject. Here’s an example – I’m stringifying it again back to JSON to show the result
Private Sub testGetXML()
Dim url As String
url = _
Here’s the XML that gets returned initially
<city id="2643743" name="London">
<coord lon="-0.12574" lat="51.50853"/>
<sun rise="2013-10-29T06:50:02" set="2013-10-29T16:38:19"/>
<temperature value="282.05" min="282.05" max="282.05" unit="kelvin"/>
<humidity value="79" unit="%"/>
<pressure value="1022.29" unit="hPa"/>
<speed value="3.55" name="Gentle Breeze"/>
<direction value="266.501" code="W" name="West"/>
<clouds value="0" name="sky is clear"/>
<weather number="800" value="Sky is Clear" icon="01n"/>
And here’s what we get after converting to JSON, parsing to a cJobject, and stringifying back to JSON. I notice it adds a few unnecessary Text nodes, but it’s usable.
The VBA code
Since most of the work is done by Google Apps Script, this is pretty trivial.
The Google Apps Script Code
Again, just few lines
The Rest to Excel library can now automatically detect whether an API response is XML or JSON, and convert it to JSON, as described in XML to JSON with VBA. That way we’ll have a consistent data structure to play with regardless of the API format.