Proxy jsonp

If you are reading this, I guess you are familiar with this error message "XMLHttpRequest cannot load 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

For help and more information 
join our community,  follow the blog,  follow me on twitter, or follow me on g+

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="">Bruce McPherson</a><a href=""></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

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 on dropbox using jQuery. This is example is here on dropbox.

<script type="text/javascript" src=""></script>

th {
  font-weight: bold;
  background: LightBlue;
  border-bottom-style: solid;
  border-bottom-width: 2px;
  padding: 6px;
  margin: 3px;
td {
  background: GhostWhite;
  padding: 6px;
  margin: 3px;
  border-bottom-style: dotted;
  border-bottom-width: 1px;
<script type="text/javascript">
$(document).ready(function() {
var url = encodeURI("");
var proxyJsonp = "";
$.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) {
// headings
for(var k in dataTable[0]) {
$('#data').append('<th> ' + k + '</th>');
// data
for (var i = 0 ; i < dataTable.length;i++){
for(var k in dataTable[0]) {
$('#data').append('<td> ' + dataTable[i][k] + '</td>');
} else {
$('#data').append("empty dataset");
} else {
$('#data').append("failed to get data for url " + url);
​<div id='data'><h3>Using google apps script to act as proxy jSonp</h3></div>

That gives this result


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.
  • 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

Google Caching is already implemented in the cBrowser class used here, so this proxy script, will also minimize fetching from original data source. 

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.