Pickleball Round Robin Generator

\n \n View source on Val Town\n \n \n

\n );\n}\n\nfunction client() {\n createRoot(document.getElementById(\"root\")).render();\n}\n\nif (typeof document !== \"undefined\") {\n client();\n}\n\nfunction shuffleArray(array: T[]): T[] {\n for (let i = array.length - 1; i > 0; i--) {\n const j = Math.floor(Math.random() * (i + 1));\n [array[i], array[j]] = [array[j], array[i]];\n }\n return array;\n}\n\nfunction generateRoundRobin(\n numberOfPlayers: number,\n courts: number,\n rounds: number,\n) {\n const players = Array.from(\n { length: numberOfPlayers },\n (_, i) => `P${String(i + 1).padStart(3, \"0\")}`,\n );\n const schedule: { pairings: string[][]; byes: string[] }[] = [];\n const playerStats = new Map(\n players.map(\n (player) => [player, { gamesPlayed: 0, byes: 0, lastByeRound: -1 }]\n ),\n );\n const partnerships = new Map(\n players.map((player) => [player, new Set()]),\n );\n\n for (let round = 0; round < rounds; round++) {\n const roundPairings: string[][] = [];\n const byes: string[] = [];\n const availablePlayers = shuffleArray([...players]);\n\n // How many byes do we need this round?\n const byesNeeded = availablePlayers.length - courts * 4;\n if (byesNeeded > 0) {\n // Separate those who did NOT have a bye last round vs. those who DID\n const noRecentBye = availablePlayers.filter((p) =>\n playerStats.get(p)!.lastByeRound !== round - 1\n );\n const recentBye = availablePlayers.filter((p) =>\n playerStats.get(p)!.lastByeRound === round - 1\n );\n\n // Sort both groups with the original distribution logic\n const sortFn = (a: string, b: string) => {\n const statA = playerStats.get(a)!;\n const statB = playerStats.get(b)!;\n if (statA.byes !== statB.byes) return statA.byes - statB.byes;\n return (round - statA.lastByeRound) - (round - statB.lastByeRound);\n };\n noRecentBye.sort(sortFn);\n recentBye.sort(sortFn);\n\n // Merge them, picking from noRecentBye first\n const sortedPotentialByes = [...noRecentBye, ...recentBye];\n\n // Assign actual byes\n for (let i = 0; i < byesNeeded; i++) {\n if (!sortedPotentialByes.length) break;\n const player = sortedPotentialByes.shift()!;\n byes.push(player);\n const stats = playerStats.get(player)!;\n stats.byes++;\n stats.lastByeRound = round;\n }\n\n // Remove all byes from availablePlayers to avoid double-allocation\n byes.forEach((p) => {\n const idx = availablePlayers.indexOf(p);\n if (idx >= 0) availablePlayers.splice(idx, 1);\n });\n }\n\n // Allocate players to courts in groups of 4\n while (availablePlayers.length >= 4) {\n const court: string[] = [];\n for (let i = 0; i < 4; i++) {\n let bestPlayer: string | null = null;\n let leastCommonPartners = Infinity;\n\n for (const player of availablePlayers) {\n const playerPartnerships = partnerships.get(player)!;\n // Check how many players on this court they've already teamed with\n const commonPartners = court.filter((p) =>\n playerPartnerships.has(p)\n ).length;\n if (commonPartners < leastCommonPartners) {\n leastCommonPartners = commonPartners;\n bestPlayer = player;\n }\n }\n\n if (bestPlayer) {\n court.push(bestPlayer);\n availablePlayers.splice(availablePlayers.indexOf(bestPlayer), 1);\n playerStats.get(bestPlayer)!.gamesPlayed++;\n }\n }\n\n // Update partnerships for the 4 players on this court\n for (let i = 0; i < 4; i++) {\n for (let j = i + 1; j < 4; j++) {\n partnerships.get(court[i])!.add(court[j]);\n partnerships.get(court[j])!.add(court[i]);\n }\n }\n\n roundPairings.push(court);\n }\n\n schedule.push({ pairings: roundPairings, byes });\n }\n\n return schedule;\n}\n\nasync function generateExcelFile(\n numberOfPlayers: number,\n schedule: { pairings: string[][]; byes: string[] }[],\n) {\n const ExcelJS = await import(\"https://esm.sh/exceljs@4.3.0\");\n\n const workbook = new ExcelJS.Workbook();\n\n // Player Names worksheet\n const playerNamesSheet = workbook.addWorksheet(\"Player Names\");\n playerNamesSheet.addRow([\"Player Number\", \"Player Name\"]);\n for (let i = 1; i <= numberOfPlayers; i++) {\n if (i === 1) {\n playerNamesSheet.addRow([`P${String(i).padStart(3, \"0\")}`, \"Player 01\"]);\n } else if (i === 2) {\n playerNamesSheet.addRow([`P${String(i).padStart(3, \"0\")}`, \"Player 02\"]);\n } else {\n playerNamesSheet.addRow([\n `P${String(i).padStart(3, \"0\")}`,\n `Player ${i}`,\n ]);\n }\n }\n playerNamesSheet.getColumn(1).width = 15;\n playerNamesSheet.getColumn(2).width = 20;\n\n // Schedule worksheet\n const scheduleSheet = workbook.addWorksheet(\"Schedule\");\n scheduleSheet.addRow([\n \"Round\",\n \"Court\",\n \"Team 1\",\n \"Team 2\",\n \"Score Team 1\",\n \"Score Team 2\",\n \"Team 1 Win\",\n \"Team 2 Win\",\n ]);\n let rowIndex = 2;\n schedule.forEach((round, roundIndex) => {\n round.pairings.forEach((pairing, courtIndex) => {\n scheduleSheet.addRow([\n roundIndex + 1,\n courtIndex + 1,\n {\n formula: `VLOOKUP(\"${pairing[0]}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \" / \" & VLOOKUP(\"${\n pairing[1]\n }\", 'Player Names'!$A$2:$B$${numberOfPlayers + 1}, 2, FALSE)`,\n },\n {\n formula: `VLOOKUP(\"${pairing[2]}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \" / \" & VLOOKUP(\"${\n pairing[3]\n }\", 'Player Names'!$A$2:$B$${numberOfPlayers + 1}, 2, FALSE)`,\n },\n null,\n null,\n {\n formula:\n `IF(AND(E${rowIndex}<>\"\", F${rowIndex}<>\"\"), IF(E${rowIndex}>F${rowIndex}, 1, 0), \"\")`,\n },\n {\n formula:\n `IF(AND(E${rowIndex}<>\"\", F${rowIndex}<>\"\"), IF(F${rowIndex}>E${rowIndex}, 1, 0), \"\")`,\n },\n ]);\n rowIndex++;\n });\n if (round.byes.length > 0) {\n scheduleSheet.addRow([\n roundIndex + 1,\n \"Byes\",\n {\n formula: `TEXTJOIN(\", \", TRUE, ${\n round.byes.map((player) =>\n `VLOOKUP(\"${player}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE)`\n )\n .join(\", \")\n })`,\n },\n ]);\n rowIndex++;\n }\n scheduleSheet.addRow([]);\n rowIndex++;\n });\n\n scheduleSheet.getColumn(\"A\").width = 10;\n scheduleSheet.getColumn(\"B\").width = 10;\n scheduleSheet.getColumn(\"C\").width = 30;\n scheduleSheet.getColumn(\"D\").width = 30;\n scheduleSheet.getColumn(\"E\").width = 15;\n scheduleSheet.getColumn(\"F\").width = 15;\n scheduleSheet.getColumn(\"G\").width = 10;\n scheduleSheet.getColumn(\"H\").width = 10;\n\n // Statistics worksheet\n const statsSheet = workbook.addWorksheet(\"Statistics\");\n statsSheet.addRow([\n \"Player\",\n \"Games Played\",\n \"Wins\",\n \"Total Points\",\n \"Points Percentage\",\n \"Byes\",\n ]);\n for (let i = 1; i <= numberOfPlayers; i++) {\n const row = i + 1;\n const playerNumber = `P${String(i).padStart(3, \"0\")}`;\n statsSheet.addRow([\n {\n formula: `VLOOKUP(\"${playerNumber}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE)`,\n },\n {\n // Only count the game if the scores (columns E & F) are not empty\n formula: `\n COUNTIFS(\n Schedule!$C$2:$C$${rowIndex - 1}, \n \"*\" & VLOOKUP(\"${playerNumber}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \"*\",\n Schedule!$E$2:$E$${rowIndex - 1}, \"<>\",\n Schedule!$F$2:$F$${rowIndex - 1}, \"<>\"\n )\n +\n COUNTIFS(\n Schedule!$D$2:$D$${rowIndex - 1}, \n \"*\" & VLOOKUP(\"${playerNumber}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \"*\",\n Schedule!$E$2:$E$${rowIndex - 1}, \"<>\",\n Schedule!$F$2:$F$${rowIndex - 1}, \"<>\"\n )\n `.replace(/\\s+/g, \" \"), // just flatten the formula whitespace\n },\n {\n formula: `\n SUMIF(\n Schedule!$C$2:$C$${rowIndex - 1},\n \"*\" & VLOOKUP(\"${playerNumber}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \"*\",\n Schedule!$G$2:$G$${rowIndex - 1}\n )\n +\n SUMIF(\n Schedule!$D$2:$D$${rowIndex - 1},\n \"*\" & VLOOKUP(\"${playerNumber}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \"*\",\n Schedule!$H$2:$H$${rowIndex - 1}\n )\n `.replace(/\\s+/g, \" \"),\n },\n {\n formula: `\n SUMIF(\n Schedule!$C$2:$C$${rowIndex - 1},\n \"*\" & VLOOKUP(\"${playerNumber}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \"*\",\n Schedule!$E$2:$E$${rowIndex - 1}\n )\n +\n SUMIF(\n Schedule!$D$2:$D$${rowIndex - 1},\n \"*\" & VLOOKUP(\"${playerNumber}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \"*\",\n Schedule!$F$2:$F$${rowIndex - 1}\n )\n `.replace(/\\s+/g, \" \"),\n },\n {\n formula: `IF(B${row}=0, 0, D${row}/(B${row}*11))`,\n },\n {\n // Only count a bye if column B == \"Byes\" and column C includes the player's name\n formula: `\n COUNTIFS(\n Schedule!$B$2:$B$${rowIndex - 1}, \"Byes\",\n Schedule!$C$2:$C$${rowIndex - 1},\n \"*\" & VLOOKUP(\"${playerNumber}\", 'Player Names'!$A$2:$B$${\n numberOfPlayers + 1\n }, 2, FALSE) & \"*\"\n )\n `.replace(/\\s+/g, \" \"),\n },\n ]);\n }\n\n statsSheet.getColumn(\"A\").width = 20;\n statsSheet.getColumn(\"B\").width = 15;\n statsSheet.getColumn(\"C\").width = 10;\n statsSheet.getColumn(\"D\").width = 15;\n statsSheet.getColumn(\"E\").width = 20;\n statsSheet.getColumn(\"F\").width = 10;\n\n const buffer = await workbook.xlsx.writeBuffer();\n return buffer;\n}\n\nexport default async function server(request: Request): Promise {\n if (\n request.method === \"POST\" && new URL(request.url).pathname === \"/generate\"\n ) {\n const { numberOfPlayers, courts, rounds } = await request.json();\n const schedule = generateRoundRobin(numberOfPlayers, courts, rounds);\n const excelBuffer = await generateExcelFile(numberOfPlayers, schedule);\n\n return new Response(excelBuffer, {\n headers: {\n \"Content-Type\":\n \"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\",\n \"Content-Disposition\":\n \"attachment; filename=pickleball_round_robin.xlsx\",\n },\n });\n }\n\n return new Response(\n `\n \n \n Pickleball Round Robin Generator\n \n \n \n

\n \n \n \n \n `,\n {\n headers: {\n \"content-type\": \"text/html\",\n },\n },\n );\n}\n\nconst css = `\nbody {\n font-family: Arial, sans-serif;\n max-width: 800px;\n margin: 0 auto;\n padding: 20px;\n}\n\nh1 {\n color: #333;\n}\n\nform {\n display: flex;\n flex-direction: column;\n gap: 15px;\n}\n\nlabel {\n font-weight: bold;\n}\n\ninput {\n width: 100%;\n padding: 8px;\n border: 1px solid #ccc;\n border-radius: 4px;\n}\n\nbutton {\n background-color: #4CAF50;\n color: white;\n padding: 10px 15px;\n border: none;\n border-radius: 4px;\n cursor: pointer;\n font-size: 16px;\n}\n\nbutton:hover {\n background-color: #45a049;\n}\n`;","deleted","label",[99],"permalink","https://www.val.town/x/stevekrouse/pickball_round_robin/code/main.tsx","extension","actionData","errors"]