*Fuzzy Khosrowshahi, Google Apps Script Team*

Ikai Lan, Google Apps Script Team

Originally published July 2009, updated December 2012

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:

- Spreadsheet.getRangeByName()
- Sheet.getMaxRows()
- Range.getRowIndex()
- Range.offset()
- Browser.msgBox()
- Sheet.clear()
- Range.setValue()
- Range.setBackgroundColor()

We will also learn how to do the following:

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

#### Index

This tutorial is divided into the following sections:

- Section 1: Create and set up the spreadsheet
- Section 2: Paste in the code
- Section 3: Understanding the different sections of the code
- Section 4: Running the script and viewing the results
- Summary

### Section 1: Setting up a Spreadsheet and the example Script

- First thing we need to do is to open a new spreadsheet by going to drive.google.com
- Save and name the spreadsheet. I will name mine Brackets Test.
- 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".
- 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

- Now open the script editor. If you don't know how, see Your First Spreadsheet Script Tutorial
- Paste in the code below into the editor and save the script as "BracketTest" or whatever other name you want to call the script:

// 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, currently 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. 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. function setConnector_(sheet, rng) { sheet.setColumnWidth(rng.getColumnIndex(), CONNECTOR_WIDTH); rng.setBackgroundColor('green'); }

### Section 3: Understanding the different sections of the code

- The code at the top of the editor simply defines some constants.
- 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.
- 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.
- 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.
- 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).
- 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.
- 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.

... var rangePlayers = ss.getRangeByName(RANGE_PLAYER1); ...

... // 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 (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); ...

... 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(); ...

... // Sets the value of an item in the bracket and the color. 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.