staff directory lookup. Used in http://mashe.hawksey.info/2011/07/gas-staff-directory/

gistfile1.js

/*   
   Copyright 2011 Martin Hawksey and Dito LLC 

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/
var ss               = SpreadsheetApp.openById("twCOMuicP1sqdaL_kgGTtXw"); // you can get this by running the getID() function and View > Logs 
var enableEdit       = true; // set this to false if you don't want to give users the ability to add/edit their details
var textOverview = "Staff directory developed in Google Apps Script by mhawksey which allows Google Apps " +
                 "users search a spreadsheet of staff details. This script uses an " +
                 "auto-complete function developed by www.ditoweb.com.";
var editText         = "Please edit your details if required. Any problems email somone@somewhere.notcom";
var sheetOwner       = ss.getSheetByName("Staff"); // Sheet with staff details
var domainOnlyFilter = false;  // change to 'true' to only gather contacts within the domain (@company.com)
// Index for column numbers in staff sheet
var FNAME_COL        = 0; // used in auto-complete
var SNAME_COL        = 1; // used in auto-complete
var EMAIL_COL        = 2; // used in auto-complete
var JA_EMAIL_COL     = 3; // used in auto-complete, unique index which matches Google Apps login
var ROLE_COL         = 4; // used to filter search
var SERVICE_COL      = 5; // used to filter search
var STATUS_COL       = 6; // displayed in Edit tab only
var TEL_COL          = 7; // displayed in results
var MOB_COL          = 8; // displayed in results
var SPECIAL_COL      = 9; // used to filter search
var SKYPE_COL        = 10; // displayed in results
var TWITTER_COL      = 11; // displayed in results

function getID(){
  var msg = "This spreadsheet ID is: "+SpreadsheetApp.getActiveSpreadsheet().getId();
  Logger.log(msg);
}
function doGet(e) {
  // main UI used to create tabbed interface
  // Inspired by https://sites.google.com/site/scriptsexamples/tabpanel
  var app = UiApp.createApplication().setHeight(280).setWidth(620).setTitle('Staff Directory');; 
  var tabPanel = app.createTabPanel().setId('tabPanel');
  var searchTab = app.createFlowPanel();
  var resultTab = app.createFlowPanel();
  
  var searchPanel = searchPanelBuild(app).setHeight('236px');
  
  // some extra bits for the results tab
  var resultText = app.createLabel().setWidth('616px').setId('resultText').setStyleAttribute('textAlign', 'right').setText("No results yet ;)");
  resultTab.add(resultText);
  var resultScroll = app.createScrollPanel().setId('resultScroll').setHeight('220px').setId('resultScroll');
  var resultTable = app.createFlexTable().setId('resultTable');
  resultScroll.add(resultTable);
  
  // search tab
  searchTab.add(searchPanel);
  tabPanel.add(searchTab, "Search");
  
  // result tab
  resultTab.add(resultScroll);
  tabPanel.add(resultTab, "Results");
  
  //edit tab
  if (enableEdit){
    var editTab = app.createFlowPanel().setWidth('616px');
    var editPanel = editPanelBuild(app);
    editTab.add(editPanel);
    tabPanel.add(editTab, "Edit");
  }
  tabPanel.selectTab(0);
  
  app.add(tabPanel);
  return app;
}

function searchPanelBuild(app){
/*
* This is a modification of the email auto-complete script was written by Dito LLC (www.ditoweb.com)
* http://googleappsdeveloper.blogspot.com/2011/05/autocomplete-email-addresses-in-apps.html
*/
  var flowPanel = app.createFlowPanel();
  var mainPanel = app.createVerticalPanel().setId('mainPanel').setWidth('555px');
  flowPanel.add(mainPanel);
 
  //create a grid 2 cells tall and 1 cell wide
  var gridE = app.createGrid(2, 1).setWidth('600px');
  mainPanel.add(gridE);
  gridE.setWidget(0, 0, app.createLabel(textOverview).setWidth('610px')); 
  
  //leverage a grid to hold left and right vertical panels
  var containGrid = app.createGrid(1, 2).setCellPadding(0); 
  gridE.setWidget(1, 0, containGrid);
  var leftGrid  = app.createVerticalPanel().setWidth('330px').setStyleAttribute("paddingTop","10px"); 
  containGrid.setWidget(0, 0, leftGrid);
  var rightGrid = app.createVerticalPanel().setWidth('235px');
  rightGrid.setStyleAttribute("paddingLeft", "10px");
  containGrid.setWidget(0, 1, rightGrid); 
  leftGrid.add(app.createLabel('Enter email:').setId('labelR'));

  //create a text box for the auto-complete during email lookup in the left grid
  var textBox = app.createTextBox().setName('textBox').setWidth('330px').setId('textBox');
  var tBoxHandler = app.createServerKeyHandler('search_');
  tBoxHandler.addCallbackElement(textBox);
  textBox.addKeyUpHandler(tBoxHandler);
  leftGrid.add(textBox);
  
  // ".setVisibleItemCount" is not documented in Apps Script yet, but is available from Google
  // Web Toolkit (GWT). By entering a value > "1" creates a list of choices.
  var listBox = app.createListBox().setName('list').setWidth("330px").setId('list').setVisibleItemCount(5)
                   .setStyleAttribute("border", "1px solid white")
                   .setStyleAttribute("background", "white");
  var listClickHandler = app.createServerClickHandler('storeMembers_');  
  listClickHandler.addCallbackElement(listBox);
  listBox.addClickHandler(listClickHandler); 
  var listKeyHandler = app.createServerKeyHandler('storeMembers_');
  listBox.addKeyPressHandler(listKeyHandler);
  leftGrid.add(listBox);

  // Right grid where <s>we compile all the selected emails</s> add some drop down lists to filter results
  rightGrid.add(app.createLabel('Service').setId('labelService'));
  var serviceList = app.createListBox().setName('serviceList').setWidth("240px").setId('serviceList');
  rightGrid.add(serviceList);
  // fill in the service box
  addUniqueList(serviceList, SERVICE_COL);
  
  rightGrid.add(app.createLabel('Role').setId('labelRole'));
  var roleList = app.createListBox().setName('roleList').setWidth("240px").setId('roleList');
  rightGrid.add(roleList);
  // fill in the role box
  addUniqueList(roleList, ROLE_COL);
  
  rightGrid.add(app.createLabel('Specialisms').setId('labelSpecial'));
  var specialList = app.createListBox().setName('specialList').setWidth("240px").setId('specialList');
  rightGrid.add(specialList);
  // fill in the specialisms box
  addUniqueList(specialList, SPECIAL_COL);
  
  // add a button
  var button = app.createButton('Search/More info').setStyleAttribute("width", "600px");
      var ch = app.createServerClickHandler('resultsProcess_').addCallbackElement(textBox).addCallbackElement(rightGrid);
      button.addClickHandler(ch);
  flowPanel.add(button);
  return flowPanel;
}
function search_(e){
/*
* This is a modification of the email auto-complete script was written by Dito LLC (www.ditoweb.com)
* http://googleappsdeveloper.blogspot.com/2011/05/autocomplete-email-addresses-in-apps.html
*/
  // this search is for the auto-complete list on the search tab. It queries first name, surname and 
  // email and lists matches in alphabetical order based on surname. Our org has primary/secondary 
  // emails, secondary being Google Apps id 
  var app = UiApp.getActiveApplication();
  app.getElementById('list').clear(); 
  var searchKey = new RegExp(e.parameter.textBox,"gi");
  if (searchKey == "") app.getElementById('textBox').setValue('');
  var range        = sheetOwner.getRange(2, 1, sheetOwner.getLastRow(), 4).getValues();
  var listBoxCount = 0;
  var firstOne     = true;
  for (var i in range){
     if (range[i][FNAME_COL].search(searchKey) != -1 || range[i][SNAME_COL].search(searchKey) != -1 || range[i][EMAIL_COL].search(searchKey) != -1){
       if (range[i][0].toString()){
          app.getElementById('list').addItem(range[i][FNAME_COL].toString()+" "+range[i][SNAME_COL].toString()+' .. '+range[i][EMAIL_COL].toString(), range[i][JA_EMAIL_COL].toString()); 
          var listBoxCount = listBoxCount + 1;
       } else {
          app.getElementById('list').addItem(range[i][SNAME_COL].toString()); 
          var listBoxCount = listBoxCount + 1;
       }
       if (firstOne) var firstItem = range[i][SNAME_COL].toString();
       var firstOne = false;
     }  
  }
  // set the top listbox item as the default
  if (listBoxCount > 0) app.getElementById('list').setItemSelected(0, true);

  // if enter key is pressed in text box, then assume they want to add that email that's not in the list
  if (e.parameter.keyCode==13 && listBoxCount < 1 && searchKey !== "") {
    // validate email
    var forceEmail = e.parameter.textBox;
    var atpos      = forceEmail.indexOf('@');
    var dotpos     = forceEmail.lastIndexOf('.');
    if (atpos < 1 || dotpos < atpos+2 || dotpos+2 >= forceEmail.length){
       return app;  // invlaid email .. no message .. just ignore
    } 
    app.getElementById('list').clear(); 
    app.getElementById('textBox').setValue('');
  }
  // enter key pressed and listbox results exist. get the first list box item
  if (e.parameter.keyCode==13 && listBoxCount > 0) {
    app.getElementById('list').clear(); 
    app.getElementById('textBox').setValue('');
  }
  if (e.parameter.textBox.length < 1) app.getElementById('list').clear();
  return app;
}

