Saturday, 27 April 2013

Trees in Google Apps Script II

More learnings this week!

  • Don't try to work around deficiencies in Google products until you are desperate. After hacking a way through uncharted and untracked bush, you may find that Google has built a highway to your destination.
  • Technical documentation is really clear in hindsight - but then you do not need it.
In this post, I described a work around for the defective Tree widget in the Google Apps Script UI. I learnt a lot then. I am really pleased that the  long outstanding issue 307 from 2010 has been fixed.

I have spent a lot of time trying to get OAUTH2 to work for me to enable the use of Google Drive as an entry point for my Google Apps Script based applications. Reviewing the documentation now, it is obvious - why did it seem so much greek even with the benefit of +Arun Nagarajan 's video and code?

I will provide some details of the Google Drive + Google Apps Script implementation and purpose in a future post. Basically, folders and files are business records of the core business operation and it is natural for a significant class of users to use an open or create operation on a folder to initiate a business operation. It also allows business rules to be applied to the records (which may be scanned letters) before they become part of the stored information base.

Saturday, 13 April 2013

JSONP and Google Apps Script (Part2)

In an earlier post, I explored the use of the Google Apps Script Content Service supplying data to a web page in an effort to provide a reasonable degree of separation between a service view of Google Apps and a presentation layer. There were also considerable side-benefits in performance and freeing up of the way the web page can be built.

  • HTML Service in GAS in comparatively slow delivering even a simple page.
  • Adding large libraries like JQUERY and DataTables introduces further delays server-side
  • In my simple use of DataTables, simply painting the screen first time was very slow
The issue with JSONP is the exposure of script which can then be captured by a third party who may then induce an authorised user to load another page which includes the script and provides the third party with access to restricted data and controls. Note this does require an active attacker (information cannot accidentally leak).
With any information storage, you do have to consider the value and sensitivity of the information and the effort that a third party will go through to get it and match your response accordingly.
A simple method of providing assurance that the server is sending its data response to the right page (not some other party copy) involves a number of steps.

  • The request from the browser should contain a 'hard to guess' additional factor - a 'session key' used by the server script to verify the validity of the request in concert with the Google Account.
  • The session key is stored in a cookie on the local machine. The browser operation will prevent a page from a foreign site from obtaining cookies associated with your site.
  • Session key is generated by apps script and stored in a User Property. 
  • The new session key is returned to the user by an alternate path (email, SMS, UIApp) and entered into the original web page to be stored in cookie.
This strategy should be sufficient to negate the exposure of the script that initiates the JSONP exchange. Of course, it does not protect against all other attacks.

Example apps script code

function doGet(request) {
var start = new Date().getTime();
// get user to return in content
var user = Session.getActiveUser().getEmail();
// get session property to check with incoming session parameter
var sessionValue = UserProperties.getProperty('session');
Logger.log("got property sessionValue = "+sessionValue + " parameter = "+request.parameter.session);
if (request.parameter.session == sessionValue && request.parameter.session !== undefined ) {
// get the data into an array
var sourceSpreadSheet = SpreadsheetApp.openById("0At0FkhjjhjhjjhjhjjhkhjkhkjZYi15SEpjTkE");
var dataArray = sourceSpreadSheet.getDataRange().getValues();
Logger.log(new Date().getTime() - start);
var aadata = [];
var aoColumns = [];
var col = {};
var html = "";
var headers = dataArray[0];
// all except header for DataTable
for ( var i=1;i<dataArray.length;i++) {
// headers for DataTable
for (i=0;i<headers.length;i++) {
col = { "sTitle": headers[i]};
// result object return by content service
var result ={
"aaData": aadata,
"aoColumns": aoColumns,
else {
// not a valid session parameter create new and pass to user through alt path
sessionValue = generateGUID();
// send a mail message (SMS is alternative and arguably more secure but would use a random number for ease of use)
"Session Second Factor Security",
"Copy and paste this code into the Session Code Entry Box\n"+sessionValue);
// save it in the userproperties
// set return object
var result ={
"aaData": aadata,
"aoColumns": aoColumns,
"user":user, "success":false
Logger.log(new Date().getTime() - start);
// formatted for JSONP
html = ContentService.createTextOutput(request.parameters.prefix + '(' + JSON.stringify(result) + ')')
return html;
function generateGUID () {
// rfc4122 version 4 compliant solution from broofa

return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
return v.toString(16);


<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>JQUERY DataTables Page Hosted in Google Drive with cookie for added security</title>
<link rel="stylesheet" type="text/css" href="main.css" />
<link rel="stylesheet" type="text/css" href="//">
<script type="text/javascript" charset="utf8" src="//"></script>
<script type="text/javascript" charset="utf8" src="//"></script>
<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 = "";
var sessionparam = "session="+Cookies['ppkcookie1'];
$.getJSON(proxyJsonp + "?" +sessionparam + "&prefix=?", null, function(data) {
$('#example').dataTable( {
"aaData": data.aaData ,
"aoColumns": data.aoColumns
} );
} );
<script type="text/javascript" src="cookies.js"></script>
<script type="text/javascript">
function saveIt(name) {
var x = document.forms['cookieform'].cookievalue.value;
if (!x)
alert('Please fill in a value in the input box.');
else {
alert('Cookie created');
<h1>JQUERY DataTables Page Hosted in Google Drive with cookie for added security</h1>
<div id="header">
<div id="content">
<form name="cookieform" action="#"><p>
Session Code <input name="cookievalue" />
<p><a href="javascript:saveIt('ppkcookie1')" class="page">Save Session Code</a><br />
<p>Session cookie contains a hard to guess value to be passed in request and validated at server</p>
<p>Data returns from Google Apps Script JSONP</p>
<p>Includes sorting, paging and filtering by default.</p>
<p id="user">User ???????</p>
<p>User must be logged on to Google Apps Account for this data table to appear (by design!) </p>
<p>Entire data table loaded in one hit.</p>
<div id="demo">This iss where the table goes</div>

Cookies usage was based on