Selecting a Random Winner

Google Sheets: Select a Random Winner

by Daniel J. VandeBunte

For my first post for Teachers Can Code I’d like to go through a fairly short google apps script I wrote called selectWinner. I wrote the script during the summer of 2015 to select a random winner from a group of teachers attending a professional development session on Google Classroom. Coincidentally, the prize was Alice Keeler’s 50 Things You Can Do With Google Classroom.

I want to show the actual code first and then use it to illustrate how the program was designed, how it does what it does, and why some of the code looks the way it looks.

select-winner-entire-code

selectWinner code

Before You Code

The most important step in designing and writing a computer program is asking and answering the question, “what do I want the program to do?” In this case, I wanted the program to access the names in the spreadsheet, randomly select one of them, and then display that name in a given cell. Or, more abstractly, I wanted the program to access data in a spreadsheet, randomly select one of the data based on some criteria, and write the selected data as the contents of another cell.

Your First Lines of Code

Organize your program into what will ultimately be little snippets of code by using comments. With an outline written, writing the program was simply a matter of writing the snippet of code to accomplish the task described in the comment.

Access the Data

accessing the spreadsheet data

accessing the spreadsheet data

I try to name each function in a way that describes what it does. In this case, my function selects a random winner, so I named the function selectWinner.

function selectWinner() {

The next four lines of code are standard fare in Google Apps Script for collecting the data from the spreadsheet.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance");
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();

Notice here that the spreadsheet being accessed is named “Attendance.” When using the script, one of two things will have to be true on order for that line of code to execute; 1) the spreadsheet with the names in it is named “Attendance,” or 2) that name is changed in the source code to reflect the name of the spreadsheet containing the names. Thanks to Corey Coble for suggesting this edit in his comment below.

Program Math

Since my attendance spreadsheet had a header row, the number of teachers in attendance was one less than the number of rows of data. To make sure that the header row was not selected as the winner, I needed to subtract 1 from the number of rows before selecting the winner.

determine the number of attendees

determine the number of attendees

var attendees = numRows - 1;

By far the most difficult part of the program was choosing a random number and then translating that number into the number of a row containing the name of someone in attendance.

determine a random row number

determine a random row number

The header row should not be selected as the winner, so subtract 1 from the number of rows; (numRows – 1).

The Math.random() JavaScript function generates a random decimal d such that 0 <= d < 1. When d is multiplied by (numRows – 1), the product is a new decimal D such that 0 <= D < (numRows – 1), the number of rows containing potential winners.

The header row is still in play, since 0 <= D < the number of rows containing potential winners, so D must be adjusted to skip over row 0 and start no lower than row 1, the result of Math.random()*(numRows – 1) must be increased by 1 to guarantee that 1 <= D < the row number corresponding to the last name in the sheet.

In the spreadsheet, the top row is row 1, not row 0. In computer programming languages, however, the first element in an ordered array of elements has index 0. For an explanation of why this is, you can read all about it here. But finish reading first.

Since row numbers in the sheet do not have decimals, the decimal part of D must be dropped using Math.floor(), which rounds the input down to the nearest integer less than or equal to the input.

var randomNum = Math.floor(Math.random()*(numRows - 1)+1);

Displaying the Winner’s Name

At this point, the random winner has been determined by the program. The only thing that remains is to let the rest of the world know who it is.

The program considers the data in the spreadsheet to be a 2-dimensional array of data, with each element being identified by both a row and a column location. Because of this, we need to know both the row and column in which our winner’s name is located. The row has already been determined by the program. But the column depends on which column contains the names of the people in attendance. In my case, it’s the first, or 0th column in the spreadsheet. The name I want to display then, is the data located in the values array at [randomNum][0]. If your spreadsheet is set up differently, this may need to be adjusted.

display the winner's name

display the winner’s name

sheet.getRange('C2').setValue(values[randomNum][0]);

Run the Program

Lastly, you will need a way to run the program. You can do this by opening the selectWinner script and running it from the script editor. That is possible. But Google Apps Script also allows for custom menus.

insert a custom menu

insert a custom menu

By inserting a custom menu you can select and run scripts right from the spreadsheet without having to open the script editor.

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Select Winner", functionName: "selectWinner"}];
ss.addMenu("Scripts", menuEntries);
}


   2 Comments


  1. Corey Coble
      March 1, 2016

    When I tried to do this, the “Scripts” option was not available. I was able, in Google Scripts, to “test as add-on”. It ran great in the Add-ons part of Sheets. Was there a step I missed?

    Also, I ran into an error when I made my own. I forgot to change the name of the sheet to what I named it in the script. SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Attendance”);
    I didn’t see that in the instructions or comments. Might be a good instruction to add to be sure to name your sheet the same as what you are calling in the “getSheetByName”.

    Thanks for a fun introduction to Scripts that I can use in my classroom.

    • Daniel VandeBunte
        March 6, 2016

      Thanks for your comment and suggestion. I will add a bit to account for the spreadsheet name.

      When I wrote the script (August, 2015) you would write and access scripts using the Script Editor option under the “Tools” menu. Adding a custom menu using a script was fairly common among apps scripters. Since that time, I seem to recall Google disabling that ability in new spreadsheets, pushing scripts to the add-ons menu.

      Since the script is owned by me, it may also be the case that users cannot run the script from the original source unless they do so as an add-on. You may be able to get around this by accessing the script itself and copying and pasting the source code into a new script file.

Leave a Reply

Your email address will not be published. Required fields are marked *