Using Apps Script for xml json conversion

One of the great things about Apps Script is that you can use the content service to create your own apis, without the need for a server infrastructure. In From Xml to JSON and Convert JSON to XML I showed how to use Apps Script to convert between json and api. Now here is that code wrapped up in an web app, which means you can call it from any language, either passing some json and getting xml back, or the other way round. 

You can also use it from apps script of course - useful if you get xml back from some API but would rather have JSON.

How to use

First of all you'll need the URL of the web app. It's publicly published here, if you want to use my version. 

https://script.google.com/macros/s/AKfycbzNhEv3Nb38Tr277Ws0rUMGjXutkrGzEtLXfdX8XxThU8SUa-c/exec

or you can make your own by getting the code off github.

It supports both GET and POST, and will take an optional parameter, callback=, if you want JSONP rather than JSON from an XML to JSON conversion.  The data to be converted is passed with the convert= parameter which is mandatory. The direction of conversion is automatically detected from the data sent.

In the examples following we have a variable to which the converter webapp url has been assigned.
var URL = "https://script.google.com/macros/s/AKfycbzNhEv3Nb38Tr277Ws0rUMGjXutkrGzEtLXfdX8XxThU8SUa-c/exec";

Here's an example using POST from another Apps Script app.  In this example, first I get some XML from an API that returns XML, then call the converter web app to turn that into JSON.
  // do an api that returns xml
  var xml =  UrlFetchApp.fetch("http://www.omdbapi.com/?s=star%20wars&r=xml");
  if (!xml.getResponseCode() === 200 ) throw new Error ('failed to get data from api ' + xml.getContentText());
  
  // convert it to JSON
  var result = UrlFetchApp.fetch(URL , {
    method:"POST",
    payload:{
      convert:xml.getContentText()
    }
  });
  
  // show result
  if (!result.getResponseCode() === 200 ) throw new Error ('failed to covert' + result.getContentText());
  Logger.log(result.getContentText());

and here's the result
{
  "result": [{
  "Title": "Star Wars: Episode IV - A New Hope",
  "Year": 1977,
  "imdbID": "tt0076759",
  "Type": "movie",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMTU4NTczODkwM15BMl5BanBnXkFtZTcwMzEyMTIyMw@@._V1_SX300.jpg"
  }, {
  "Title": "Star Wars: Episode V - The Empire Strikes Back",
  "Year": 1980,
  "imdbID": "tt0080684",
  "Type": "movie",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMjE2MzQwMTgxN15BMl5BanBnXkFtZTcwMDQzNjk2OQ@@._V1_SX300.jpg"
  }, {
  "Title": "Star Wars: Episode VI - Return of the Jedi",
  "Year": 1983,
  "imdbID": "tt0086190",
  "Type": "movie",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMTQ0MzI1NjYwOF5BMl5BanBnXkFtZTgwODU3NDU2MTE@._V1._CR93,97,1209,1861_SX89_AL_.jpg_V1_SX300.jpg"
  }, {
  "Title": "Star Wars: Episode I - The Phantom Menace",
  "Year": 1999,
  "imdbID": "tt0120915",
  "Type": "movie",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMTQ4NjEwNDA2Nl5BMl5BanBnXkFtZTcwNDUyNDQzNw@@._V1_SX300.jpg"
  }, {
  "Title": "Star Wars: Episode III - Revenge of the Sith",
  "Year": 2005,
  "imdbID": "tt0121766",
  "Type": "movie",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BNTc4MTc3NTQ5OF5BMl5BanBnXkFtZTcwOTg0NjI4NA@@._V1_SX300.jpg"
  }, {
  "Title": "Star Wars: Episode II - Attack of the Clones",
  "Year": 2002,
  "imdbID": "tt0121765",
  "Type": "movie",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMTY5MjI5NTIwNl5BMl5BanBnXkFtZTYwMTM1Njg2._V1_SX300.jpg"
  }, {
  "Title": "Star Wars: The Force Awakens",
  "Year": 2015,
  "imdbID": "tt2488496",
  "Type": "movie",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BOTAzODEzNDAzMl5BMl5BanBnXkFtZTgwMDU1MTgzNzE@._V1_SX300.jpg"
  }, {
  "Title": "Star Wars: The Clone Wars",
  "Year": 2008,
  "imdbID": "tt1185834",
  "Type": "movie",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMTI1MDIwMTczOV5BMl5BanBnXkFtZTcwNTI4MDE3MQ@@._V1_SX300.jpg"
  }, {
  "Title": "Star Wars: The Clone Wars",
  "Year": "2008–2015",
  "imdbID": "tt0458290",
  "Type": "series",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMTM0NjQ2Mjk0OV5BMl5BanBnXkFtZTcwODQ3Njc3Mg@@._V1_SX300.jpg"
  }, {
  "Title": "Star Wars: Clone Wars",
  "Year": "2003–2005",
  "imdbID": "tt0361243",
  "Type": "series",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMjE2Mjk5Mzk3M15BMl5BanBnXkFtZTcwMDkzMTIzMQ@@._V1_SX300.jpg"
  }],
  "response": true
 }

Now, we'll try getting the result in JSON. There is no difference in the code, aside from the request to get xml rather than JSON. The converter detects automatically which type of conversion is needed, so in this case it will detect it has been handed some JSON and will convert it to XML.
  // do an api that returns json
  var json =  UrlFetchApp.fetch("http://www.omdbapi.com/?s=star%20wars&r=json");
  if (!json.getResponseCode() === 200 ) throw new Error ('failed to get data from API' + json.getContentText());
  
  // convert it to JSON
  var result = UrlFetchApp.fetch(URL , {
    method:"POST",
    payload:{
      convert:json.getContentText()
    }
  });
  
  // show result
  if (!result.getResponseCode() === 200 ) throw new Error ('failed to covert' + result.getContentText());
  Logger.log(result.getContentText());

