In this post Peter Bauer provides a proof of concept showing the use of Google Apps Script as a rest server where the ultimate work is done as originating user rather than the script owner as in my example.
Interesting test, but there is a risk that Google will decide this is not how things should be and stamp on it.
Wednesday, 1 May 2013
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.
Logger.log(JSON.stringify(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++) {
aadata.push(dataArray[i]);
}
// headers for DataTable
for (i=0;i<headers.length;i++) {
col = { "sTitle": headers[i]};
aoColumns.push(col);
}
// result object return by content service
var result ={
"aaData": aadata,
"aoColumns": aoColumns,
"user":user,"success":true
}
}
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)
MailApp.sendEmail(user,
"Session Second Factor Security",
"Copy and paste this code into the Session Code Entry Box\n"+sessionValue);
// save it in the userproperties
UserProperties.setProperty('session',sessionValue);
// 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) + ')')
.setMimeType(ContentService.MimeType.JSON);
return html;
}
function generateGUID () {
// rfc4122 version 4 compliant solution from broofa
//http://stackoverflow.com/questions/105034/how-to-create-a-guid-uuid-in-javascript
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);
});
}
<head>
<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="//ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="//ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.0.min.js"></script>
<script type="text/javascript" charset="utf8" src="//ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></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 = "https://script.google.com/a/macros/example.com/s/AKfylkjghffdvN2tXR6fo_c/exec";
var sessionparam = "session="+Cookies['ppkcookie1'];
$.getJSON(proxyJsonp + "?" +sessionparam + "&prefix=?", null, function(data) {$('#example').dataTable( {
"aaData": data.aaData ,
"aoColumns": data.aoColumns
} );
document.getElementById("user").innerHTML=data.user;});
} );</script>
<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 {
Cookies.create(name,x,7);
alert('Cookie created');
}
}</script>
</head>
<body>
<h1>JQUERY DataTables Page Hosted in Google Drive with cookie for added security</h1>
<div id="header">
</div>
<div id="content">
<h3>
<form name="cookieform" action="#"><p>
Session Code <input name="cookievalue" />
</p></form><p><a href="javascript:saveIt('ppkcookie1')" class="page">Save Session Code</a><br />
</h3>
</div>
<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></body>
</html>
- 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
Following the documentation for the content service, I got to this dramatic 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.
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.
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) {Logger.log(JSON.stringify(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++) {
aadata.push(dataArray[i]);
}
// headers for DataTable
for (i=0;i<headers.length;i++) {
col = { "sTitle": headers[i]};
aoColumns.push(col);
}
// result object return by content service
var result ={
"aaData": aadata,
"aoColumns": aoColumns,
"user":user,"success":true
}
}
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)
MailApp.sendEmail(user,
"Session Second Factor Security",
"Copy and paste this code into the Session Code Entry Box\n"+sessionValue);
// save it in the userproperties
UserProperties.setProperty('session',sessionValue);
// 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) + ')')
.setMimeType(ContentService.MimeType.JSON);
return html;
}
function generateGUID () {
// rfc4122 version 4 compliant solution from broofa
//http://stackoverflow.com/questions/105034/how-to-create-a-guid-uuid-in-javascript
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);
});
}
HTML Code
<html><head>
<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="//ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="//ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.0.min.js"></script>
<script type="text/javascript" charset="utf8" src="//ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></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 = "https://script.google.com/a/macros/example.com/s/AKfylkjghffdvN2tXR6fo_c/exec";
var sessionparam = "session="+Cookies['ppkcookie1'];
$.getJSON(proxyJsonp + "?" +sessionparam + "&prefix=?", null, function(data) {$('#example').dataTable( {
"aaData": data.aaData ,
"aoColumns": data.aoColumns
} );
document.getElementById("user").innerHTML=data.user;});
} );</script>
<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 {
Cookies.create(name,x,7);
alert('Cookie created');
}
}</script>
</head>
<body>
<h1>JQUERY DataTables Page Hosted in Google Drive with cookie for added security</h1>
<div id="header">
</div>
<div id="content">
<h3>
<form name="cookieform" action="#"><p>
Session Code <input name="cookievalue" />
</p></form><p><a href="javascript:saveIt('ppkcookie1')" class="page">Save Session Code</a><br />
</h3>
</div>
<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></body>
</html>
Cookies usage was based on http://www.quirksmode.org/js/cookies.html
Wednesday, 20 March 2013
Making Use of Google Apps Data
Google Apps provides a number of office productivity components - eg Drive , Document, Spreadsheet, Drawing and Calendar which are often seen as single purpose personal tools. Underlying these components are some useful data structures that can be used to assist the broader business processes.
For example, Calendar can be used not only to provide a personal reminder service but as the core of a job management system.
For Karen at the centre of busy removals business in Sydney, the calendar has become the tool of choice for keeping the team aware of what jobs are coming up, where they should be and when the trucks are earning money. A potential job comes in from a web site contact, phone call, mail or a chat in the street and can be pencilled in as an event for a date/time that does not conflict with other commitments of team resources. A document is created in the Google Drive “Jobs” folder. Geoff, as director of operations, can glance at the month ahead and see the committed and potential jobs. Since he is often out driving or finalising details with customer, it is handy that the calendar is just as visible on his I-Phone as in the office. Karen is also out and about with customers and needs to be able to see the resources available as she commits to a new job (perhaps moving the ‘pencilled-in’ time to a committed one that will allow the customer to meet completion schedule on a purchase).
When the job is committed, Karen completes a summary of the move in the calendar title and description. The detail documents (inventory, quote, property access etc) becomes part of the ever growing set of job information linked by the common event.
Geoff will be assigning people to the job (in calendar, simply by adding them as ‘guests’). Team members then relate to the jobs as for any calendar invitations (accept, decline, etc) and see their own workload in their calendars.
A jobs list provides a handy customised summary of the current working period by combining information from the calendar and the jobs folder. Visibility of detailed information is limited by the standard sharing protocols of Google Apps (for example, the quote may only be seen by the team leader, but the inventory might be available to all).
This works simply by a bit of office discipline:
- Giving jobs an identifier and using that identifier when creating document file names and calendar events
- Adding assigning people to jobs as guests in the calendar event
… and a bit of Google Apps Script to create a custom list from data in the calendar and data in the file names
If you want to leverage your standard office tools to enhance business processes in your organisation or project, drop me a line.
Thursday, 14 March 2013
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.
Subscribe to:
Posts (Atom)
