Wednesday, 20 August 2014

Google Drive and User Interfaces

I have explored a few ways of interacting with Google Drive files and folders for a simple document management system. The user-interface needs to present folders and files which represent real world artefacts (eg people , places). Business operations are initiated by the user making a selection on a folder or file name.

The Drive and Google Apps scripting features have developed over time and I have tried using

For me, using the Google Drive SDK  has proven the simplest to build into a responsive user interface and best performing for the user.

The UI Service approach provided an early introduction to interacting with Google Apps script but it is very limited in function and locks the user interface into the script. It carries all the overheads of generating the user interface at run time as well.

HTMLService does provide the familiarity of HTML/CSS/JS development but has limitations and performance impacts caused by CAJA.

Calling a Google Apps Script from the Google Drive user interface suits users who spend there time interacting with Drive directly but selection of action on folder/file becomes a multiclick operation and subsequent user interface has the limitations of HTMLService .

The filepicker provided for Google Drive is functional and has the advantage of being ready to use with good performance. However the pop-up iframe approach may not suit all design styles and customisation is limited. In particular, presenting the file/folder list initially random order slows down the user interaction.

The direct use of the Google Drive SDK has been simplified recently  by improved documentation of the authorisation process. I took the quickstart example and built an html page of links from folder names corresponding to people of interest. It is then simple to incorporate this into a comprehensive user interface for the business user.

Performance and flexibility is the key for this approach. I get a few hundred folder references generate in 3 seconds and can then have a js expert work in there usual toolset to develop responsive bootstrap, jQuery or whatever interface style is required.

