If you are reading this, I guess you are familiar with this error message

XMLHttpRequest cannot load http://dl.dropbox.com/u/14196218/Share/somedata.json. Origin null is not allowed by Access-Control-Allow-Origin.

This is of course the same origin policy that prevents you being able to access jSon data that is stored on a different domain than your javaScript Code. This is really annoying if you are trying to use publicly hosted data, but there are ways round it

What is jSonP

In simple terms, this is when you pass a callback function name to the data provider server. This causes the data provider to wrap its jSon reponse up in a call to your function, as an argument. So lets say you do this, in this case using jQuery
$.getJSON(url + "?callback=?", null, function(data)  {..} );
If the server at ‘url’ knows how to react to a jsonp request, it will return something like,
yourfunction (returnedJson), and your function can then process the returned jSon.

What if you don’t have a server

But what if the data is not being hosted on a server (let’s say on dropbox or some other file sharing place), or what  if the data hosting  server doesn’t allow jSonp? You’re pretty much screwed.

Using Google Apps Content service as jSONP wrapper.

UrlFetch() in Google Apps Script is not subject to the same Same origin Policy as javaScript, so what if we passed the url holding the data to Google Apps Script as a parameter, asked it to do the data retrieval , wrap it up in a jSonp return, and then use google content service to serve the retrieved data back?
It works! I stumbled across this when I was researching ScriptDB as image store.
All you need to do is to create a publicly published google apps script that will accept a url and a callback, and wrap up the returned data in jSONP. I’ve already made one, here it is.
/** @description
 * using Google Apps Script as a proxy jSON getter where jSONP is not implemented 
 * @author <a href="mailto:bruce@mcpher.com">Bruce McPherson</a><a href="https://ramblings.mcpher.com"> ramblings.mcpher.com</a>
 */

/**
 * execute e.url and wrap up as jSONp for e.callback
 * @param {object} e the event parameters
 * @return {String} the jSONp response
 */
function doGet(e) {
    return ContentService
            .createTextOutput(urlGet(e))
              .setMimeType(ContentService.MimeType.JSON);  

}
function urlGet(e) {
  var results = {};
  
  if (!e.parameter.url) {
    results = {error:'missing url',results: null };
  }
  else {

    var response = new mcpher.cBrowser().httpGET (decodeURIComponent(e.parameter.url));

    if (!response) {
      var results = {error:'no data returned',results: null};
    }
    else {
      var results = { results: response };
    }
  }
  var j = Utilities.jsonStringify(results);

  if (e.parameter.callback) {
      j = e.parameter.callback + "(" + j + ")"
  }
  return j;
}

You can use it by referencing this URL (the code is here ).

and appending 2 arguments
  • url = encoded URI of the cross origin domain
  • callback = your callback function
What you’ll get back is
callback ( { results: "json encoded results from URL") }

Example jquery script

Here’s an example of  picking up this json  file hosted using jQuery.
<html>
<head>
<script type="text/javascript" src="http://code.jquery.com/jquery-1.7.1.min.js"></script>
<style>

th {
  font-weight: bold;
  background: LightBlue;
  border-bottom-style: solid;
  border-bottom-width: 2px;
  text-align:left;
  padding: 6px;
  margin: 3px;
}
td {
  background: GhostWhite;
  padding: 6px;
  margin: 3px;
  border-bottom-style: dotted;
  border-bottom-width: 1px;
  text-align:left;
}
</style>
</head>
<body>
<script type="text/javascript">
 $(document).ready(function() {
 var url = encodeURI("http://dl.dropbox.com/u/14196218/Share/somedata.json");
 var proxyJsonp = "https://script.google.com/a/macros/mcpher.com/s/AKfycbzGgpLEWS0rKSBqXG5PcvJ7Fpe02fvGqiCqq54SVQmBJSpy_6s/exec";
 $.getJSON(proxyJsonp + "?url=" + url + "&callback=?", null, function(data) {
 var results = data.error;
 if(!results) {
 results = jQuery.parseJSON(data.results);

 var dataTable = results.cDataSet;
 if(dataTable.length) {
 $('#data').append("<table><tbody><tr>");
 // headings
 for(var k in dataTable[0]) {
 $('#data').append('<th> ' + k + '</th>');
 }
 $('#data').append("</tr>");
 // data
 for (var i = 0 ; i < dataTable.length;i++){
 $('#data').append("<tr>");
 for(var k in dataTable[0]) {
 $('#data').append('<td> ' + dataTable[i][k] + '</td>');
 }
 $('#data').append("</tr>");
 }
 $('#data').append("</tbody></table>");
 
 } else {
 $('#data').append("empty dataset");
 }
 } else {
 $('#data').append("failed to get data for url " + url);
 }
 });
 }); 
</script>
​<div id='data'><h3>Using google apps script to act as proxy jSonp</h3></div>
</body>
</html>

That gives this result

Summary

Using this technique, all that Cross Domain data hosting problems just go away. Here’s what you need
  • The  jsonP proxy.  The proxyUrl pointing to the google apps script proxy.
https://script.google.com/a/macros/mcpher.com/s/AKfycbzGgpLEWS0rKSBqXG5PcvJ7Fpe02fvGqiCqq54SVQmBJSpy_6s/exec
  • Provide the url and callback as parameters to the jQuery  getJSON function
proxyUrl + "?url=" +  url + "&callback=?"
  • Check returned data for an error code in your jsonp callback, or process your data
,function(data)  {
if (data.error) ... handle error
else ... handle your data returned by jQuery.parseJSON(data.results)
}

For the sake of the example, I have used jQuery but you should be able to use Ajax, plain javaScript or some other frameworks that support jSonP.  For more like this see From VBA to Google Apps Script.   Join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.

Much of  the code referred to here is the mcpher library.  You can find out how to include it in your project here.

For help and more information join our community,  follow the blog, or  follow me on Twitter