Last active
July 14, 2018 06:16
-
-
Save Poeticalto/ed06fab60c8ac8649e33f42aecbec837 to your computer and use it in GitHub Desktop.
NATPT Scripts, for use with the Generic NATPT Tournament Spreadsheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/////////////////////////////////////////////////////////// | |
// 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