Modifed Quickstart example.

    <meta http-equiv="Content-type" content="text/html;charset=UTF-8">
    <script type="text/javascript">
      var CLIENT_ID = '';
      var SCOPES = '';

       * Called when the client library is loaded to start the auth flow.
      function handleClientLoad() {
          console.log('handleClientLoad:'+new Date().getTime())
        window.setTimeout(checkAuth, 1);

       * Check if the current user has authorized the application.
      function checkAuth() {
                    console.log('CheckAuth:'+new Date().getTime())
            {'client_id': CLIENT_ID, 'scope': SCOPES, 'immediate': true},

       * Called when authorization server replies.
       * @param {Object} authResult Authorization result.
      function handleAuthResult(authResult) {
                    console.log('handleAuthResult:'+new Date().getTime())
        var authButton = document.getElementById('authorizeButton'); = 'none';

        if (authResult && !authResult.error) {
          // Access token has been successfully retrieved, requests can be sent to the API.
        } else {
          // No access token could be retrieved, show the button to start the authorization flow.
 = 'block';
          authButton.onclick = function() {
                  {'client_id': CLIENT_ID, 'scope': SCOPES, 'immediate': false},

 /*  List folders based on a search
* specify the owning folder and mimetype = folder in the search parameter (q)
* set maxResults  (can do paged calls if actual results exceed 1000) 
function getItems() {
    console.log('getItems:'+new Date().getTime())
    var start = new Date();
    var request = gapi.client.request({
      'path': 'drive/v2/files',
      'method': 'GET',
      'params': {
        'q': 'mimeType="application/" and "0B90FGJizRd-gX25PTS1CTUF0eHM" in parents  and trashed = false',
       'maxResults': '400'
     console.log("elapsed: "+(new Date()-start))
/* Sort the result returned from drive api into title order
*   insert html paragraph for each title


function listItems(resp) {
              console.log('listitems:'+new Date().getTime())
    var start = new Date().getTime();
    var result = resp.items.sort(function(a, b){
     var nameA=a.title.toLowerCase(), nameB=b.title.toLowerCase()
     if (nameA < nameB) //sort string ascending
      return -1 
     if (nameA > nameB)
      return 1
     return 0 //default return value (no sorting)

 console.log('items: '+result.length)
    var i = 0;
    var element = document.getElementById("div1");  // insertion point in html
      for (i=0;i<result.length;i++) {
          var para = document.createElement("p"); // create a link element in a para
          var aref = document.createElement("a");
          var node = document.createTextNode(result[i].title);
           aref.setAttribute('href',result[i].alternateLink); // link to driver folder
    console.log("elapsed: "+(new Date().getTime()-start));
    <script type="text/javascript" src=""></script>
      <input type="button" id="authorizeButton" style="display: none" value="Authorize" />
  <div id='div1'></div>

Thursday, 12 December 2013

Sharing Drive Folders with User Groups

A little issue that has caused a day's worth of pain ...
If you share a folder with a Google Apps user group and, later, add a user to that group the new member will not see the folder or it’s content until that user explicitly follows a link to the folder. This issue (see here for a variant) is not confined to the Google Drive UI, it will occur in scripted access to the folder as well.
It is easy to make this a self-service function by publishing the link to the top level folder that is to be shared in a welcome to user/role page.

Sunday, 17 November 2013

Keeping information within the domain

Google Apps administrators have some useful controls and tools to track where documents are being shared. Often there is a fundamental policy about sharing outside the domain (for example,  to limit exposure of privileged or private information).
An  administrator can set up the Drive app to prevent sharing beyond the domain.
Note that this does not revoke existing shares! If you are closing off a loophole in your security, then you can discover the documents and files that have been shared publicly (beyond the domain) using a script or custom app like the General Audit Tool.
This also provides a means of enabling sharing of documents and files by exception. Keep the domain normally secured so that users cannot share outside the domain but open the gate briefly to share a document or folder publicly and then close it again. This is handy if you want to use Google Drive Hosting on an otherwise closed domain.

Tuesday, 15 October 2013

Document Manager on Google Apps

I have put together some apps scripts , and conventional HTML/JS/jQuery web application to form a document management system for small business/non-profits. Because it uses the underlying access control of Google Apps, users can approach the managed documents either from the Google Drive interface or from the customised business interface.
The application implements organisation-wide business rules to files/documents stored within Google Drive. Essentially, it ensures that documents are named, shared and retained according to the enterprise rules for record and document management.
In the current implementation a particular business information entity type (eg Clients) has its access controlled by administrative divisions (eg Regions).
A nominal user (DocMan) is assigned ownership of all documents and regular users get access to the documents according to their membership of user groups. Effectively, the normal user asks DocMan to undertake administrative actions through the application.
The organisation can specify a fixed structure of folders and the name of the root folder. Access control is handled exclusively by the standard Google Apps Users, User-Groups and Sharing mechanisms.
The diagram below outlines the relationship between folders, documents, users and groups used with the Document Management Application.

More on Google Apps Script HtmlService performance

Following on from the post where I identified some performance issues with HtmlService there are some additional 'best practices' to be considered for performance reasons.

  • Minimise number of source files css and js included in the html. The initial load time is sensitive to the number of files to be processed.
  • Avoid source file loads of css and js on client side. These end up as serial operations (in contrast to the usual parallel browser operations). This is a significant factor even if these url are cached.
  • Avoid un-cached images (icons and the like). With references buried in css and js files, these seem to take a long time to load and also fall into the serial operations category.
If you are using Twitter Bootstrap or similar toolsets, this is hard to achieve. Repackaging the css to enable fast loading undoes most of the benefits of using Bootstrap :(

Tuesday, 10 September 2013

Google Apps Script HtmlService Performance

If you are moving to HtmlService from UiApp, you may encounter some performance issues. First look at the best practices. Hopefully that section will expand over time.
HtmlService introduces Caja to the mix for good or ill and the processing changes quite significantly.
To explore the performance issues around some design decisions, I developed a simple form for an upload to Google Drive. Looking forward to a time when form is as important as function, I chose to style with Twitter Bootstrap and use a bootstrap extension to style the file input component.
Input form image
Simple input form
This means that, as well as the html, there a few .css and .js files to load.
Note that this example has no submit functionality coded, just the display and upload file selection.

I produced an Apps script that simply loaded the form, using bootstrap and jQuery from CDN and additional css and js from Google Drive hosting. Published as a web app here
function doGet() {
  return HtmlService.createHtmlOutputFromFile('bootstrap').setSandboxMode(HtmlService.SandboxMode.NATIVE);
 To provide a comparison with a hosted web site, I put the same html onto the Google Drive host, using the same .css and .js locations. Available here

The performance difference measured by Chrome at the desktop is quite startling. The Htmlservice script takes around 8seconds to display the form and a further 4seconds to complete loading the javascript files which make the form work. The Google Drive hosted version generally responded fully in 1.4seconds (with occasional outliers around 3.5seconds) which is not bad for a service intended for development rather than high performance service delivery.

A noticable feature of the HtmlService working is that the .js gets are sequential while the hosted version did them all in parallel.

[EDIT The results for the Htmlservice script were so bad that I explored possible workarounds. Putting the css and js files into the project and including them (as described in best practices) did improve the service time to 5-6 seconds. I could not get the jquery.min.js into the project ... the copy crashed in chrome. However, this approach does rather go against the simplicity of including the scripts from a CDN.]

From this, I shall be avoiding Htmlservice wherever possible. I do prefer the separation between presentation and Google Apps function that comes from JSON-P and Javascript.

Your milage may vary of course. I am seeing this in New Zealand at the end of an ADSL link.

Wednesday, 21 August 2013

Using Google Drive as an Entry Point for Google Script

We have a structured set of folders in Google Drive mapping to the organisation’s business functions. Think of the office filing cabinet.
There is a folder for each client. For control purposes and overall convenience, the clients are assigned to regional groups (folders again).
There are business operations that should be controlled to apply some records management auditability, and for which the user may require a guided path.
The documents on G Drive are essentially the business records (requests for action, review reports, closure etc) and form the usual source of information for staff working with the client. So if a staff member is working with a client, it is reasonable that they would be reviewing the records.


There are several business actions that can be broadly visualised as a form entry, validation, response sequence. For example: Setting up a new client will require setting up a folder within the region folder; naming the folder; adding a structure of subfolders.
While we could maintain a database of clients and drive operations from that, the key data is actually in the base documents so we have chosen to script the business operations as functions of Drive itself. Our functions are create or open actions taken at the various folder levels in the structure and activated by a right click.

The Apps Script is built in 3 parts
  1. Integration with G-Drive which handles the initial authorisation; token handling; and calling either the open or create functions. This is based on an introduction that +Arun Nagarajan  put together and code samples from his team. A good startpoint is I have included my commented source below.
  2. Open and Create. Each of these produce a menu of operation that the user can chose from. These scripts receive the state parameter from G-Drive interface which allows the folder or files selected to be identified.
  3. Detail action scripts. These separate out the individual functions and simplify maintenance.

Source Code

/*  Integration of Google Drive with the Actions of CCSLT Document Manager
*The interconnection with Drive is based on Arun's DRIVE SDK + GAS Presentation and code
*Refresh token logic has been added to give continuous use capability
*Handles the installation for the user (execute script with no params and authorise through OAuth2 conversation
// Globals used in api constructs
var TOKEN_URL = '';
var REDIRECT_URL= ScriptApp.getService().getUrl();  // url of *this* script exec
var TOKENPROPERTYNAME = 'GOOGLE_OAUTH_TOKEN'; // access token valid until time expire or revoke by user
var REFRESHPROPERTYNAME = 'GOOGLE_OAUTH_REFRESH'; //oauth2 refresh token valid until revoked by user
var EXPIRYPROPERTYNAME = 'GOOGLE_OAUTH_EXPIRY' ; // expiry of oauth2 token time to do refresh!
// OAUTH2 data from API project - needs to be replaced if new project is created or project generates new secret/id
var CLIENT_ID = 'ghxcvhxcvhjdfshgjdsfahg';
var CLIENT_SECRET = 'fdsvvdfsh,gvsadhgfsdkj';
var DRIVE_API_URL = '';
// maintain scope in step with changing application requirements
var SCOPE = '';
/* Main entry point
* functions depend on code and state parameters
* if state present ... app is installed
* createAction called from Google Drive when user selects DOCMAN app from type choice at CREATE menu state=create
* fileAction called from Google Drive when user selects DOCMAN app from type choice at file selection menu state otherwise
* if state not present then it is an installation and authorisation call

function doGet(e) {
 var HTMLToOutput;
 // business operations of Drive API send state parameter, authentication returns code, initialisation has no parameters
   var state = JSON.parse(e.parameters.state);
   if(state.action === 'create'){
     // called as a result of  selection from CREATE menu of Google Drive user interface actually creates a UIApp
     return createAction(state);

   else {
     // called as a result of selection from right click on file menu of Google Drive user interface
    // HTMLToOutput=fileAction(state);
    // return HtmlService.createHtmlOutput(HTMLToOutput)
    return fileAction(state);
 else if(e.parameters.code){//if we get "code" as a parameter in, then this is a callback from the install authorisation dance
   getAndStoreAccessToken(e.parameters.code);  // installer
   HTMLToOutput = '<html><h1>App is installed, you can close this window now or navigate to your <a href="">Google Drive</a>.</h1></html>';
 else {//we are starting from scratch or resetting (result of running the /exec of this script)
   HTMLToOutput = "<html><h1>Install this App into your Google Drive!</h1><a href='"+getURLForAuthorization()+"'>click here to start</a></html>";
 return HtmlService.createHtmlOutput(HTMLToOutput);
* first step of OAUTH2 dance to get an authorisation code
function getURLForAuthorization(){
 return AUTHORIZE_URL + '?' +
   'redirect_uri='+REDIRECT_URL +
     '&response_type=code' +
       '&client_id='+CLIENT_ID +
           '&scope=' + encodeURIComponent(SCOPE) +
* second step of  OAUTH2 dance to exchange authorisation code for access key and refresh key
function getAndStoreAccessToken(code){
 var payload = "client_id=" + CLIENT_ID
 payload = payload + "&redirect_uri="+encodeURIComponent(REDIRECT_URL)
 payload = payload + "&client_secret="+CLIENT_SECRET
 payload = payload + "&code="+encodeURIComponent(code)
 payload = payload + "&scope=&grant_type=authorization_code"

 var parameters = {
   'method' : 'post',
   'contentType' : 'application/x-www-form-urlencoded',
   'payload' : payload
 var response = UrlFetchApp.fetch(TOKEN_URL,parameters).getContentText();
  var tokenResponse = JSON.parse(response);
 // store the access token for later retrieval
 UserProperties.setProperty(TOKENPROPERTYNAME, tokenResponse.access_token);
 // store the refresh token for use when access token expires
 UserProperties.setProperty(REFRESHPROPERTYNAME, tokenResponse.refresh_token);
 // store the expiry time to determine when access token expires (expiry is returned as seconds to go - converted to UTC time in msecs)
 UserProperties.setProperty(EXPIRYPROPERTYNAME,tokenResponse.expires_in * 1000 +new Date().getTime());
* Handles the token refresh function of OAUTH2 using saved refresh token
function refreshAccessToken(){
 var payload = 'client_id=' +CLIENT_ID+
     var parameters = {
       'method' : 'post',
       'contentType' : 'application/x-www-form-urlencoded',
       'payload' : payload
 var response = UrlFetchApp.fetch(TOKEN_URL,parameters).getContentText();

 var tokenResponse = JSON.parse(response);
 // store the token for later retrival - note refresh token does not expire
 UserProperties.setProperty(TOKENPROPERTYNAME, tokenResponse.access_token);
 UserProperties.setProperty(EXPIRYPROPERTYNAME,tokenResponse.expires_in * 1000 +new Date().getTime());
 return tokenResponse.access_token
* Construct fetch options

function getUrlFetchOptions() {
 return {'contentType' : 'application/json',
         'headers' : {'Authorization' : 'Bearer ' + isTokenValid,
                      'Accept' : 'application/json'}};
* CHECK IF STORED token is valid, if not use refresh token to get new one
function isTokenValid() {
 var now = new Date().getTime();
 var storedToken = UserProperties.getProperty(TOKENPROPERTYNAME);
 var storedRefresh = UserProperties.getProperty(REFRESHPROPERTYNAME);
 var expiry = UserProperties.getProperty(EXPIRYPROPERTYNAME);
 // if expired then refresh storedtoken
 if (expiry<= now){
   storedToken = refreshAccessToken();
 return storedToken;