The result in XML
<?xml version="1.0" encoding="UTF-8"?>
<root>
  <Search>
    <Title>Star Wars: Episode IV - A New Hope</Title>
    <Year>1977</Year>
    <imdbID>tt0076759</imdbID>
    <Type>movie</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BMTU4NTczODkwM15BMl5BanBnXkFtZTcwMzEyMTIyMw@@._V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: Episode V - The Empire Strikes Back</Title>
    <Year>1980</Year>
    <imdbID>tt0080684</imdbID>
    <Type>movie</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BMjE2MzQwMTgxN15BMl5BanBnXkFtZTcwMDQzNjk2OQ@@._V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: Episode VI - Return of the Jedi</Title>
    <Year>1983</Year>
    <imdbID>tt0086190</imdbID>
    <Type>movie</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BMTQ0MzI1NjYwOF5BMl5BanBnXkFtZTgwODU3NDU2MTE@._V1._CR93,97,1209,1861_SX89_AL_.jpg_V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: Episode I - The Phantom Menace</Title>
    <Year>1999</Year>
    <imdbID>tt0120915</imdbID>
    <Type>movie</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BMTQ4NjEwNDA2Nl5BMl5BanBnXkFtZTcwNDUyNDQzNw@@._V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: Episode III - Revenge of the Sith</Title>
    <Year>2005</Year>
    <imdbID>tt0121766</imdbID>
    <Type>movie</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BNTc4MTc3NTQ5OF5BMl5BanBnXkFtZTcwOTg0NjI4NA@@._V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: Episode II - Attack of the Clones</Title>
    <Year>2002</Year>
    <imdbID>tt0121765</imdbID>
    <Type>movie</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BMTY5MjI5NTIwNl5BMl5BanBnXkFtZTYwMTM1Njg2._V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: The Force Awakens</Title>
    <Year>2015</Year>
    <imdbID>tt2488496</imdbID>
    <Type>movie</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BOTAzODEzNDAzMl5BMl5BanBnXkFtZTgwMDU1MTgzNzE@._V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: The Clone Wars</Title>
    <Year>2008</Year>
    <imdbID>tt1185834</imdbID>
    <Type>movie</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BMTI1MDIwMTczOV5BMl5BanBnXkFtZTcwNTI4MDE3MQ@@._V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: The Clone Wars</Title>
    <Year>2008–2015</Year>
    <imdbID>tt0458290</imdbID>
    <Type>series</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BMTM0NjQ2Mjk0OV5BMl5BanBnXkFtZTcwODQ3Njc3Mg@@._V1_SX300.jpg</Poster>
  </Search>
  <Search>
    <Title>Star Wars: Clone Wars</Title>
    <Year>2003–2005</Year>
    <imdbID>tt0361243</imdbID>
    <Type>series</Type>
    <Poster>http://ia.media-imdb.com/images/M/MV5BMjE2Mjk5Mzk3M15BMl5BanBnXkFtZTcwMDkzMTIzMQ@@._V1_SX300.jpg</Poster>
  </Search>
</root>

Using GET


You can use GET rather than POST for small payloads. This example takes an xmlString and passes it to the webapp for conversion to JSON. The data should be encoded and passed as the convert parameter like this. 
  // test xml to json
  var result = UrlFetchApp.fetch(URL + '?convert=' + encodeURIComponent (testXml), {
    method:"GET"
  });
  
  // show result
  if (!result.getResponseCode() === 200 ) throw new Error ('failed to covert' + result.getContentText());
  Logger.log(result.getContentText());



The code

You'll get the code off GitHub for the webapp if you need it. Here's some examples of using it. The conversions alogorithms are also available at From Xml to JSON and Convert JSON to XML
var URL = "https://script.google.com/macros/s/AKfycbzNhEv3Nb38Tr277Ws0rUMGjXutkrGzEtLXfdX8XxThU8SUa-c/exec";

function doSomeTests () {
  
  // json to xml
  Logger.log (tester("http://www.omdbapi.com/?s=star%20wars&r=json"));
  
  // xml to json
  Logger.log (tester("http://www.omdbapi.com/?s=star%20wars&r=xml"));
}

function tester (url) {
  
// do an api that returns either xml or json and convert it
  var result =  UrlFetchApp.fetch(url);
  if (!result.getResponseCode() === 200 ) throw new Error ('failed to convert' + result.getContentText());
  
  // convert it to JSON
  var conversion = UrlFetchApp.fetch(URL , {
    method:"POST",
    payload:{
      convert:result.getContentText()
    }
  });
  
  // show result
  if (!conversion.getResponseCode() === 200 ) throw new Error ('failed to covert' + conversion.getContentText());
  return conversion.getContentText();
}


Here's an example of converting a file on drive from xml to json
function getDriveFile() {

  var inputFile = "0B92xxxxxxxxxRyRm5J";

  // convert it to JSON
  var conversion = UrlFetchApp.fetch(URL , {
    method:"POST",
    payload:{
      convert:DriveApp.getFileById(inputFile).getBlob().getDataAsString()
    }
  });
  
  // write result to drive
  if (!conversion.getResponseCode() === 200 ) throw new Error ('failed to covert' + conversion.getContentText());
  DriveApp.createFile('conversion.json', conversion.getContentText());
}


For more like this, see Google Apps Scripts snippets. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.


Comments