Spreadsheet filter - view "my data" filtered to UiApp table

gistfile1.js

// Spreadsheet Filter - a script that runs as a service and allows users 
// to view just their data from a spreadsheet and optionally* others' data
// too as granted by the spreadsheet owner. 
//
// * requires a sheet named "Additional Access" with 2 columns:
//   "User" (1st column heading) Email of a user who has been delegated access 
//      to others' data; 
//   "Additional access" (2nd column heading) Comma separated list of emails of 
//      users for which the first user will be given access. Use * to indicate any user.
// Version: 11 March 2011

// The key if the spreadsheet we will report on
var SPREADSHEET_ID = "tSNqGd33UaMKebmiOUPiA3rA";

// In that spreadsheet, the sheet with the data
var SPREADSHEET_DATASHEET_NAME = "Sheet1";

// In that spreadsheet, the number of header rows in the sheet with the data
var SPREADSHEET_DATASHEET_NUMHEADERS = 1;
 
// The column we will match on the current user's email address;
var SPREADSHEET_DATASHEET_USERCOL = 3; //Use the number: A=1,B=2,Z=26 etc

// The columns we will total; e.g. [8,9,12];
var SPREADSHEET_DATASHEET_COLSTOTOTAL= [8]; //Use the number: A=1,B=2,Z=26 etc


var _tableheader = {
      "fontWeight": "bold"
      };

var _table = {
  //"color":"blue",
  "verticalAlign":"top",
  "borderCollapse":"collapse",
  "borderColor":"#ccc"
      };

//unique members of an array
Array.prototype.unique = function() {
    var o = {}, i, l = this.length, r = [];
    for(i=0; i<l;i++) o[this[i]] = this[i];
    for(i in o) r.push(o[i]);
    return r;
};
//trim leading and trailing whitespace
String.prototype.trim = function () {
    return this.replace(/^\s*/, "").replace(/\s*$/, "");
};
function applyStyles_(element, style){
  for (var key in style){
    element.setStyleAttribute(key, style[key]); 
  }  
}

function doGet(e) {
  var app = UiApp.createApplication();
  
// get spreadsheet things
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = ss.getSheetByName(SPREADSHEET_DATASHEET_NAME);

// .... populate app with ui objects ...
  app.setTitle(ss.getName() + " (filtered)"); 
   
// Create a vertical panel 
  var panel = app.createVerticalPanel(); 

// Add info to the panel
  //panel.add(app.createLabel("Using spreadsheet \"" + ss.getName() + "\" (" + ss.getId() + ")"));
  //panel.add(app.createLabel("Sheet name: " + sheet.getName()));
  //panel.add(app.createLabel("User:" + Session.getUser().getEmail()));
  //panel.add(app.createLabel("Active user: " + Session.getActiveUser().getEmail()));
  //panel.add(app.createLabel("Effective user: " + Session.getEffectiveUser().getEmail()));
  
// create a flextable and add to the panel
  var myTable = app.createFlexTable();
   for (var i in myTable) {
    Logger.log(i);
  }


  applyStyles_(myTable, _table);
  myTable.setBorderWidth(1);
  panel.add(myTable);
  
//Process the data
  processAllDataToTable_(sheet, myTable, app);

// add a no records line
  if (getKeysToProcess_(sheet).length === 0) {
    panel.add(app.createLabel("No records found for " + Session.getUser().getEmail()));
}
      
// add the panel to the app
  app.add(panel);
  return app;
}

// Gets an array of unique keys from the column  
//   column is a num where A=1 etc.
function getUniqueKeys_(sheet, column, numheaders) {
  var keys = [];
  //Logger.log("Start logging getUniqueKeys_");
  var lastrow = sheet.getDataRange().getLastRow();
  var values = sheet.getRange(numheaders + 1, column, lastrow-numheaders, 1).getValues();
  for (var i = 0; i < values.length; i++){
    for (var j = 0; j < values[i].length; j++){
      keys.push(values[i][j]);
    }
  }
  return keys.unique();
}

// Gets an array of email addresses (or *) representing all the users whose data
// the current user has the rights to see including self. 
// Requires a Sheet "Additional Access" with 2 columns:
//   "User" (1st column heading) Email of a user who has been delegated access 
//      to others' data; 
//   "Additional access" (2nd column heading) Comma separated list of emails of 
//      users for which the first user has access. Use * to indicate any user.
function getAccessibleUsersForCurrentUser_(spreadsheet) {
  var currentUser = Session.getUser().getEmail();
  var users = [];
  users.push(currentUser);
  var SPREADSHEET_DELEGATIONSHEET_NAME = "Additional Access";
  var sheet = spreadsheet.getSheetByName(SPREADSHEET_DELEGATIONSHEET_NAME);
  if (sheet==null) {
    return users; //break out now.
  }
  var values = sheet.getDataRange().getValues();
  for (var i = 0; i < values.length; i++){
    if (values[i][0] === currentUser) {
      var additional = values[i][1].split(",");
      for (var k = 0; k < additional.length; k++){
        if (additional[k].trim()==="*") {
          var users = ["*"];
          return users; //break out now.
        }
        users.push(additional[k].trim());
      }
    }
  }
  return users;
}
  
