Getting Data into your HTML page from Google Apps

In the scenario where the ‘data’ is in Google Apps (in a spreadsheet, ScriptDB, documents or being integrated from a number of sources in a script) it is worth considering breaking the presentation from the processing work and having a well defined interface between the two.

+Riël Notermans raised a question of how to get tables nicely from Google Apps into a web page. It is tempting to use the HTMLService within an apps script to respond directly to the request from a user with HTML. For me this has some downside factors:

• The whole page is constructed server-side before being sent to the browser maximising latency
• HTML build will follow “get Data” operations maximising service time
• Changes to look and feel HTML have to be made in script increasing risk of introducing error with change
• What you can do on the page is limited by HTMLService. So it may be impossible to incorporate an organisation wide look and feel (say using Bootstrap)
• Difficult to reuse the ‘data service’ in other ways which either increases the development workload or constrains the end users to the delivered solution.
• There is a performance impact from CAJA

Consider a common situation where the data is being presented as a 2-D array or table of information. We tend to incorporate some common behaviours into the display or user interface around this simple table concept. For example:

• Paging and scrolling through rows and columns
• Sorting by one or more columns
• Searching within table (beyond visible elements)

Rather than build your own Google Apps Script to do these things even as a generic library, commercial shops are likely to standardise on existing tools like jQuery. Although jQuery is supported by GAS HTMLService , others, like Twitter Bootstrap, do not play nicely with GAS at present.

Following the best practice recommendation to load data asynchronously, the apparent performance of presenting large tables can be improved by getting the data in two or more passes. To delivery a default format jQuery DataTable, the HTMLService would incorporate a script like this.

<script type="text/javascript" charset="utf8" src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>

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

/*  Ready function */

$(document).ready(function(){

var runner = google.script.run.withFailureHandler(onFailure);

var runner2 = google.script.run.withFailureHandler(onFailure);

runner.withSuccessHandler(onSuccess).getData();

runner2.withSuccessHandler(onSuccess2).getData2();

});

/* function done when getData is successful */    

var onSuccess = function(data){

var aDataSet = data.slice(1);  // all except header

var head = [];  // headers

data[0].forEach(function(e){

head.push({'sTitle': e});

});

/* jQuery DataTable insertion happens after data load*/

$('#demo').html( '<table cellpadding="0" cellspacing="0" border="0" class="display" id="example"></table>' );

$('#example').dataTable( {

"aaData": aDataSet,

"aoColumns": head

});

}

var onSuccess2 = function(data){

var aDataSet = data.slice(1);  // all except header

/* jQuery DataTable refresh happens after 2nd (longer) data load*/

$('#example').dataTable().fnClearTable();  // clear existing

$('#example').dataTable().fnAddData(aDataSet);  // reload full table

$('#example').dataTable().fnDraw();

}

/* function done if error in getdata */

var onFailure = function(err){

alert(err.message);

}

</script>

The GAS script has two callbacks, the first presents a useful amount of data (say the first page) so the user gets something to work with quickly and the second returns the whole table data. The table is built when the first call is successful and refreshed when the second is successful.

You can standardise on the datamodel used by the jQuery DataTables extension as the interface between presentation and web service layers or develop a generic table model (perhaps incorporating paging through a 3rd dimension of data) that can be mapped to the jQuery DataTable on the client-side script.

A better approach is to use the content service to return JSON-P which I explored here.

jQuery supports AJAX operations for getting data, (and sorting, paging etc. when server side processing is more appropriate - large tables, mobile devices)