Tutorial: Creating a Tournament Bracket

Fuzzy Khosrowshahi, Google Apps Script Team Ikai Lan, Google Apps Script Team Originally published July 2009, updated December 2012

Goal

This tutorial shows you how to use the Spreadsheets service to create Tournament Brackets similar to College Basketball's March Madness. You can use this tutorial to easily create your own brackets.

Time to Complete

Approximately 15 minutes

Prerequisites

Before you begin this tutorial, you must:

  • Feel comfortable using the Script Editor and have experience using the most basic Spreadsheet functions: See Your First Spreadsheet Script Tutorial
  • Have some basic JavaScript skills (like using Arrays and Objects). If you don't feel really comfortable with JavaScript, this tutorial will give detailed explanations.

What you will learn in this lesson

We will be learning about the following methods:

We will also learn how to do the following:

  • Creating a custom menu item and assigning a function to run

Section 1: Setting up a Spreadsheet and the example Script

  1. First thing we need to do is to open a new spreadsheet by going to drive.google.com
  2. Save and name the spreadsheet. I will name mine Brackets Test.
  3. Add a new sheet to the spreadsheet so you have 2 sheets in the spreadsheet. Name the first sheet "Players" and the second sheet "Bracket".

  4. In the "Players" sheet (ie first sheet),

    • Enter the text "Players/Teams:" in cell A1 and make the cell Bold.
    • Select cell A2 and go to "Data-->Named and protected ranges" menu and define a range called "FirstPlayer". After completing this step, when you go to "Data-->Named and protected ranges" again, you should see "FirstPlayer" in the right hand pane. Selecting it should take you to cell A2.

    Note that if this step is not completed properly, your script will not run.

    • Enter the following names in cells A2 thru A9: "Goofy", "Daffy", "Mickey", "Minnie", "Donald", "Daisy", "Pluto", "Fudgy"

When you are done with this step, the spreadsheet should look like this (note the names of both sheets at the bottom):

Section 2: Paste in the code

  1. Now open the script editor. If you don't know how, see Your First Spreadsheet Script Tutorial

  2. Paste in the code below into the editor and save the script as "BracketTest" or whatever other name you want to call the script:

    sheets/bracketmaker/bracketmaker.gs
    // This script works with the Brackets Test spreadsheet to create a tournament bracket
// given a list of players or teams.

var RANGE_PLAYER1 = 'FirstPlayer';
var SHEET_PLAYERS = 'Players';
var SHEET_BRACKET = 'Bracket';
var CONNECTOR_WIDTH = 15;

/**
 * This method adds a custom menu item to run the script
 */
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu('Bracket Maker',
             [{name: 'Create Bracket', functionName: 'createBracket'}]);
}

/**
 * This method creates the brackets based on the data provided on the players
 */
function createBracket() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rangePlayers = ss.getRangeByName(RANGE_PLAYER1);
  var sheetControl = ss.getSheetByName(SHEET_PLAYERS);
  var sheetResults = ss.getSheetByName(SHEET_BRACKET);

  // Get the players from column A.  We assume the entire column is filled here.
  rangePlayers = rangePlayers.offset(0, 0, sheetControl.getMaxRows() -
      rangePlayers.getRowIndex() + 1, 1);
  var players = rangePlayers.getValues();

  // Now figure out how many players there are(ie don't count the empty cells)
  var numPlayers = 0;
  for (var i = 0; i < players.length; i++) {
    if (!players[i][0] || players[i][0].length == 0) {
      break;
    }
    numPlayers++;
  }
  players = players.slice(0, numPlayers);

  // Provide some error checking in case there are too many or too few players/teams.
  if (numPlayers > 64) {
    Browser.msgBox('Sorry, this script can only create brackets for 64 or fewer players.');
    return; // Early exit
  }

  if (numPlayers < 3) {
    Browser.msgBox('Sorry, you must have at least 3 players.');
    return; // Early exit
  }

  // First clear the results sheet and all formatting
  sheetResults.clear();

  var upperPower = Math.ceil(Math.log(numPlayers) / Math.log(2));

  // Find out what is the number that is a power of 2 and lower than numPlayers.
  var countNodesUpperBound = Math.pow(2, upperPower);

  // Find out what is the number that is a power of 2 and higher than numPlayers.
  var countNodesLowerBound = countNodesUpperBound / 2;

  // This is the number of nodes that will not show in the 1st level.
  var countNodesHidden = numPlayers - countNodesLowerBound;

  // Enter the players for the 1st round
  var currentPlayer = 0;
  for (var i = 0; i < countNodesLowerBound; i++) {
    if (i < countNodesHidden) {
      // Must be on the first level
      var rng = sheetResults.getRange(i * 4 + 1, 1);
      setBracketItem_(rng, players);
      setBracketItem_(rng.offset(2, 0, 1, 1), players);
      setConnector_(sheetResults, rng.offset(0, 1, 3, 1));
      setBracketItem_(rng.offset(1, 2, 1, 1));
    } else {
      // This player gets a bye
      setBracketItem_(sheetResults.getRange(i * 4 + 2, 3), players);
    }
  }

  // Now fill in the rest of the bracket
  upperPower--;
  for (var i = 0; i < upperPower; i++) {
    var pow1 = Math.pow(2, i + 1);
    var pow2 = Math.pow(2, i + 2);
    var pow3 = Math.pow(2, i + 3);
    for (var j = 0; j < Math.pow(2, upperPower - i - 1); j++) {
      setBracketItem_(sheetResults.getRange((j * pow3) + pow2, i * 2 + 5));
      setConnector_(sheetResults, sheetResults.getRange((j * pow3) + pow1, i * 2 + 4, pow2 + 1, 1));
    }
  }
}

