Historically, spreadsheets have been used as an invaluable tool for capturing, displaying, and manipulating data. However, with the utilization of Spread.Sheets you can extend the tool's capabilities beyond data organization and calculation.
Download the sample bracket
In this tutorial, we will go over how to create a bracket for the March Madness Basketball Tournament, including a sheet for individuals to make predictions as to which team will win each matchup. Using the power of conditional formatting and hidden sheets, we can give users the ability to select the team that wins each round and enable that team to propagate up to later rounds.
Setting up the JavaScript bracket with Spread.Sheets
When creating the application, I knew the easiest way to generate the bracket was to use the Spread.Sheets Designer, as it gives me the freedom to quickly design a bracket without having to write any code. For the purposes of this tutorial, I have already created a template that we can edit with conditional formatting.
In the download, these are the “March Madness Bracket Template.ssjson" and “MarchMadnessBracketTemplate.js" files.
Once the files are downloaded, create a new HTML page, and add the Spread.Sheets references as well as a reference to the template and the FileSaver library that we will use for exporting:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>March Madness Bracket</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2014-11-29/FileSaver.min.js"></script>
<link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.11.0.1.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.11.0.1.min.js"></script>
<script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/interop/gc.spread.excelio.11.0.1.min.js"></script>
<script type="text/javascript" src="./MarchMadnessBracketTemplate.js"></script>
</head>
</html>
We will also want to add a DIV element for the Spread.Sheets instance, as well as a button and textbox that will be used for exporting the Spread.Sheets instance to Excel:
<body>
<input type="button" class="btn btn-default" id="saveExcel" value="Export" onclick="ExportFile()" />
<input type="text" id="exportFileName" placeholder="Export file name" class="form-control" value="MarchMadnessBracket.xlsx" />
<div id="ss" style="width: 1450px; height: 850px; border: 1px solid gray"></div>
</body>
To start scripting, we will add a function that runs when the page loads, and we can use that function to initialize a few different variables that we are going to be using:
var excelIO, spread, activeSheet;
window.onload = function () {
excelIO = new GC.Spread.Excel.IO();
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
spread.options.allowContextMenu = false;
}
To load the template, we can create a separate function that loads the JS file for the template:
function LoadTemplate() {
spread.fromJSON(MarchMadnessBracketTemplate);
}
Add Hidden Sheets
To successfully create conditionally formatted lists to go into the bracket, we can create some hidden validation sheets, which will make things a lot easier:
function AddHiddenSheets() {
var validationSheet = new GC.Spread.Sheets.Worksheet();
validationSheet.name("Validation");
validationSheet.setColumnCount(64);
spread.addSheet(2, validationSheet);
var validationBracketSheet = new GC.Spread.Sheets.Worksheet();
validationBracketSheet.name("ValidationBracket");
validationBracketSheet.setColumnCount(64);
spread.addSheet(3, validationBracketSheet);
FillHiddenSheets();
spread.sheets[2].visible(false);
spread.sheets[3].visible(false);
}
The first hidden sheet (called “Validation") is going to be used for the tracker. This will identify the team matchups for each round where we can use the formula validation lists in the tracker sheet. Now users will have the ability to select the teams they predict will win.
The second sheet (called “ValidationBracket") will be used for the bracket sheet. This is going to be almost identical to the “Validation" sheet, with the exception that there will be no “[Pick a team]" option in the list:
function FillHiddenSheets() {
for (var c = 0; c < 63; c++) {
spread.getSheet(2).setText(0, c, "[Pick a team]");
}
for (var s = 2; s <= 3; s++) {
for (var c = 0; c <= 15; c++) {
spread.getSheet(s).getCell(1, c).formula("=Bracket!$C$" + ((c * 4) + 4));
spread.getSheet(s).getCell(2, c).formula("=Bracket!$C$" + ((c * 4) + 6));
}
for (var c = 16; c <= 31; c++) {
spread.getSheet(s).getCell(1, c).formula("=Bracket!$R$" + (((c - 16) * 4) + 4));
spread.getSheet(s).getCell(2, c).formula("=Bracket!$R$" + (((c - 16) * 4) + 6));
}
for (var c = 32; c <= 39; c++) {
var rowNum = (((c - 32) * 8) + 5);
spread.getSheet(s).getCell(1, c).formula("=IF(Bracket!$D$" + rowNum + " <>\"\", Bracket!$D$" + rowNum + ", \"— Undecided —\"");
spread.getSheet(s).getCell(2, c).formula("=IF(Bracket!$D$" + (rowNum + 4) + " <>\"\", Bracket!$D$" + (rowNum + 4) + ", \"— Undecided —\"");
}
for (var c = 40; c <= 47; c++) {
var rowNum = (((c - 40) * 8) + 5);
spread.getSheet(s).getCell(1, c).formula("=IF(Bracket!$Q$" + rowNum + " <>\"\", Bracket!$Q$" + rowNum + ", \"— Undecided —\"");
spread.getSheet(s).getCell(2, c).formula("=IF(Bracket!$Q$" + (rowNum + 4) + " <>\"\", Bracket!$Q$" + (rowNum + 4) + ", \"— Undecided —\"");
}
for (var c = 48; c <= 51; c++) {
var rowNum = (((c - 48) * 16) + 7);
spread.getSheet(s).getCell(1, c).formula("=IF(Bracket!$E$" + rowNum + " <>\"\", Bracket!$E$" + rowNum + ", \"— Undecided —\"");
spread.getSheet(s).getCell(2, c).formula("=IF(Bracket!$E$" + (rowNum + 8) + " <>\"\", Bracket!$E$" + (rowNum + 8) + ", \"— Undecided —\"");
}
for (var c = 52; c <= 55; c++) {
var rowNum = (((c - 52) * 16) + 7);
spread.getSheet(s).getCell(1, c).formula("=IF(Bracket!$P$" + rowNum + " <>\"\", Bracket!$P$" + rowNum + ", \"— Undecided —\"");
spread.getSheet(s).getCell(2, c).formula("=IF(Bracket!$P$" + (rowNum + 8) + " <>\"\", Bracket!$P$" + (rowNum + 8) + ", \"— Undecided —\"");
}
for (var c = 56; c <= 57; c++) {
var rowNum = (((c - 56) * 32) + 11);
spread.getSheet(s).getCell(1, c).formula("=IF(Bracket!$F$" + rowNum + " <>\"\", Bracket!$F$" + rowNum + ", \"— Undecided —\"");
spread.getSheet(s).getCell(2, c).formula("=IF(Bracket!$F$" + (rowNum + 16) + " <>\"\", Bracket!$F$" + (rowNum + 16) + ", \"— Undecided —\"");
}
for (var c = 58; c <= 59; c++) {
var rowNum = (((c - 58) * 32) + 11);
spread.getSheet(s).getCell(1, c).formula("=IF(Bracket!$O$" + rowNum + " <>\"\", Bracket!$O$" + rowNum + ", \"— Undecided —\"");
spread.getSheet(s).getCell(2, c).formula("=IF(Bracket!$O$" + (rowNum + 16) + " <>\"\", Bracket!$O$" + (rowNum + 16) + ", \"— Undecided —\"");
}
spread.getSheet(s).getCell(1, 60).formula("=IF(Bracket!$G$19 <>\"\", Bracket!$G$19, \"— Undecided —\"");
spread.getSheet(s).getCell(2, 60).formula("=IF(Bracket!$G$51 <>\"\", Bracket!$G$51, \"— Undecided —\"");
spread.getSheet(s).getCell(1, 61).formula("=IF(Bracket!$M$19 <>\"\", Bracket!$M$19, \"— Undecided —\"");
spread.getSheet(s).getCell(2, 61).formula("=IF(Bracket!$M$51 <>\"\", Bracket!$M$51, \"— Undecided —\"");
spread.getSheet(s).getCell(1, 62).formula("=IF(Bracket!$I$26 <>\"\", Bracket!$I$26, \"— Undecided —\"");
spread.getSheet(s).getCell(2, 62).formula("=IF(Bracket!$K$44 <>\"\", Bracket!$K$44, \"— Undecided —\"");
}
}
Add Data Validation
Now that we have the hidden sheets set up, we can connect them to formula validation lists in the “Bracket" and “Tracker" sheets. Starting with the “Bracket" sheet, I set the formula validation lists round by round for each division. I also added an error message for when the user attempts to type in a team that does not exist in the options:
function AddDataValidationBracket() {
spread.setActiveSheet("Bracket");
var secondRoundRowNumLeft = 0;
var secondRoundRowNumRight = 16;
for (var r = 4; r <= 64; r += 4) {
var columnNameLeft = spread.getSheet(2).getCell(0, secondRoundRowNumLeft, GC.Spread.Sheets.SheetArea.colHeader).value();
var dvLeft = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!" + columnNameLeft + "1:" + columnNameLeft + "3");
dvLeft.showErrorMessage(true);
dvLeft.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(r, 3, 1, 1, dvLeft);
secondRoundRowNumLeft++;
var columnNameRight = spread.getSheet(2).getCell(0, secondRoundRowNumRight, GC.Spread.Sheets.SheetArea.colHeader).value();
var dvRight = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!" + columnNameRight + "1:" + columnNameRight + "3");
dvRight.showErrorMessage(true);
dvRight.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(r, 16, 1, 1, dvRight);
secondRoundRowNumRight++;
}
var sweet16RowNumLeft = 32;
var sweet16RowNumRight = 40;
for (var r = 6; r <= 62; r += 8) {
var columnNameLeft = spread.getSheet(2).getCell(0, sweet16RowNumLeft, GC.Spread.Sheets.SheetArea.colHeader).value();
var dvLeft = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!" + columnNameLeft + "1:" + columnNameLeft + "3");
dvLeft.showErrorMessage(true);
dvLeft.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(r, 4, 1, 1, dvLeft);
sweet16RowNumLeft++;
var columnNameRight = spread.getSheet(2).getCell(0, sweet16RowNumRight, GC.Spread.Sheets.SheetArea.colHeader).value();
var dvRight = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!" + columnNameRight + "1:" + columnNameRight + "3");
dvRight.showErrorMessage(true);
dvRight.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(r, 15, 1, 1, dvRight);
sweet16RowNumRight++;
}
var elite8RowNumLeft = 48;
var elite8RowNumRight = 52;
for (var r = 10; r <= 58; r += 16) {
var columnNameLeft = spread.getSheet(2).getCell(0, elite8RowNumLeft, GC.Spread.Sheets.SheetArea.colHeader).value();
var dvLeft = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!" + columnNameLeft + "1:" + columnNameLeft + "3");
dvLeft.showErrorMessage(true);
dvLeft.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(r, 5, 1, 1, dvLeft);
elite8RowNumLeft++;
var columnNameRight = spread.getSheet(2).getCell(0, elite8RowNumRight, GC.Spread.Sheets.SheetArea.colHeader).value();
var dvRight = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!" + columnNameRight + "1:" + columnNameRight + "3");
dvRight.showErrorMessage(true);
dvRight.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(r, 14, 1, 1, dvRight);
elite8RowNumRight++;
}
var final4RowNumLeft = 56;
var final4RowNumRight = 58;
for (var r = 18; r <= 50; r += 32) {
var columnNameLeft = spread.getSheet(2).getCell(0, final4RowNumLeft, GC.Spread.Sheets.SheetArea.colHeader).value();
var dvLeft = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!" + columnNameLeft + "1:" + columnNameLeft + "3");
dvLeft.showErrorMessage(true);
dvLeft.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(r, 6, 1, 1, dvLeft);
final4RowNumLeft++;
var columnNameRight = spread.getSheet(2).getCell(0, final4RowNumRight, GC.Spread.Sheets.SheetArea.colHeader).value();
var dvRight = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!" + columnNameRight + "1:" + columnNameRight + "3");
dvRight.showErrorMessage(true);
dvRight.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(r, 12, 1, 1, dvRight);
final4RowNumRight++;
}
var dvLeftFinal = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!BI1:BI3");
dvLeftFinal.showErrorMessage(true);
dvLeftFinal.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(25, 8, 1, 1, dvLeftFinal);
var dvRightFinal = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!BJ1:BJ3");
dvRightFinal.showErrorMessage(true);
dvRightFinal.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(43, 10, 1, 1, dvRightFinal);
var dvFinal = GC.Spread.Sheets.DataValidation.createFormulaListValidator("ValidationBracket!BK1:BK3");
dvFinal.showErrorMessage(true);
dvFinal.errorMessage("Incorrect Value");
spread.getActiveSheet().setDataValidator(36, 9, 1, 1, dvFinal);
}
We will also want to protect the sheet—disabling the user's ability to click on any locked cells and to bind to the ValidationError event, so a message will appear when the user types in incorrect data:
spread.getActiveSheet().options.protectionOptions = { allowSelectLockedCells: false };
spread.getActiveSheet().options.isProtected = true;
spread.getActiveSheet().bind(GC.Spread.Sheets.Events.ValidationError, function (sender, args) {
if (args.validator.showErrorMessage()) {
if (confirm(args.validator.errorMessage())) {
args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.retry;
} else {
args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.forceApply;
}
}
});
We also want to add similar validation to the “Tracker" sheet, using different formulas for the formula validation lists:
function AddDataValidationTracker() {
spread.setActiveSheet("Tracker");
for (var r = 2; r <= 33; r++) {
var columnName = spread.getSheet(2).getCell(0, r - 2, GC.Spread.Sheets.SheetArea.colHeader).value();
var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator("=INDIRECT(\"Validation!" + columnName + "1:" + columnName + "3\")");
dv.showErrorMessage(true);
dv.errorMessage("Incorrect Value");
for (var c = 4; c <= 12; c += 2) {
spread.getActiveSheet().setDataValidator(r, c, 1, 1, dv);
spread.getActiveSheet().setText(r, c, "[Pick a team]");
}
}
for (var r = 38; r <= 53; r++) {
var columnName = spread.getSheet(2).getCell(0, r - 6, GC.Spread.Sheets.SheetArea.colHeader).value();
var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator("=INDIRECT(\"Validation!" + columnName + "1:" + columnName + "3\")");
dv.showErrorMessage(true);
dv.errorMessage("Incorrect Value");
for (var c = 4; c <= 12; c += 2) {
spread.getActiveSheet().setDataValidator(r, c, 1, 1, dv);
spread.getActiveSheet().setText(r, c, "[Pick a team]");
}
}
for (var r = 56; r <= 63; r++) {
var columnName = spread.getSheet(2).getCell(0, r - 8, GC.Spread.Sheets.SheetArea.colHeader).value();
var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator("=INDIRECT(\"Validation!" + columnName + "1:" + columnName + "3\")");
dv.showErrorMessage(true);
dv.errorMessage("Incorrect Value");
for (var c = 4; c <= 12; c += 2) {
spread.getActiveSheet().setDataValidator(r, c, 1, 1, dv);
spread.getActiveSheet().setText(r, c, "[Pick a team]");
}
}
for (var r = 66; r <= 69; r++) {
var columnName = spread.getSheet(2).getCell(0, r - 10, GC.Spread.Sheets.SheetArea.colHeader).value();
var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator("=INDIRECT(\"Validation!" + columnName + "1:" + columnName + "3\")");
dv.showErrorMessage(true);
dv.errorMessage("Incorrect Value");
for (var c = 4; c <= 12; c += 2) {
spread.getActiveSheet().setDataValidator(r, c, 1, 1, dv);
spread.getActiveSheet().setText(r, c, "[Pick a team]");
}
}
for (var r = 72; r <= 73; r++) {
var columnName = spread.getSheet(2).getCell(0, r - 12, GC.Spread.Sheets.SheetArea.colHeader).value();
var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator("=INDIRECT(\"Validation!" + columnName + "1:" + columnName + "3\")");
dv.showErrorMessage(true);
dv.errorMessage("Incorrect Value");
for (var c = 4; c <= 12; c += 2) {
spread.getActiveSheet().setDataValidator(r, c, 1, 1, dv);
spread.getActiveSheet().setText(r, c, "[Pick a team]");
}
}
var columnName = spread.getSheet(2).getCell(0, 62, GC.Spread.Sheets.SheetArea.colHeader).value();
var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator("=INDIRECT(\"Validation!" + columnName + "1:" + columnName + "3\")");
dv.showErrorMessage(true);
dv.errorMessage("Incorrect Value");
for (var c = 4; c <= 12; c += 2) {
spread.getActiveSheet().setDataValidator(76, c, 1, 1, dv);
spread.getActiveSheet().setText(76, c, "[Pick a team]");
}
spread.getActiveSheet().options.protectionOptions = { allowSelectLockedCells : false };
spread.getActiveSheet().options.isProtected = true;
spread.getActiveSheet().bind(GC.Spread.Sheets.Events.ValidationError, function (sender, args) {
if (args.validator.showErrorMessage()) {
if (confirm(args.validator.errorMessage())) {
args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.retry;
} else {
args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.forceApply;
}
}
});
}
Once this code is added, users will be able to select winning teams from each round in the “Bracket" sheet, while making predictions in the “Tracker" sheet. Here, you can track and compete for the most points.
Lastly, we can add the ability to export the workbook to Excel. We have already added the 2 HTML elements we needed, so we just need a small function to handle the exporting:
function ExportFile() {
var fileName = document.getElementById("exportFileName").value;
if (fileName.substr(-5, 5) !== '.xlsx') {
fileName += '.xlsx';
}
var json = JSON.stringify(spread.toJSON());
excelIO.save(json, function (blob) {
saveAs(blob, fileName);
}, function (e) {
console.log(e);
});
}
I hope you enjoyed this step-by-step Spread.Sheets tutorial. As you can see, creating a March Madness Tournament Bracket and tracker is quick and easy with Spread.Sheets. The built-in conditional formatting and data validation features enable users to track winner predictions efficiently. Furthermore,the built-in Spread functionality is compatible with Excel, which makes exporting the workbook to an Excel file easy.
Get the sample
Try SpreadJS today
Kevin Ashley