And the Winner is…

As part of Merry Maveristmas — A Week of Holiday Festivities, (read more about Maveristmas here) employees gained Magical Maveris Medallions (MMMs) by submitting entries for competitions, voting on submissions from their coworkers, and winning competitions. As employees were awarded MMMs, their total was tracked within a Google Sheet. Some of these values were calculated and recorded using Google Forms and Google Apps Script, as discussed here, while others were recorded manually such as when an employee would participate in a Maveristmas Lunch meeting. At any point throughout the week, employees could check their current MMMs total and their chance of winning the Grand Prize using an integration the team developed within Slack.

At the conclusion of the competitions, an employee’s name was entered into a raffle based upon the number of MMMs they accrued; where one MMM equaled one entry. In total, over 2800 MMMs were awarded. Because we were using a Google Sheet to store the MMM totals, we decided to use Google’s Apps Script for calculation and selection of the prize winners. Nine Large Prizes and a Grand Prize were awarded, so we needed functionality to draw 10 distinct employee names.

The selection of winners followed this process:

Winner Selection Process Diagram

The Apps Script started by reading each employee’s email address and their MMM total from the tracking spreadsheet.

MMM tracking example

These values were read into the totalMedallionsRange, which we verified to ensure it was able to read from the spreadsheet. We then created the employeeTokenMasterList array used to hold all entries for each employee. Then we iterated through each row from the spreadsheet, grabbing the employees email address and their MMM count. At the conclusion of this loop, the employeeTokenMasterList array contained each employee’s email multiple times, based upon their MMM total.

if (!totalMedallionsRange) {
  Logger.log('No data found.');
} else {
  //Read from Master Tracker spreadsheet - insert employees name into array for each MMM they have
  var employeeTokenMasterList = [];
  for (var row = 1; row < totalMedallionsValue.length - 1; row++) {
    var employeeEmailAddress = totalMedallionsValue[row][0];
    var employeeTokenCount = totalMedallionsValue[row][1];
    for (var tokenCount = 0; tokenCount <employeeTokenCount; tokenCount++){
      if (!doNotInclude.includes(employeeEmailAddress))
        employeeTokenMasterList.push(employeeEmailAddress);
      }
    }
  }

In the code above, you see that, while email addresses were read from totalMedallionsValue and inserted into the employeeTokenMasterList, we did not add values that were found in the doNotInclude array. This array held the email addresses of leadership. While they participated in the festivities, they elected to remove themselves from winning prizes.

Having built out the employeeTokenMasterList, we decided to shuffle the array using the Math.random function.

//Shuffles the array
//Returns and radomized array
function shuffleArray(array) {
  var i, j, temp;
  for (i = array.length - 1; i > 0; i--) {
    j = Math.floor(Math.random() * (i + 1));
    temp = array[i];
    array[i] = array[j];
    array[j] = temp;
  }
  return array;
}

With the shuffled employeeTokenMasterList, we entered a loop that allowed us to select the 10 distinct winners. The chooseWinners function calculates a random number which is multiplied by the array’s length. The result is then rounded to the nearest integer. The winner is then selected from the array, shuffled above, at the calculated location and returned. The selected winner is added to the winnersArray to be excluded from all subsequent selections. In the code snippet below you can see where a filter method is called on the employeeTokenMasterList to exclude the email address of those contained within the winnersArray. The Apps Script then writes the winners email address to the winnersSpreadsheet in the second column. To ensure randomization, the employeeTokenMasterList was passed to the shuffleArray function after each winner selection.

  //Now that the array of employees is created, shuffle them to break up positioning
  shuffleArray(employeeTokenMasterList);
for(let i = 1; i < 11; i++) {
    var winner = chooseWinners(employeeTokenMasterList
      .filter(email => !winnersArray.includes(email)));
    winnersArray.push(winner);
    //Print winner on Winners tab of workbook
    winnersSpreadhsheet.getRange(i, 2).setValue(winner); 
    //After a winner is selected, call the method to reshuffle the array (adds to randomization)
    shuffleArray(employeeTokenMasterList);
  }
}
//Calculates a random number based upon the number of entries and chooses an item from the array based upon that number
//Returns the winner
function chooseWinners(array){
  var randomNumber = Math.round(Math.random() * array.length);
  var winner = array[randomNumber];
  return winner;
}

The script to select the winners was initiated via a button within the Winners spreadsheet. Upon completion, the script provided 10 distinct names. From there, a prize was awarded based upon the prize ranking each employee had previously submitted.

Apps Script output to spreadsheet example

In all, the team had roughly 2.5 working days from conception to the first day of Maveristmas to create the necessary Google Forms and script the necessary code. Based upon feedback anonymously provided by employees upon the completion of the week’s events, another Google Form submission but this time without capturing their email address, Maveristmas was a great success.

If you find yourself needing to run a raffle or randomizing selection, hopefully what you find here will bring you one step closer.