Delegating xml to json conversion to GAS

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.

VBA example

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 = _
     "http://api.openweathermap.org/data/2.5/weather?q=London&mode=xml"
    Debug.Print getAndMakeJobjectFromXML(url).stringify
End Sub

Here’s the XML that gets returned initially

<current>
<city id="2643743" name="London">
<coord lon="-0.12574" lat="51.50853"/>
<country>GB</country>
<sun rise="2013-10-29T06:50:02" set="2013-10-29T16:38:19"/>
</city>
<temperature value="282.05" min="282.05" max="282.05" unit="kelvin"/>
<humidity value="79" unit="%"/>
<pressure value="1022.29" unit="hPa"/>
<wind>
<speed value="3.55" name="Gentle Breeze"/>
<direction value="266.501" code="W" name="West"/>
</wind>
<clouds value="0" name="sky is clear"/>
<precipitation mode="no"/>
<weather number="800" value="Sky is Clear" icon="01n"/>
<lastupdate value="2013-10-29T20:12:03"/>
</current>

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.

{
    "json": {
        "current": {
            "temperature": {
                "value": "282.963",
                "min": "282.963",
                "max": "282.963",
                "unit": "kelvin"
            },
            "city": {
                "coord": {
                    "lon": "-0.12574",
                    "lat": "51.50853"
                },
                "country": {
                    "Text": "GB"
                },
                "sun": {
                    "set": "2013-10-29T16:38:19",
                    "rise": "2013-10-29T06:50:02"
                },
                "id": "2643743",
                "name": "London",
                "Text": ""
            },
            "lastupdate": {
                "value": "2013-10-29T18:57:41"
            },
            "weather": {
                "value": "scattered clouds",
                "number": "802",
                "icon": "03n"
            },
            "wind": {
                "direction": {
                    "value": "270",
                    "code": "W",
                    "name": "West"
                },
                "speed": {
                    "value": "4.05",
                    "name": "Gentle Breeze"
                },
                "Text": ""
            },
            "humidity": {
                "value": "76",
                "unit": "%"
            },
            "clouds": {
                "value": "44",
                "name": "scattered clouds"
            },
            "pressure": {
                "value": "1020.21",
                "unit": "hPa"
            },
            "Text": "",
            "precipitation": {
                "mode": "no"
            }
        }
    }
}

The VBA code

Since most of the work is done by Google Apps Script, this is pretty trivial. 

Public Function getAndMakeJobjectFromXML(url As String) As cJobject
    ' we do an get on the given url
    Dim cb As cBrowser, helperUrl As String
    Set cb = New cBrowser
    helperUrl = _
     "https://script.google.com/macros/s/AKfycbziYOdWjNFtUR_TTQU-GiMYkan2h5ZDtaqeWIsYUAKEa6irjzNa/exec"

    With cb
        ' get the xml
        .httpGET url
        If .isOk Then
            Set getAndMakeJobjectFromXML = makeJobjectFromXML(.Text)
        Else
            MsgBox ("error getting " & url)
        End If
        .tearDown
    End With
    
End Function

The Google Apps Script Code

Again, just few lines

function doPost(e) {
  // some defaults for standalone testing
  e = e || {postData:{contents:"<a>a</a>"}};
  e.queryString = e.queryString || "convert=xmltojson" ;  
  var type = e.queryString.toLowerCase().split("=");
 
  // convert xml to json
  var results = {status:"bad", error:"no errors"};
  try {
    if (type.length < 2 || type[0] != "convert") {
      results.error = "need convert= on url";
    }
    else if (type[1] == "xmltojson") {
      results.xml = e.postData.contents;
      results.json = Xml.parse(results.xml,true);
      results.status = "good";
    }
    else {
      results.error = "only xmltojson implemented for now";
    }
  }
  catch(err) {
    results.error = err;
  }
 
  return ContentService.createTextOutput (JSON.stringify(results))
                  .setMimeType(ContentService.MimeType.JSON) ; 
}

function doGet(e) {
 e=e||{};
 e.type="get";
  return ContentService.createTextOutput (JSON.stringify(e))
                  .setMimeType(ContentService.MimeType.JSON) ; 
}

Next steps

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.