function storeMembers_(e){
/*
* This is a modification of the email auto-complete script was written by Dito LLC (www.ditoweb.com)
* http://googleappsdeveloper.blogspot.com/2011/05/autocomplete-email-addresses-in-apps.html
*/
  var app = UiApp.getActiveApplication();
  app.getElementById('textBox').setValue(e.parameter.list);
  app.getElementById('list').clear(); 
  return app;  
}

function resultsProcess_(e){
  // function to grab search terms and build results page
  var app = UiApp.getActiveApplication();
  var needles = [];
  // grab search parameters
  var email = e.parameter.textBox; 
  var serv = e.parameter.serviceList;
  var role = e.parameter.roleList;
  var spec = e.parameter.specialList;
  // only process if something entered
  if (email!="" || role!="" || serv!="" || spec!=""){
    // grab all the data from the spreadsheet
    var data = sheetOwner.getRange(2, 1, sheetOwner.getLastRow(), sheetOwner.getLastColumn()).getValues();
    // build object array for search query
    if (email!=""){
      needles.push([JA_EMAIL_COL,email]);
    }
    if (role != ""){
      needles.push([ROLE_COL,role]);
    }
    if (serv != ""){
      needles.push([SERVICE_COL,serv]);
    }
    if (spec != ""){
      needles.push([SPECIAL_COL,spec]);
    }
    // get the results
    var result = find(data,needles);
    clearResults(app, ScriptProperties.getProperty('maxRows'));
    // if results then build results table
    if (result.length > 0){
      // clear any existing results 
      
      var j=0;
      // for each result build record
      for (var i=result.length; i>0; i--){
        writeResults(app, result[i-1], j);
        j++;
      }
     // note maximum results for clearing next time around
     
    }
    ScriptProperties.setProperty('maxRows',result.length);
    // render results tab then set focus
    var tabPanel = app.getElementById('tabPanel');
    var resultText = app.getElementById('resultText');
    resultText.setText((result.length)+" results found");
    tabPanel.selectTab(1);
  }
  return app;
}

