Skip to content

Instantly share code, notes, and snippets.

@Poeticalto

Poeticalto/tournament_setup.gs

Last active July 14, 2018 06:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Poeticalto/ed06fab60c8ac8649e33f42aecbec837 to your computer and use it in GitHub Desktop.
Save Poeticalto/ed06fab60c8ac8649e33f42aecbec837 to your computer and use it in GitHub Desktop.
Download ZIP
NATPT Scripts, for use with the Generic NATPT Tournament Spreadsheet
Raw
tournament_setup.gs
///////////////////////////////////////////////////////////
// NATPT Tournament Scripts //
// Author: Poeticalto //
// Version: 1.0-A //
///////////////////////////////////////////////////////////
// Specific Tournament Info //
// Tournament Name: Generic NATPT-Style Tournament [Abbr]//
// Advanced Statistics: False //
// Autoscore Version: AB+ [R] //
// Bot Setup: False //
// Standard Champions: True //
// Standard Formatting: True //
// Standard Season Index #: 2 //
// Standard Template: True //
// Standard Tree: True //
///////////////////////////////////////////////////////////
// Excluded Scripts //
// TreeToDiscord //
// TournamentAnalytics //
///////////////////////////////////////////////////////////
// Custom Vars //
var tournamentName = ;
var spreadsheetID = ;
var formID = ;
var seasonModifier = 0;
///////////////////////////////////////////////////////////
function Autoscore() { // This script refreshes the IMPORTHTML function used for the autoscore system.
var ss = SpreadsheetApp.openById(spreadsheetID); // Tournament Spreadsheet
// To use, click on the clock to set up a minutes trigger
var sheet = ss.getSheets()[2]; // assumes the season sheet is on index 5, or 6 from the left
var valueOld = sheet.getRange("P2").getValue();
sheet.getRange('P2').setValue(''); // temp switcher value
SpreadsheetApp.flush();
sheet.getRange('P2').setValue(valueOld); // sets back to default
}
function Documentation(){// Creates a UI box which contains a link to the NATPT Documentation
if (typeof Browser != "undefined"){// redundant since a bot will never use this function, but just in case I guess
var url = "https://docs.google.com/document/d/15bPUV8ctqPE4xaFAmU_FsfpdH9J07zCPs_pF4ssAMhw/edit?usp=sharing";
var name = "Click here for the NATPT Documentation/Help!";
var html = '<html><body><a href="'+url+'" target="blank" onclick="google.script.host.close()">'+name+'</a></body></html>';
var ui = HtmlService.createHtmlOutput(html)
SpreadsheetApp.getUi().showModelessDialog(ui,"NATPT Documentation");
}
}
function DraftBoardSetup() { // This script sets up the draft board.
ScriptApp.newTrigger('Autoscore').timeBased().everyMinutes(1).create(); // Creates new trigger for Autoscore
var ss = SpreadsheetApp.openById(spreadsheetID); // Tournament Spreadsheet
var sheet = ss.getSheets()[2]; // assumes that the current season is on index 5, or 6 from the left
var check = sheet.getRange("M4").getValue();
if (check == "TagPro Name"){
var range = sheet.getRange("T3"); // gets the cell for team number
var cell = range.getValue(); // sets var for number of teams
var sh2 = ss.getSheetByName('Templates.STND'); // sheet of the draft board
// List of Templates:
// STND = Standard Templates
var valuesrand = [];
for (i=1;i<=cell;i++){
valuesrand.push(["=RAND()"]);}
if ( cell == 3 ) {
sheet.insertRows(4, 7); // inserts space for the board
var rangeToCopy = sh2.getRange(6, 12, 7, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q8');
var rangep = sheet.getRange('M12:M23');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("AF6:AF8").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("AE6:AF8").sort(32);
var rangecap = sheet.getRange("AG6:AH8").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 4 ) {
sheet.insertRows(4, 13);
var rangeToCopy = sh2.getRange(13, 12, 13, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q9');
var rangep = sheet.getRange('M18:M33');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("Z6:Z9").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("Y6:Z9").sort(26);
var rangecap = sheet.getRange("AA6:AB9").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 6 ) {
sheet.insertRows(4, 16);
var rangeToCopy = sh2.getRange(26, 12, 16, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q11');
var rangep = sheet.getRange('M21:M44');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("AF6:AF11").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("AE6:AF11").sort(32);
var rangecap = sheet.getRange("AG6:AH11").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 8 ) {
sheet.insertRows(4, 20);
var rangeToCopy = sh2.getRange(42, 12, 20, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q13');
var rangep = sheet.getRange('M25:M56');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("Z6:Z13").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("Y6:Z13").sort(26);
var rangecap = sheet.getRange("AA6:AB13").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 9 ) {
sheet.insertRows(4, 20);
var rangeToCopy = sh2.getRange(62, 12, 20, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q14');
var rangep = sheet.getRange('M25:M60');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("AF6:AF14").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("AE6:AF14").sort(32);
var rangecap = sheet.getRange("AG6:AH14").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 12 ) {
sheet.insertRows(4, 23);
var rangeToCopy = sh2.getRange(82, 12, 23, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q17');
var rangep = sheet.getRange('M28:M75');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("AF6:AF17").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("AE6:AF17").sort(32);
var rangecap = sheet.getRange("AG6:AH17").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 16 ) {
sheet.insertRows(4, 30);
var rangeToCopy = sh2.getRange(105, 12, 30, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q21');
var rangep = sheet.getRange('M35:M98');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("V6:V21").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("U6:V21").sort(26);
var rangecap = sheet.getRange("W6:X21").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 18 ) {
sheet.insertRows(4, 32);
var rangeToCopy = sh2.getRange(135, 12, 32, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q23');
var rangep = sheet.getRange('M37:M108');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("AF6:AF23").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("AE6:AF23").sort(32);
var rangecap = sheet.getRange("AG6:AH23").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 21 ) {
sheet.insertRows(4, 35);
var rangeToCopy = sh2.getRange(167, 12, 35, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q26');
var rangep = sheet.getRange('M40:M123');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("AF6:AF26").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("AE6:AF26").sort(32);
var rangecap = sheet.getRange("AG6:AH26").copyTo(rangerand,{contentsOnly: true});
}
else if ( cell == 24 ) {
sheet.insertRows(4, 38);
var rangeToCopy = sh2.getRange(202, 12, 38, 24); // location of the board
rangeToCopy.copyTo(sheet.getRange(4, 12)); // copies board to the season sheet
var range = sheet.getRange('O6:Q29');
var rangep = sheet.getRange('M43:M138');
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangep,false).build();
range.setDataValidation(validation);
var rangerand = sheet.getRange("AF6:AF29").setValues(valuesrand);
SpreadsheetApp.flush();
var rangesort = sheet.getRange("AE6:AF29").sort(32);
var rangecap = sheet.getRange("AG6:AH29").copyTo(rangerand,{contentsOnly: true});
}
}
else {
if (typeof Browser != "undefined"){
Browser.msgBox("Error: A draft board was already detected, please ensure the sheet is setup properly. c:");
}
}
}
function FormSetup() { // This script sets up the form and pings the NATPT Administration group.
var form = FormApp.openById(formID); // Tournament Form
var ss = SpreadsheetApp.openById(spreadsheetID); // Tournament Spreadsheet
// Pings GroupMe group that Tournament is being setup.
var POST_URL = "https://api.groupme.com/v3/bots/post";
var payload =
{"bot_id": '2e9481aaf48996e1bbc6e2fccb',// bot ID for the NATPT group
"text": tournamentName+' has just set up a new season!'}
var options =
{
"method" : "post",
"payload" : payload
};
UrlFetchApp.fetch(POST_URL, options);
// Sets up the Form
form.removeDestination(); // Unlinks the form from its current location, done as a precaution
form.deleteAllResponses(); // Deletes all responses from the form
form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId()); // links form to spreadsheet
var sheet = ss.getSheets()[0]; // Google Forms always inserts to the far left, which is spreadsheet 0.
var ssurl = ss.getUrl(); // gets URL of the spreadsheet
var sheetid = sheet.getSheetId(); // gets the ID of the spreadsheet
form.setDescription('Spreadsheets here: ' + ssurl+'#gid='+sheetid); // updates the description with the new spreadsheet link
form.setConfirmationMessage('Thanks for signing up and have fun! c: If you missed it, link to the spreadsheet: ' + ssurl+'#gid='+sheetid); // updates the confirmation message with the new spreadsheet link
var hof = ss.getSheetByName('Season Champions')
hof.getRange('O23').setValue(ssurl+'#gid='+sheetid); // Updates the current Spreadsheet link corresponding to the tourney
var sheetdate = hof.getRange('p4');
var sheetdset = hof.getRange('o4');
sheetdset.copyTo(sheetdate,{contentsOnly: true});
}
function onOpen() { // This function creates the UI menu to run the scripts.
SpreadsheetApp.getUi().createMenu('Tournament Setup')
.addItem('Tournament Documentation/Help','Documentation')
.addSeparator()
.addItem('1a: Set up Form', 'FormSetup')
.addItem('1b: Set up Sheet', 'SheetSetup')
.addSeparator()
.addItem('2a: Set up Draft', 'DraftBoardSetup')
.addItem('2b: Refresh Autoscore', 'Autoscore')
.addSeparator()
.addItem('3a: Season Cleanup', 'TournamentCleanup')
.addToUi()
}
function SheetSetup() { // This function sets up the sheet after the form.
var ss = SpreadsheetApp.openById(spreadsheetID); // Tournament Spreadsheet
var sheet = ss.getSheets()[0]; // Assumes current sheet is on index 0, or on the far left
sheet = ss.getSheets()[0];
var check = sheet.getRange("A1").getValue();
if (check == "Timestamp"){
var season = ss.getSheets().length-6+seasonModifier; // -10 is used to get to the right season number, may change depending on implementation
ss.setActiveSheet(ss.getSheets()[0]); // used for the renameActiveSheet in the next line
ss.renameActiveSheet("S-"+season); // exhibits buggy behavior due to potential timeout, make sure that this sheet is open and AGtive to reduce issues
sheet.deleteColumns(8,5); // also exhibits buggy behavior due to potential timeout, just reset and try the script again if it fails at this point
sheet.setColumnWidth(7,100); // in order to standardize the scoring table, columns H-L are deleted, G is set to default size 100
sheet.insertColumnsBefore(1,11); // inserts 7 columns to make room for Autoscore,Backscore
sheet.setFrozenRows(0); // Google Forms freezes 1 row by default, changes to 0 rows
var sh1 = ss.getSheetByName('Ban List'); // Location of Conditional Formatting
var rowEnd = sheet.getLastRow(); // used for the lols
sh1.getRange("M1:Q").copyFormatToRange(sheet, 13, 17, 1, rowEnd); // copies conditional formatting to the form sheet
sheet.insertRows(1, 3); // inserts space for the header
var sh2 = ss.getSheetByName('Templates.STND'); // sheet of the header
var rangeToCopy = sh2.getRange(1, 1, 4, 35); // location of the header
rangeToCopy.copyTo(sheet.getRange(1, 1)); // copies header to the season sheet
sheet.hideColumns(1, 12); // hides Col A-L
ss.setActiveSheet(ss.getSheets()[0]);
ss.moveActiveSheet(3);
var sh2 = ss.getSheetByName('Season Champions'); // sheet of the Forms
sh2.getRange('O6').setValue(season);
sheet.getRange('M2').setValue("Season "+season); // sets number of season
}
else {
if (typeof Browser != 'undefined'){
Browser.msgBox("Error: A new season was not detected, please set up the form before activating SheetSetup. c:");
}
}
}
function test(){
Logger.log("Congrats! You have successfully authorized the NATPT scripts!");
}
function TournamentCleanup(){
var ss = SpreadsheetApp.openById(spreadsheetID); // Tournament Spreadsheet
var allTriggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < allTriggers.length; i++) {// removes all triggers
ScriptApp.deleteTrigger(allTriggers[i]);
}
ScriptApp.newTrigger('onOpen').forSpreadsheet(ss).onOpen().create(); // recreates menu trigger
var seasonold = ss.getSheets()[2]; // denotes the old season
seasonold.deleteColumns(1,11); // deletes autoscore stuff from the season, used to reduce clutter
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment