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.

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.

Break your own Google Apps security

2-step verification for Google Apps is a must for those serious about protecting their account and all it contains.

MightyText is handy for getting SMS messages to and from your desk.

Doing

both

is not so smart.  The 2-step verification sends an sms message to your registered phone number and MightyText will route that to your desktop browser where it pops up, conveniently for the opportunist with access to your machine.