Wednesday, 13 February 2013

Mixing User Information and Private Information in Google Apps Script

Going back to real coding. Well Google Apps Script anyway:)
Google Apps Script presents a few hurdles to an application designer if you want to work in the users' context with information derived from your private documents or domain.I needed to work with
  • some information derived from a document within my protected Google Apps document store  (document private to me, within a domain where sharing documents outside domain is forbidden)
  • the identity of user (may be outside domain but with a Google Account)

The identity of a user is available from Session.getActiveUser().getEmail() but this will fail in interesting ways unless the script is published to run under the user id of the user running the script.

The private document cannot be shared outside the domain, and in this particular case, access to the full document is restricted to one user within the domain. Scripted access to the information on the document must be done by a script published  to run as that privileged user.

So we need two scripts that talk to each other. One doing the conversation with the user and one operating as me in the background. UrlFetchApp.fetch() provides the means for one script to call another with parameters. ContentService.createTextOutput() provides the means to set the response to a regular format like JSON or XML.

The following scripts demonstrate the mechanics.

1. The front end UI interaction with the users

function doGet() {
 var app = UiApp.createApplication();
 var email = Session.getActiveUser().getEmail();
 var label = app.createLabel('Your email address is '+email);
 // get the private data object
 var obj = callUserWithPermission(email);
 // put out data originating from private world and data round tripped from user world to private world and back
 var label2 = app.createLabel('App effective user was '+obj.available + " Originating user was " + obj.actual);
 // Proceed with business on user side .... initial UI here
 return app;

function callUserWithPermission(email) {
 try {
 // use the run as privileged user script
    var response = UrlFetchApp.fetch(""+encodeURIComponent(email));
    var parsedResponse = JSON.parse(response.getContentText());
    return parsedResponse;
 catch(e) {
    Logger.log("error "+JSON.stringify(e))

This script must be deployed so that it is run as the script user and available to anyone. It will insist on a Google Account login and acceptance by user of exposure of the email id.

2. The back end interaction with private domain documents

function doGet(e) {
 // see content service simple example
 // receive the info from "run as user" script as parameter "who"
 try {
    var app = UiApp.createApplication();
    var effective = Session.getEffectiveUser().getEmail();
    var result = {
     available: effective,
     actual: e.parameter.who
    // DO the business function and add info to result
    return ContentService.createTextOutput(JSON.stringify(result))
 catch(ex) {
    throw new Error("In testgetprivate doGet()"+ex.message)

This script must be deployed so that it is run as the script owner and available to anyone including anonymous users. This certainly should give you something to think about … this script can be run by anyone, from anywhere who knows the, admittedly difficult to guess, URI. Is the information returned very sensitive (not in my case)? Is the risk of exposure (very slight unless the developer leaves the source code lying around) balanced by the ease of implementation. Alternatives requires a whole authentication mechanism built into your app.

You may find that you cannot deploy a standalone script like this because of the "no sharing of documents beyond domain" restriction set up by administrator. In that case, put the script into a public site and deploy it from there.