function editPanelBuild(app){
  // function to build edit tab
  // from signed in user email grab row of their data
  var jaemail = Session.getUser().getEmail();
  var rowNum = parseInt(getRowFromCol(jaemail,JA_EMAIL_COL))+1;
  var newUser = false;
  if (!rowNum){
    rowNum = sheetOwner.getLastRow()+1;
    newUser = true;
  }
  var row = sheetOwner.getRange(rowNum, 1, 1, sheetOwner.getLastColumn()).getValues();
  var r = row[0];
 
  // build edit interface populating with users details
   
  var itemElement = app.createFlowPanel();
  var nameHolder =  app.createFlexTable().setWidth('590px');
 
  nameHolder.setText(0, 0, "First Name:");
  nameHolder.setWidget(0, 1, app.createTextBox().setName("col"+FNAME_COL).setId("col"+FNAME_COL).setValue(r[FNAME_COL]));
  nameHolder.setText(0, 2, "Last Name:");
  nameHolder.setWidget(0, 3, app.createTextBox().setName("col"+SNAME_COL).setId("col"+SNAME_COL).setValue(r[SNAME_COL]));
  nameHolder.setText(0, 4, "Status:");
  var statusList = app.createListBox().setName('col'+STATUS_COL).setWidth("240px").setId('col'+STATUS_COL).setWidth("70px");
  addUniqueList(statusList, STATUS_COL, r[STATUS_COL]);
  nameHolder.setWidget(0, 5, statusList);
  
  var posHolder =  app.createFlexTable().setWidth('590px');
  posHolder.setText(0, 0, "Role:");
  var roleList = app.createListBox().setName('col'+ROLE_COL).setWidth("240px").setId('col'+ROLE_COL);
  addUniqueList(roleList, ROLE_COL, r[ROLE_COL]);
  posHolder.setWidget(0, 1, roleList);
  posHolder.setText(0, 2, "Service:");
  var serviceList = app.createListBox().setName('col'+SERVICE_COL).setWidth("240px").setId('col'+SERVICE_COL);
  addUniqueList(serviceList, SERVICE_COL, r[SERVICE_COL]);
  posHolder.setWidget(0, 3, serviceList);
  
  var contactHolder = app.createFlexTable().setWidth('590px');
  contactHolder.setText(0 , 0, "Email:");
  contactHolder.setWidget(0 , 1, app.createTextBox().setName("col"+EMAIL_COL).setId("col"+EMAIL_COL).setValue(r[EMAIL_COL]).setWidth('220px'));
  contactHolder.setText(0 , 2, "JA Email:");
  contactHolder.setWidget(0 , 3, app.createTextBox().setName("col"+JA_EMAIL_COL).setId("col"+JA_EMAIL_COL).setValue(r[JA_EMAIL_COL]).setWidth('220px').setEnabled(newUser));
  contactHolder.setText(1 , 0, "Tel:");
  contactHolder.setWidget(1 , 1, app.createTextBox().setName("col"+TEL_COL).setId("col"+TEL_COL).setValue(r[TEL_COL]));
  contactHolder.setText(2 , 0, "Mob:");
  contactHolder.setWidget(2 , 1, app.createTextBox().setName("col"+MOB_COL).setId("col"+MOB_COL).setValue(r[MOB_COL]));
  contactHolder.setText(1 , 2, "Skype:");
  contactHolder.setWidget(1 , 3, app.createTextBox().setName("col"+SKYPE_COL).setId("col"+SKYPE_COL).setValue(r[SKYPE_COL]));
  contactHolder.setText(2 , 2, "Twitter:");
  contactHolder.setWidget(2 , 3, app.createTextBox().setName("col"+TWITTER_COL).setId("col"+TWITTER_COL).setValue(r[TWITTER_COL]));
  
   var button = app.createButton('Save').setWidth('80px').setId('saveBut');
      var ch = app.createServerClickHandler('editProcess_').addCallbackElement(itemElement);
      button.addClickHandler(ch);
  
  var specialHolder = app.createFlexTable();
  specialHolder.setText(0 , 0, "Specialisms:").setStyleAttribute("verticalAlign","top");
  specialHolder.setWidget(0, 1, app.createTextBox().setName("col"+SPECIAL_COL).setId("col"+SPECIAL_COL).setValue(r[SPECIAL_COL]).setWidth("430px"));
  specialHolder.setWidget(0, 2, button);
 
  itemElement.add(app.createLabel(editText).setWidth('610px').setStyleAttribute("padding", "5px").setId('inst'));
  itemElement.add(nameHolder);
  itemElement.add(posHolder);
  itemElement.add(contactHolder);
  itemElement.add(specialHolder);
  
  return itemElement;
}
function editProcess_(e){
  // function to store any edits made by the user
  // from signed in user email grab row number of their data
  var app = UiApp.getActiveApplication();
  var jaemail = Session.getUser().getEmail();
  var rowNum = parseInt(getRowFromCol(jaemail,JA_EMAIL_COL))+1;
   if (!rowNum){
    rowNum = sheetOwner.getLastRow()+1;
  }
  
  // building the interation to cycle through the columns building array to setValues with
  var maxColNum = TWITTER_COL;
  var data = [];
  var row = [];
  for (var i=0; i<=maxColNum; i++){
    row.push(e.parameter["col"+i].replace(/^\s*/, "").replace(/\s*$/, ""));
  }
  data.push(row);
  
  // Overwrite existing data with values
  sheetOwner.getRange(rowNum, 1, 1, maxColNum+1).setValues(data);
  var but = app.getElementById('saveBut');
  but.setEnabled(false);
  but.setText("Saved");
return app;
}