/**
 * Sets the value of an item in the bracket and the color.
 * @param {Range} rng The Spreadsheet Range.
 * @param {string[]} players The list of players.
 */
function setBracketItem_(rng, players) {
  if (players) {
    var rand = Math.ceil(Math.random() * players.length);
    rng.setValue(players.splice(rand - 1, 1)[0][0]);
  }
  rng.setBackgroundColor('yellow');
}

/**
 * Sets the color and width for connector cells.
 * @param {Sheet} sheet The spreadsheet to setup.
 * @param {Range} rng The spreadsheet range.
 */
function setConnector_(sheet, rng) {
  sheet.setColumnWidth(rng.getColumnIndex(), CONNECTOR_WIDTH);
  rng.setBackgroundColor('green');
}

Section 3: Understanding the different sections of the code

  1. The code at the top of the editor simply defines some constants.
  2. The "onOpen" function runs automatically when the Spreadsheet is opened. This creates a custom dropdown menu next to "Help" that has a single item: "Create Bracket". To learn more about custom menus, see the tutorial named Defining Spreadsheet Menus.

  3. The first few lines of the "createBracket" function instantiate some variable names to point to each sheet and the range "FirstPlayer". Note the use of Spreadsheet.getRangeByName() to reference a named range.

    sheets/bracketmaker/bracketmaker.gs
    var rangePlayers = ss.getRangeByName(RANGE_PLAYER1);

  4. The code below points "rangePlayers" to all the cells in the first column starting from A2 down to A100. It then places all the values in those cells in an array called "players". Note the use of the method Sheet.getMaxRows() to get the maximum number of rows in the sheet and the use of Range.getRowIndex() to get the row index of cell A2. We then use the Range.offset() method to select a range from A2 thru the maximum row in the same column.

    sheets/bracketmaker/bracketmaker.gs
    // Get the players from column A.  We assume the entire column is filled here.
rangePlayers = rangePlayers.offset(0, 0, sheetControl.getMaxRows() -
    rangePlayers.getRowIndex() + 1, 1);
var players = rangePlayers.getValues();

  • Here in order to figure out how many players there are, we loop through the cells in "rangePlayers" until we hit and empty cell. We then use the Javascript slice method to create an array of all the actual players (i.e. excluding all the empty cells at the bottom).

    sheets/bracketmaker/bracketmaker.gs
    // Now figure out how many players there are(ie don't count the empty cells)
var numPlayers = 0;
for (var i = 0; i < players.length; i++) {
  if (!players[i][0] || players[i][0].length == 0) {
    break;
  }
  numPlayers++;
}
players = players.slice(0, numPlayers);

  • The next section of the code uses Browser.msgBox() to perform some error checking and warn the user if there are too few or too many players. It also utilizes the Sheet.clear() method to clear the bracket sheet.

    sheets/bracketmaker/bracketmaker.gs
    if (numPlayers < 3) {
  Browser.msgBox('Sorry, you must have at least 3 players.');
  return; // Early exit
}

// First clear the results sheet and all formatting
sheetResults.clear();

  • In the last section of the code we perform some fancy logic to figure out where to place each player and where to draw connectors to the players. The placement of the players depends on the total number of players. If the number of players is not equal to some power of the number 2, we get an irregular bracket. You will see later when you actually play around with the script and add more players. For now the important things to take away are the uses of Range.setValue() and Range.setBackgroundColor() below. The first one writes a value to a cell and the other colors the cell.

    sheets/bracketmaker/bracketmaker.gs
    /**
 * Sets the value of an item in the bracket and the color.
 * @param {Range} rng The Spreadsheet Range.
 * @param {string[]} players The list of players.
 */
function setBracketItem_(rng, players) {
  if (players) {
    var rand = Math.ceil(Math.random() * players.length);
    rng.setValue(players.splice(rand - 1, 1)[0][0]);
  }
  rng.setBackgroundColor('yellow');
}

Section 4: Running the script and viewing the results

Reload the spreadsheet in your browser. Because "onOpen" is defined, after the spreadsheet finishes loading, a new menu item should appear next to "Help":

Click on the new custom menu and "Create Bracket" to run your function. Select the "Bracket" sheet. It should look lik this:

Try adding some players or deleting some players on the first sheet and then running "Bracket Maker->Create Bracket" button again. The bracket should adjust based on the number of players.

Summary

Congratulations, you've completed this tutorial. You should now feel comfortable using some of the basic methods of the Spreadsheet service and also be able to add a custom menu to your spreadsheet for running your script.