Blog Posts

JSONP and Google Apps Script

Blog: Dave Thinking Aloud - David French

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.

Leave a Comment

Get the BPI Web Feed

Using the HTML code below, you can display this Business Process Incubator page content with the current filter and sorting inside your web site for FREE.

Copy/Paste this code in your website html code:

<iframe src="http://www.businessprocessincubator.com/content/jsonp-and-google-apps-script/?feed=html" frameborder="0" scrolling="auto" width="100%" height="700">

Customizing your BPI Web Feed

You can click on the Get the BPI Web Feed link on any of our page to create the best possible feed for your site. Here are a few tips to customize your BPI Web Feed.

Customizing the Content Filter
On any page, you can add filter criteria using the MORE FILTERS interface:

Customizing the Content Filter

Customizing the Content Sorting
Clicking on the sorting options will also change the way your BPI Web Feed will be ordered on your site:

Get the BPI Web Feed

Some integration examples

BPMN.org

XPDL.org

×