function clearResults(app, rows){
  // function to clear results from flexTable
  for (var i = 0; i < rows; i++){
    app.getElementById('resultTable').setWidget(i, 0, app.createLabel());
  }
  ScriptProperties.setProperty('maxRows',0);
}

function writeResults(app, r, i){
  // function to build each result item
  var itemElement = app.createFlowPanel().setStyleAttribute("borderBottom","2px solid #ccc").setStyleAttribute("paddingBottom","5px");
  var nameHolder = app.createLabel().setStyleAttribute("fontSize","17px");
  var serviceHolder = app.createLabel();
  var contactHolder = app.createFlexTable().setWidth('590px');
  nameHolder.setText(r[FNAME_COL]+" "+r[SNAME_COL]);
  serviceHolder.setText(r[ROLE_COL]+", "+r[SERVICE_COL]);
  contactHolder.setText(0 , 0, "Email: "+r[EMAIL_COL]);
  contactHolder.setText(0 , 1, "JA Email: "+r[JA_EMAIL_COL]);
  contactHolder.setText(1 , 0, "Tel: "+r[TEL_COL]);
  contactHolder.setText(2 , 0, "Mob: "+r[MOB_COL]);
  contactHolder.setText(1 , 1, "Skype: "+r[SKYPE_COL]);
  contactHolder.setText(2 , 1, "Twitter: "+r[TWITTER_COL]);
  itemElement.add(nameHolder);
  itemElement.add(serviceHolder);
  itemElement.add(contactHolder);
  itemElement.add(app.createLabel("Specialisms: "+r[SPECIAL_COL]).setWidth("590px").setStyleAttribute("fontSize","small").setStyleAttribute("paddingLeft","3px"));
  app.getElementById('resultTable').setWidget(i, 0, itemElement);
}

