JSONP and Google Apps Script

This follows my baby steps towards a web page populated with data from a table. See also part 2.

Having worked with the standard user interface of Google Apps Script, I felt that I was approaching a sensible limit of what could be delivered through it. Even a simple table view was going to require significant coding and ultimately maintenance. The close-coupling of application with presentation was also a concern for future changes to data, user interface and application logic.
Exploring the implications of moving to a conventional HTML page for the user interface, I chose to work with jQuery. There are other candidates like Angular JS.
A common issue is a table or grid of data (often sourced from a spreadsheet). Presenting this as part of the user interface was a suitable example of the interface issues.
jQuery has a DataTables plugin which would save a heap of styling, structuring and client handler coding that is needed when you write your own user interface. I also chose to make use of the Google Drive Hosting facility as an easy path to get a working set of web pages available without engaging with hosting services.
First I used a demo from DataTables to see the default table functionality from delivered from a web page on Google Drive Host. This is just a script executing in the browser on ready. The data is embedded in the script. The page is available here.

<script type="text/javascript" charset="utf8">
   $(document).ready(function() {
   $('#demo').html( '<table cellpadding="0" cellspacing="0" border="0" class="display" id="example"></table>' );
$('#example').dataTable( {
   "aaData":  [
       /* Reduced data set */
       [ "Trident", "Internet Explorer 4.0", "Win 95+", 4, "X" ],
       [ "Trident", "Internet Explorer 5.0", "Win 95+", 5, "C" ],
       [ "Trident", "Internet Explorer 5.5", "Win 95+", 5.5, "A" ],
       [ "Trident", "Internet Explorer 6.0", "Win 98+", 6, "A" ],
       [ "Trident", "Internet Explorer 7.0", "Win XP SP2+", 7, "A" ],
       [ "Gecko", "Firefox 1.5", "Win 98+ / OSX.2+", 1.8, "A" ],
       [ "Gecko", "Firefox 2", "Win 98+ / OSX.2+", 1.8, "A" ],
       [ "Gecko", "Firefox 3", "Win 2k+ / OSX.3+", 1.9, "A" ],
       [ "Webkit", "Safari 1.2", "OSX.3", 125.5, "A" ],
       [ "Webkit", "Safari 1.3", "OSX.3", 312.8, "A" ],
       [ "Webkit", "Safari 2.0", "OSX.4+", 419.3, "A" ],
       [ "Webkit", "Safari 3.0", "OSX.4+", 522.1, "A" ]
   ]  ,
   "aoColumns":  [
       { "sTitle": "Engine" },
       { "sTitle": "Browser" },
       { "sTitle": "Platform" },
       { "sTitle": "Version", "sClass": "center" },
       {
               "sTitle": "Grade",
               "sClass": "center",
               "fnRender": function(obj) {
                       var sReturn = obj.aData[ obj.iDataColumn ];
                       if ( sReturn == "A" ) {
                               sReturn = "<b>A</b>";
                       }
                       return sReturn;
               }
       }
   ]
   } );  
   } );  
   </script>

The default table looked good enough to work with so I proceeded to linking the page to some application data. Since the DataTables feature can map to a 2D table like a spreadsheet, it seemed reasonable to use the object defining the table as the interface between application and presentation. A layout designer can simply think of the table in the UI as a box, filled on ready. The apps designer can add columns, sort specifications etc as appropriate.

The script was then required to get the data using a jQuery getJSON call. JSONP is required in the interface because of the cross domain.

<script type="text/javascript" charset="utf8">

  $(document).ready(function() {
  $('#demo').html( '<table cellpadding="0" cellspacing="0" border="0" class="display" id="example"></table>' );
  var proxyJsonp = "https://script.google.com/macros/s/AKfycbxqfgshdfgd2WmRQ4/exec";
  $.getJSON(proxyJsonp + "?prefix=?", null, function(data) {

   $('#example').dataTable( {
   "aaData": data.aaData  ,
   "aoColumns": data.aoColumns
   } );
});
} );
</script>

The application side of the interface was supplied by an apps script using the content service.


function doGet(request) {
 var result ={
   "aaData":  [
    /* Reduced data set */
    [ "Trident", "Internet Explorer 4.0", "Win 95+", 4, "X" ],
    [ "Trident", "Internet Explorer 5.0", "Win 95+", 5, "C" ],
   
    [ "Webkit", "Safari 2.0", "OSX.4+", 419.3, "A" ],
    [ "Webkit", "Safari 3.0", "OSX.4+", 522.1, "A" ]
   ]  ,
   "aoColumns":  [
    { "sTitle": "Engine" },
    { "sTitle": "Browser" },
    { "sTitle": "Platform" },
    { "sTitle": "Version", "sClass": "center" },
    {
    "sTitle": "Grade",
    "sClass": "center",
    "fnRender": function(obj) {
    var sReturn = obj.aData[ obj.iDataColumn ];
    if ( sReturn == "A" ) {
    sReturn = "<b>A</b>";
    }
    return sReturn;
    }
    }
   ], "user":user
   };

 return ContentService.createTextOutput(request.parameters.prefix + '(' + JSON.stringify(result) + ')')
   .setMimeType(ContentService.MimeType.JSON);
}

This produced a fairly responsive web page so I added real data from a spreadsheet by replacing the apps script.
function doGet(request) {
 var sourceSpreadSheet = SpreadsheetApp.openById("0AthfgyjkfyzRQd00xRUE");  // ourPeople
var dataArray = sourceSpreadSheet.getDataRange().getValues();
var aadata = [];
 var aoColumns = [];
 var col = {};
 var headers = dataArray[0];
 // all except header
 for ( var i=1;i<dataArray.length;i++) {
   aadata.push(dataArray[i]);
 }
 // headers
 for (i=0;i<headers.length;i++) {
   col = { "sTitle": headers[i]};
   aoColumns.push(col);
 }
 var result ={
   "aaData":  aadata,
   "aoColumns": aoColumns, "user":user
   };

 return ContentService.createTextOutput(request.parameters.prefix + '(' + JSON.stringify(result) + ')')
   .setMimeType(ContentService.MimeType.JSON);
}

This code made the whole thing insensitive to number of columns. I published as available to users in my domain and it required users to be logged on their Google Apps account before returning table. As written, the web page just did not build the table if user not logged on.

So that was fairly painless thanks to the giants that had gone along similar paths (+Riël Notermans +keisuke oohashi). I didn’t have to worry about OAuth2, signon etc because it all happens under the covers.
However, I am now at this Warning!

Warning: Be very careful when using this JSONP technique in your scripts. Because it's possible for anyone to embed the script tag in their web page, you may be "tricked" into executing the script when you visit a malicious website, which can then capture the data returned. It's a best practice to make sure that your JSONP scripts are read-only and only return non-sensitive information.

Part 2 outlines a possible solution to this.