//Process the data. 
function processAllDataToTable_(sheet, table, app) {
  // Get the full range of ss data to process
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  // Only get rows for these
  var keys = getKeysToProcess_(sheet);

  var trows = 0; //table counters
  var tcols = 0;

  //OK, add the header row (and set style)
   for (var i = 0; i < SPREADSHEET_DATASHEET_NUMHEADERS; i++){
    for (var j = 0; j < values[i].length; j++){ //.setStyleAttribute("color", "red"
      //add as labels so we can do styles
      //table.setText(trows, j, prepareTableText_(values[i][j]));
      var tmpLabel = app.createLabel(prepareTableText_(values[i][j]));
      applyStyles_(tmpLabel, _tableheader)
      table.setWidget(trows, j, tmpLabel);
    }
   trows++;
  }
  
  // For all keys to process, process each (and add totals)
   for (var k = 0; k < keys.length; k++){
     var rowset = processOneSet_(keys[k], values);
     for (var i = 0; i < rowset.length; i++){
       for (var j = 0; j < rowset[i].length; j++){   
         //add as labels so we can do styles
         //table.setText(trows, j, prepareTableText_(rowset[i][j])); 
         var tmpLabel = app.createLabel(prepareTableText_(rowset[i][j])); 
         applyStyles_(tmpLabel, _table);
         table.setWidget(trows, j, tmpLabel);
       }
       trows++;
     }
   }

}

// Process one set
function processOneSet_(key, values) {
  //create a new array
  var set = [];
  //columns we need to total
  var colstototal = SPREADSHEET_DATASHEET_COLSTOTOTAL;
  // make asoc arrays for each column we need to total
  var totals = {};
  for (var i = 0; i < colstototal.length; i++){
    var totkey = (colstototal[i]-1).toString();
    totals[totkey] = 0; //init    
  }
  
  //collect all the rows from values that match the key and add them to the set
  for (var i = 0; i < values.length; i++){
    if (key === values[i][SPREADSHEET_DATASHEET_USERCOL-1]) {
      set.push(values[i]); 
    }
  }
  // TOTALS - if there's totals to process, 
  if (SPREADSHEET_DATASHEET_COLSTOTOTAL.length > 0) {
    // add the totals and write them
    var colstototal = SPREADSHEET_DATASHEET_COLSTOTOTAL;
    for (var colkey in totals){ //thru cols to total
      for (var i = 0; i < set.length; i++){ // each row
        for (var j = 0; j < set[i].length; j++){ //each column
          if (parseInt(colkey)===j) { //col to total
            //do totalling
            var somethingtototal = set[i][j];
            // sum them if not NaN
            //totals[colkey] = totals[colkey] + set[i][j];
            totals[colkey] = totals[colkey] + parseFloat(set[i][j]||0);
          }
        }
      }
    }
    //add a new row for totals
    set.push(values[0]);
    for (var j = 0; j < values[0].length; j++){
      set[set.length-1][j] = "";
    }
    //add the word TOTAL to a new row in the Key column 
    set[set.length-1][SPREADSHEET_DATASHEET_USERCOL-1] = "Total";

    //add the totalled column values
    for (var colkey in totals){ //thru cols to total
      set[set.length-1][parseInt(colkey)] = totals[colkey].toString();
    }

  } //end totals

  //finished
  return set;  
}

// Ensures that the data can be written as a string to the flextable using
// .setText. Also massages dates and make emails wrap.
function prepareTableText_(value) {
  var txt = value.toString();
  // allow wrap on email addresses
  txt = txt.replace("@example.com", "@ example.com");
  // ensmallen date strings (assumes ss script properties is set to Sydney)
  if (txt.indexOf("GMT+10") > 0) {
    txt = Utilities.formatDate(new Date(txt), "GMT+10", "dd/MM/yyyy");
  } else if (txt.indexOf("GMT+11") > 0){
    txt = Utilities.formatDate(new Date(txt), "GMT+11", "dd/MM/yyyy");
  }
  return txt;
}

// Get subset of keys representing allowed & available data to process
function getKeysToProcess_(sheet) {
    // Get list of emails whose data the current user should be able to see
  var accessibleUsers = getAccessibleUsersForCurrentUser_(sheet.getParent());
  for (var i = 0; i < accessibleUsers.length; i++) {
    Logger.log("accessibleUsers is >" + accessibleUsers[i] + "<");
  }

  // get list of available data to process
  var keys = getUniqueKeys_(sheet, SPREADSHEET_DATASHEET_USERCOL, 1);
  for (var i = 0; i < keys.length; i++) {
    Logger.log("Key is " + keys[i]);
  }
  keys.sort();
  for (var i = 0; i < keys.length; i++) {
    Logger.log("Sorted key is " + keys[i]);
  }

  // get subset of allowed & available data to process
  var keystoprocess = [];
  if (accessibleUsers[0] === "*") { //all available
    keystoprocess = keys;
  } else { //just accessible from available
    for (var i = 0; i < keys.length; i++) {  
      for (var j = 0; j < accessibleUsers.length; j++) {
        if (keys[i]===accessibleUsers[j]) {
          keystoprocess.push(keys[i]);
        }
      }
    }  
  }
  for (var i = 0; i < keystoprocess.length; i++) {
    Logger.log("Keystoprocess is " + keystoprocess[i]);
  }
  return keystoprocess;
}