Blog Posts

Getting Data into your HTML page from Google Apps

Blog: Dave Thinking Aloud - David French

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:


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:
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)

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/getting-data-into-your-html-page-from-google-apps/?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

×