cJobect, 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.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
Update: The Xml service is being deprecated in Apps Script, but has been replaced by something else. The details below are still of interest, but there is another API that supercedes this. See Using Apps Script for xml json conversion
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
Here's the XML that gets returned initially
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.
Since most of the work is done by Google Apps Script, this is pretty trivial.
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.
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 >