Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment