Skip to content

Standard Tournament Generator

sage-tools-api/apps-script/standard-tournament-generator.gs — bound Apps Script that builds one facility-day's standard-tournament workbook from a Tournament Calculator plan CSV. Implements standard-tournament-master-spec.md; its §18 records where the code departs from it.

It is bound to the SAGE Standard Tournament Master workbook (spec §14), whose hidden _CATEGORY_TEMPLATE is an unchanged copy of the Pickle for Sight Annex's HIMD tab: every category tab takes its formats from fixed cells of it (TEMPLATE_PROTO).

Same arrangement as the Dual Meet Sheet Generator: it lives in the API repo for versioning, runs inside a workbook, and ships by being pasted into the master's script project beside sheets-sync.gs. Changing it is not a deploy. It needs a master for the same reason — the tabs run on the workbook-level Named Function library, here in its 22-function standard form (spec §11), with the row-5-label MATCHCOURT.

A separate file, not a mode

The two generators share no code at runtime: each master carries only its own. standard-tournament-generator.gs copies dual-meet-generator.gs's pure helpers verbatim (CSV parsing, colLetter_, the progress log, assertTabsPristine_) and rewrites everything shaped around clubs. The onOpen block is the one piece duplicated verbatim across all three .gs files, because a workbook carries sheets-sync.gs plus one generator and the last-loaded onOpen wins.

Pipeline

parsePlanCsv ─► validatePlan ─► planCategory (per ticked category)
                                     │
                     computeTabLayout_ ─► packSchedule_ (in memory: sizes SCHEDULE)
                                     │
category tabs → Variables → Title → Reference for Players → MATCHES → SCHEDULE
             → CSV → STANDINGSCSV → Court Control → Timeline → ATTENDANCE

ATTENDANCE is created empty, in the format event attendance fills (buildAttendanceTab_); a tab of that name already there is left alone.

Everything left of the Sheets writes is pure. planCategory is where every playoff shape lives, and the only part with real logic.

planCategory

Ports the calculator's groupSizes, reduceToTarget, solveForTarget, tieredRounds and playoffPlan (keep them in step with tools/tournament-calculator.html), then:

  • Round robin from RR_GUIDE_, SAGE's bracket guide (the RR tab of the BRACKET GUIDE workbook): per bracket size, which pairs meet in each round and in what order. A size the guide lacks (8, 11-13) falls back to the circle method, with a warning. Brackets are uneven, so each carries its own first-pair index, and the guide's pair numbers are offset from it. Codes run <KEY>_1 … <KEY>_<teams> unbroken, bracket 1 first.
  • Seat numbering (spec §7.6.1). Stages are walked from the final back, with one counter shared by every stage's fresh seats, so a drawn slot number identifies exactly one seat. Fed seats take the other half of a fresh partner's pair, or the lowest free number. Each stage's fed seats are wired to the previous stage's winners in block-label order.
  • Shapes without a ladder: best-of-3 (2 pairs), twice-to-beat (one bracket, the default) with a bronze walkover against BYE, and round-robin only.

It also reports the MATCHES rows and band width, every team code the tab holds (which sizes STANDINGSCSV), and any validation error: a ladder deeper than R32, more than 32 entrants, or a code or label that would repeat.

Formats come from the template

Nothing in the script sets a colour or a font. buildCategoryTab copies the template tab, lays its all-black row 34 over everything below the chrome, then copies each block's format from its fixed prototype cell in TEMPLATE_PROTO: pair rows, grid cells, banners, match blocks, the roster, the draw and the slot table. checkTemplate_ refuses to run unless _CATEGORY_TEMPLATE shows the reference HIMD tab's banner labels where TEMPLATE_PROTO expects them. Merges are broken in two pieces that cut through none of the template's — A:AA from row 6, AB onward from row 4 — since Sheets refuses to unmerge a range that splits a merge. applyTemplateRules_ stretches the template's own conditional formats to each tab's height and drops the two pinned to HIMD's cells.

Forward propagation

A standard ladder can't pull its entrants from a sorted group block the way a dual meet's playoff does — there is a draw and a chain of results in the way. So each block pushes its winner forward: column K evaluates to the code of the seat the winner moves to, and a fed seat finds its names with FILTER over the previous stage's B/K rows. Fresh seats read the qualifier scaffold (AO/AP), which resolves a drawn slot number through the slot table (AT:AV). A two-bracket category's slot numbers are written in rather than drawn (fixedSeating_), because its semifinals are a fixed crossover.

MATCHES and the packer

MATCHES is a copy of the pristine SCHEDULE prototype, tiled to one band per category, so pasting from it into SCHEDULE is a plain copy. Nothing reads it. Row labels sit in the separator column left of each band, out of the way of a paste. Block headers come from blockLabels: a bracket's round-robin matches always sit under that bracket's floor(n_b / 2) blocks, because every round of a bracket fills exactly that many and brackets run largest first.

SCHEDULE and MATCHES both lift their conditional formats off before tiling and restore them afterwards with one range per block column; tiled with copyTo, each rule would list every cell separately and setConditionalFormatRules fails outright past a few hundred.

packSchedule_ places every match in waves, taking turns between categories, never reordering a category's queue, holding each playoff stage until the one before it has finished, and keeping one slot clear after a round robin for the qualifier draw. Its only outputs are SCHEDULE's height (slots + 4 spare) and the suggested layout in the execution log. It reproduces the Pickle for Sight days at 33 and 29 slots.

In place, once

SCHEDULE, Court Control, Timeline, CSV, STANDINGSCSV and Reference for Players are grown from prototypes already in the master (REBUILT_IN_PLACE_TABS), because the sync watches SCHEDULE's and Court Control's GIDs. A second run is refused before anything is written — by the pristine check, and by the category tabs and MATCHES colliding by name.

After every tab is built, workbookName_ renames the spreadsheet to <date> <title> - <FACILITY> from the plan and the facility label; a failed or rejected run leaves the copy's name alone. dual-meet-generator.gs carries the same helper, with the venue label.

CSV gets its header and one formula row with A2 blank. SAGE → Fill match numbers (in sheets-sync.gs) copies that row down to one row per match. Slot times on SCHEDULE and Timeline's headers both chain off Variables!C4 + Variables!$C$6, never literals, so COUNTPAIRAT's exact match holds.

Importing bracket draws

SAGE → Import bracket draws runs after generation, shares nothing with generateEventTabs beyond the trace and progress-log helpers, and lives in the same file. It sits outside the PROP_TABS_GENERATED_FOR guard, so unlike Generate event tabs it stays in the menu — a re-draw is imported over the top with Replace existing names.

The pipeline is pure up to the last step. parseDrawText_ reads one of the Bracket Generator's .txt exports, anchored on the Drawn … line in the first six lines: the category is the line above it, brackets and pairs are read in file order, and parsing stops at TO CHECK THIS DRAW YOURSELF. It never throws — an unreadable file comes back carrying its own errors. splitPairNames_ splits a pair line on the earliest of /, &, +, , or and, and reports when it found no separator rather than failing.

readPlanBlock_ reads Variables!I11:L<last> — key, display value, teams, brackets — and flags the rows that have a real tab, since Variables lists every category in the plan CSV including the other venue's. resolveDrawCategory_ matches a file's category line against the key, then the display name, both normalised (trim, upper-case, collapse whitespace); an operator-picked assignment beats both.

validateDraws_ runs every check across every file before anything is written and returns all failures at once, including the emptiness of AD5:AD<4 + 2 × teams> unless replace is set, and bracket sizes against groupSizes_(teams, brackets) — the same function the tab was built with, so the comparison is against the tab as built. Only then does writeDrawNames_ run, three setValues calls per category and no formatting: AD (names), AI (codes) and AB2:AB4 (seed, draw, import stamp).

AI is written in identity order, and only ever together with AD from the same file. That is safe because the rows arrive in the order a seeded, published draw produced — the randomising already happened, and shuffling again would break the bracket assignment. There is deliberately no code path that fills AI on its own.

The fingerprints in the file are not re-verified; they are what an outsider checks the draw with. AB2:AB4's provenance is the useful half — it ties the workbook to that file.

Progress and version

The sidebar's progress bar and timed log work as the dual meet generator's do, from the same copied log block. A standard run counts each category tab, Variables, Title, Reference for Players, MATCHES, SCHEDULE and the five readout tabs. The file carries STANDARD_TOURNAMENT_GENERATOR_VERSION, shown at the foot of the sidebar and in SAGE → Help, and bumped with every change to the file.

Verifying a change

node apps-script/verify-standard-tournament-generator.mjs

Runs the real generateEventTabs against apps-script/mock-apps-script.mjs, fed the two Pickle for Sight plan CSVs in apps-script/fixtures/, plus a reconstructed BKL Day 2 plan. It checks the spec's §16 figures, the ladder cells of spec §7.5 in the tab, every one of the 105 multi-bracket combinations §9 allows, the packer against the waves, and the packed schedule through sheets-sync.gs's real planMatchNumbers_. sheets-sync.gs is loaded into the same context first, so a name clash between the two files fails there. The mock does not evaluate formulas. Change a prototype shape in REBUILT_IN_PLACE_TABS and the fixture in the script has to change with it.

The import scenario covers the draw import end to end against files built byte-shaped like the tool's real exportAsText() output: parsing, the five separators, the written cells, and each refusal in turn. Because the mock evaluates no formulas it proves the values and the geometry, not that the tab recalculates — that the B column resolves names through the AE/AI link needs a real workbook.

Features: Standard Tournament Generator usage Spec: standard-tournament-master-spec.md