An employee recognition platform built using Google Sheets.
1029 words (Approximately a 6 minute read)
With just over a fortnight to provide an employee recognition management platform to the Managing Direction during my first month in my new role, I chose to bypass the external development agency due to their insufficient retainer and build the solution myself. Limited to WPAdmin and my own intuition, I designed a method that allows for controlled user access, user voting, and automation.
Our company splits the country into five regions, each of which comprises of multiple areas, with each area containing a high number of branches.
Customers and co-workers need to be able to register a monthly vote for the staff member they feel has gone above and beyond. These votes need to be visible only to the line managers of each nominee, so a monthly prize winner can be picked for each branch.
The winning nominations should then be included on a quarterly prize spreadsheet, where those winners enter the final annual award.
My solution comprised of a simple native web form on our WordPress site which sends its data to a specified Google Sheets spreadsheet using Contact Form 7 (CF7) plug-ins. A hierarchy of spreadsheets then reference this master spreadsheet to pull through the relevant data for managers to vote. All cells are locked down to prevent accidental editing, aside from the final column which allows the managers to mark their chosen winner(s).
The main controller spreadsheet would act as the database, with the CF7 web form automatically sending all data directly into the appropriate months’ tab on the correct branch spreadsheet. This is all achieved through careful naming and folder hierarchy, strict spreadsheet sharing and cell-locking, and a couple of whizzy formulae.
Using Google’s subset of SQL, Google Visualization API Query Language we can easily grab data from other spreadsheets using a combination of formulae.
QUERY() tells us we are going to examine some data, whilst IMPORTRANGE() tells us where we need to get the data from. Combining these allows us to select specific data that matches the required conditions, e.g.
=QUERY(
IMPORTRANGE("spreadsheet-url","tab-name!cell-range"),
"select COLUMNS where (COLUMNX = 'condition')",1
)
We can use this to select all nominations from the master spreadsheet which meet the following criteria:
This all happens automatically in real-time as the nominations come in.
We can write JavaScript functions to act on the spreadsheet under the Extensions > Apps Script menu in Google Sheets. These functions can be run manually, or set up to run at specified intervals.
One such function we run needs to examine all the spreadsheets in the system, and look at the correct month for any nominations that have been marked as the WINNER.
A key part of this function, and one that is used as the base of many other operations, is the part which scans a folder full of spreadsheets. The winning nominations are added to an array which is then sorted and sent to the quarterly spreadsheet, which, in turn, is monitored by other spreadsheets for changes using the above QUERY(IMPORTRANGE()) formulae.
function retrieveLocalNominations(folder) {
folder = folder || DriveApp.getFolderById(areaFolder);
// Enter the next free row number on the 'local_winners' sheet
let nextFreeRow = 32;
let name = folder.getName();
let files = folder.getFiles();
// Array to hold the winning nomination IDs from all sheets
let rngB = [];
let fileloop = 0;
while ( files.hasNext() ) {
let file = files.next();
let activeSpreadSheet = SpreadsheetApp.open(file);
let activeName = activeSpreadSheet.getName();
let sheets = activeSpreadSheet.getSheets();
let month1Sheet = activeSpreadSheet.getSheetByName(monthName);
let rowCount;
// Arrays to hold the winning nomination IDs from the current sheet
let drng, rng, rngA, b = 0;
month1Sheet.activate();
activeName = activeSpreadSheet.getName();
drng = month1Sheet.getDataRange();
rowCount = month1Sheet.getLastRow();
if(rowCount > 1) {
rng = month1Sheet.getRange(1,1, drng.getLastRow(),10);
rngA = rng.getValues();
b = 0;
for(let i = 0; i < rngA.length; i++) {
if(rngA[i][9] != '' && rngA[i][9] != 'vote') {
rngB.push([rngA[i][0]]);
b++;
}
}
}
fileloop++;
// Log array
Logger.log("rngB after file loop " + fileloop + ": " + rngB);
}
// Remove empty array items, sort and log new array
let filtered = rngB.filter(function (el) { return el != ""; });
filtered.sort(function(a, b) { return a - b; });
Logger.log("filtered sorted: " + filtered);
// Pull winning IDs into master spreadsheet
activeSpreadSheet = SpreadsheetApp.openById("spreadhseet-id-from-url");
let q1Sheet = activeSpreadSheet.getSheetByName('local-winners');
// Make the output range the same size as the output array
let outrng = q1Sheet.getRange(nextFreeRow,14,filtered.length,1);
outrng.setValues(filtered);
}
The free plan of google software imposes processing limits—this means we need to break up the processing power by running each function through each folder separately. The function above is re-run for each areaFolder (the lowest folder, containing no sub folders, only files) in the company structure, and each monthName of the year.
We also need to run admin functions on the spreadsheets once they have been created, to update the date ranges each year, reflect any changes to the company structure, and of course, to create the spreadsheets themselves in the first instance (there are over 250 of them).
An example function to edit the formulae used across all the spreadsheets would look like this:
function replaceText(folder) {
folder = folder || DriveApp.getFolderById(areaFolder);
let files = folder.getFiles(),
subfolders = folder.getFolders();
function replaceQueryText() {
let file = files.next(),
activeSpreadSheet = SpreadsheetApp.open(file);
for(let i = 0, loopLength = monthSplits.length; i<loopLength; i++) {
let currentMonth = monthSplits[i],
activeSheet = activeSpreadSheet.getSheetByName(currentMonth[0]);
if(activeSheet != undefined) {
let formula = activeSheet.getRange("A1").getFormula(),
newFormula = formula.replace("old-string", "new-string");
activeSheet.getRange("A1").setValue(newFormula);
}
}
};
while ( files.hasNext() ) { replaceQueryText() };
while (subfolders.hasNext()) {
while ( files.hasNext() ) {
replaceQueryText()
}
};
}
Google Sheets—combined with Apps Scripts—is extremely powerful and can be used for software development proof of concept builds. There may an element of manual admin involved, but it is certainly flexible and fast enough to test out an idea and get a quick working solution.