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.

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.

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

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.

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.

Using Google Drive as an Entry Point for Google Script

Scenario

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.

Scripting

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 https://www.youtube.com/watch?v=R71oo-5NmPE. 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 AUTHORIZE_URL = 'https://accounts.google.com/o/oauth2/auth';

var TOKEN_URL = 'https://accounts.google.com/o/oauth2/token';

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 = 'https://www.googleapis.com/drive/v2';

// maintain scope in step with changing application requirements

var SCOPE = 'https://www.googleapis.com/auth/drive.install https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/drive.metadata.readonly';

/* 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

 if(e.parameters.state){

   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="https://drive.google.com">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 +

         '&approval_prompt=force'+

           '&scope=' + encodeURIComponent(SCOPE) +

             '&access_type=offline';

 

 

 

}

/*

* 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+

   '&client_secret='+CLIENT_SECRET+

     '&refresh_token='+UserProperties.getProperty(REFRESHPROPERTYNAME)+

       '&grant_type=refresh_token'

     

     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;

}

Getting Data into your HTML page from Google Apps

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:

• The whole page is constructed server-side before being sent to the browser maximising latency
• HTML build will follow “get Data” operations maximising service time
• Changes to look and feel HTML have to be made in script increasing risk of introducing error with change
• What you can do on the page is limited by HTMLService. So it may be impossible to incorporate an organisation wide look and feel (say using Bootstrap)
• Difficult to reuse the ‘data service’ in other ways which either increases the development workload or constrains the end users to the delivered solution.
• There is a performance impact from CAJA

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:

• Paging and scrolling through rows and columns
• Sorting by one or more columns
• Searching within table (beyond visible elements)

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)

GAS REST Server as calling user

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.

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.