Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Spreadsheet filter - view "my data" filtered to UiApp table
// 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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.