function addUniqueList(listName, colNum, optSelectedValue){
  // function takes column values and removes duplicates to make a listBox selection
  colNum ++;
  var rangeM = sheetOwner.getRange(2, colNum, sheetOwner.getLastRow(), 1).getValues();
  // build an array of everything 
  var uniqService = [];
  for (var i in rangeM){
    var tempArr = rangeM[i][0].split(","); // used to extract values from comma seperated list in a cell
    for (var j in tempArr){
      uniqService.push(tempArr[j].replace(/^\s*/, "").replace(/\s*$/, ""));
    }
  }
  uniqService = unique(uniqService); // filter array so it only contains unique values
  uniqService.sort(); // sort array
  var offset = 0;
  if (uniqService[0]!=""){ // add a blank item at the beginning of the listBox
    listName.addItem('');
    offset = 1;
  }
  // for each array item add it to the listBox and mark selected item
  for (var i=0; i<uniqService.length; i++){
    listName.addItem(uniqService[i]);
    if (uniqService[i]==optSelectedValue){
      listName.setSelectedIndex(i+offset);
    }
  } 
  return listName;
}
function find(haystack, needles) {
  // Inspired by http://stackoverflow.com/questions/5066656/compare-and-filter-multidimensional-arrays-against-each-other-in-java-script/5066730#5066730
  // given an object array of spreadsheet values return a results array that match the search criteria
  // if the cell values are comma seperated then convert to array and individually interate across these as well 
    var results = [];
    for(var i = haystack.length; i--;) {
        var matches = true;
        var item = haystack[i];
        for(var j = needles.length; j--; ) {
          if (!findInFind(j, needles,item)){
            matches = false;
            break; 
          }
        }
        if(matches) {
            results.push(item);
        }
    }
    return results;
}
function findInFind(j,needles,item){
  // Inspired by http://stackoverflow.com/questions/982595/how-to-break-out-of-2-loops-without-a-flag-variable-in-c/982615#982615
  // part of the find() to handle comma seperated cell values
  var needle = needles[j];
  var split = item[needle[0]].split(",");
  for (var k in split){
    if(needle[1] == split[k].replace(/^\s*/, "").replace(/\s*$/, "")) {
      return true;
    }
  }
  return false;
}
function unique(arrayName){
  // from http://www.roseindia.net/java/javascript-array/javascript-array-unique.shtml
  var newArray=new Array();
  label:for(var i=0; i<arrayName.length;i++ ){  
    for(var j=0; j<newArray.length;j++ ){
      if(newArray[j]==arrayName[i]) 
        continue label;
    }
  newArray[newArray.length] = arrayName[i];
  }
  return newArray;
}
function getRowFromCol(needle,column){
  // function to get the row number based on a column value
  column ++;
  var data = sheetOwner.getRange(1, column, sheetOwner.getLastRow(), 1).getValues();
  for (var i in data){
    if (data[i]==needle){
     return (i); 
    }
  }
  return false;
}