TJ Electronic Scoresheets

Packet databases and other quizbowl sites, apps, or software should be discussed here.
Post Reply
m02
Kimahri
Posts: 6
Joined: Tue Aug 25, 2020 4:00 pm

TJ Electronic Scoresheets

Post by m02 »

Hello all,

With tournaments moving online, the TJ electronic scoresheet and “control sheet” are being released as an option for online scorekeeping and tournament directing. The scoresheets have been used standalone at TJ for the past few years and offer several helpful features including automatically preventing errors, format checks in case mods force an error, formula protections, live rosters with dropdown selections, etc. The scoresheets currently support 20 question rounds on ACF (15-point powers, negs) or PACE (20-point powers, no negs, bouncebacks) format.

The scoresheets can be used alone or in tandem with a separate control sheet for TDs. The control sheet offers automatic creation of scoresheets with a specified number of rounds (targeted towards sufficiently large tournaments where copying rounds and sheets is annoying) and SQBS files. Everything is done using vanilla Google Sheets/Scripts. The sheet is intended to give TDs a general overview of the tournament, easy modification to rosters, and provide live updates about the status of each room/round (e.g., the current TU being read, errors in the scoresheet, lodged protests). Data filters are included for easy sorting and filtering of rooms. Automatic SQBS file creation is quick and robust, giving options to specify the rounds and bracketing (e.g., prelims and playoffs) used. The resulting file will be automatically saved to Google Drive and a download link will appear.

Modifications can be made to accommodate slight changes in tournament formats, but this could potentially conflict with the control sheet functionality. The code in the script editor should be updated to be compatible with any modifications.

Both sheets are equipped with more detailed instructions. An example tournament including scoring and stats is available here.

The scoresheets are adapted from a JHU scoresheet and the control sheet was created independently by Josh Lian (TJ) and me. Let us know if you encounter any bugs or have any questions at [email protected]. Any feedback is appreciated!
Kevin Zhang
Longfellow MS '17
TJHSST ‘21
m02
Kimahri
Posts: 6
Joined: Tue Aug 25, 2020 4:00 pm

Re: TJ Electronic Scoresheets

Post by m02 »

I am proud to have TJ sheets be employed at the AQBL-hosted September ERIS mirror and thankful for all the comments/suggestions from the TDs and mods. The sheets have been updated accordingly to include more robust features including forfeiting teams, live team scores, and user-specified point formats.
Kevin Zhang
Longfellow MS '17
TJHSST ‘21
aqblsheetsmanager
Lulu
Posts: 32
Joined: Tue Jul 02, 2019 4:52 pm

Re: TJ Electronic Scoresheets

Post by aqblsheetsmanager »

As the primary statskeeper at the AQBL-hosted September ERIS mirror, I would like to thank Josh and Kevin a lot for their creation of this stats system. Every part of it, ranging from the control room, to the error-checking basic sheets, worked incredibly well, greatly minimizing the errors and increasing the efficiency in statskeeping. Additionally, I would like to give a special thank you to Kevin, since he was incredibly helpful and was always around to assist us with fixing random moderator issues which popped up in the sheets. Especially for larger tournaments, using the TJ sheets would be a great choice and will help make every aspect of TDing a lot simpler.
AQBL Statsmin
JA01
Wakka
Posts: 123
Joined: Sat Jul 29, 2017 7:40 pm
Location: NY

Re: TJ Electronic Scoresheets

Post by JA01 »

Stony Brook used these scoresheets for our latest tournament. They are very intuitive, especially for TDs. Occasionally we had some problems where the scoresheet would display VALUE in the total score columns. Later on in the tournament, we discovered that one of the teams had to be on the left side or the scoresheet wouldn't work.

Overall, these are some of the best scoresheets out there and I would recommend them to anyone.
Jade Anderson
Smithtown High School West President Class of ‘19
Stony Brook University President Class of ‘23
jekbradbury
Lulu
Posts: 23
Joined: Fri Jul 09, 2010 9:41 pm

Re: TJ Electronic Scoresheets

Post by jekbradbury »

Olivia asked me to look into why the scoresheet was failing at the Stony Brook tournament when Yale B was on one side (see the difference between ROUND 4 and REAL round 4 in https://docs.google.com/spreadsheets/d/ ... 1477353732). It looks like this was the result of a bug in the formulas.

The short version:

The logic for detecting a `FOR.ERR` (formatting error, or giving points to the wrong person) is off by one. The SUM columns (`K` and `U`) include an `IF` statement checking for a `FOR.ERR`, but where they reference e.g. `CU4` and `CV4` respectively they should instead be referencing `CT4` and `CU4`.

The longer version:

Column `CV` holds player names from the team on the right (Yale B) not seated in columns `N` through `R`, i.e., those either sitting out (Lucas, Aryan) or sitting in column `M` (Ben). Apparently, these are intended to be used by the player dropdown for column `M`.
Yale B uniquely played with two registered players sitting out, which meant that column `CV` had three names and spilled into `CV4`.
The code in the SUM columns that was intended to check the boolean flags indicating a `FOR.ERR` mistakenly accessed columns `CU` and `CV` instead of `CT` and `CU` (where the `FOR.ERR` flags live), so it saw a player's name (Lucas Miner) where it expected a boolean ("Function IF parameter 1 expects boolean values. But 'Lucas Miner' is a text and cannot be coerced to a boolean.")
James Bradbury, Stanford '16, TJHSST '12
m02
Kimahri
Posts: 6
Joined: Tue Aug 25, 2020 4:00 pm

Re: TJ Electronic Scoresheets

Post by m02 »

The logic for detecting a `FOR.ERR` (formatting error, or giving points to the wrong person) is off by one. The SUM columns (`K` and `U`) include an `IF` statement checking for a `FOR.ERR`, but where they reference e.g. `CU4` and `CV4` respectively they should instead be referencing `CT4` and `CU4`.
Thank you for bringing this to my attention. The column references for format errors were indeed shifted by one, and the template has now been fixed.
Kevin Zhang
Longfellow MS '17
TJHSST ‘21
whatamidoinghere
Wakka
Posts: 156
Joined: Wed Apr 25, 2018 10:07 pm

Re: TJ Electronic Scoresheets

Post by whatamidoinghere »

This isn't necessarily a complaint about the scoresheets, but one thing I and other moderators experienced while scorekeeping was some issue marking dead tossups. I know that Shawrisheets allows you to mark dead tossups on the sheet with a separate column, while on these sheets you have to mark dead tossups in a bonus column (which makes less sense). I know that the sheets are pretty complicated but if there is a way to change marking dead tossups to being in a separate category than in a bonus that would probably make the sheets even more intuitive.
m02
Kimahri
Posts: 6
Joined: Tue Aug 25, 2020 4:00 pm

Re: TJ Electronic Scoresheets

Post by m02 »

Update 10/28: The updated templates now support automated YellowFruit and category stats generation - example category stats files from TJNAT and the AQBL October TU-only event.
Kevin Zhang
Longfellow MS '17
TJHSST ‘21
jonsh
Lulu
Posts: 10
Joined: Sun Oct 14, 2018 5:40 pm

Re: TJ Electronic Scoresheets

Post by jonsh »

I used these scoresheets for IO Saturnalia at MIT today and they worked really well! I will definitely use them again. I love how easy they make it for the TD to keep up with what's going on.

One thing of note is that the SQBS export only seems to work for the Windows version of SQBS, not the Mac version. This was easily resolved by asking another staffer with a Windows computer to help me with the stats uploading, but it was tricky to figure out that that was the needed fix, so I wanted to make a note of it for others.
Shuli Jones
UTS '18
MIT '22
adugas
Kimahri
Posts: 2
Joined: Fri Sep 30, 2016 8:00 am
Location: Louisiana Quiz Bowl Association
Contact:

Re: TJ Electronic Scoresheets

Post by adugas »

Hello! I used the scoresheets for my tournament yesterday, and they worked really well. I just have two issues that I can't figure out:
1) One of my scorekeepers in the first round accidentally deleted formulas causing a "#REF" error for the first round in that room. Fortunately, the reader remembered the stats and created a corrected page so I can input it manually. But how do I fix that error?

2) When trying to export the files as an SQBS file, I generate a 403 error from Google. It was power-matched, so there was no need to bracket. Is it because of that original error?

Thanks for the help! I would love to use it again if I can just figure out these kinks.
Ashley Dugas
President of the Louisiana Quiz Bowl Association (LQBA) 2016
Math Teacher at the Episcopal School of Acadiana, 2009
Coach at Episcopal School of Acadiana, 2009
m02
Kimahri
Posts: 6
Joined: Tue Aug 25, 2020 4:00 pm

Re: TJ Electronic Scoresheets

Post by m02 »

One thing of note is that the SQBS export only seems to work for the Windows version of SQBS, not the Mac version. This was easily resolved by asking another staffer with a Windows computer to help me with the stats uploading, but it was tricky to figure out that that was the needed fix, so I wanted to make a note of it for others.
So I was finally able to get my hands on a Mac and take a look into this. It seems SQBS handles line breaks differently on the two operating systems, which is what caused the issue. The sheets have been updated and the issue should now be resolved.
Kevin Zhang
Longfellow MS '17
TJHSST ‘21
User avatar
Fado Alexandrino
Yuna
Posts: 822
Joined: Sat Jun 12, 2010 8:46 pm
Location: Farhaven, Ontario

Re: TJ Electronic Scoresheets

Post by Fado Alexandrino »

Hey, quick question:

Does the TJ sheets download automatically tag carryover games (i.e. when you go from 2 groups of 8 into top/bottom 4, "playoffs" will be rounds 8-11 plus assorted games from rounds 1-7)? Or do they have to manually be dealt with in SQBS/Yellowfruit?

Thanks,
Joe Su
Lisgar 2012, McGill 2015, McGill 2019, Queen's 2020, OCT

Writer and Editor, Reach for the Top
LearnedLeague LL91 Midseason Classic Rundle D Nebula Champion
PSwann
Kimahri
Posts: 2
Joined: Mon Oct 07, 2019 4:46 pm

Re: TJ Electronic Scoresheets

Post by PSwann »

Thanks for making these great scoresheets! We are going to try to use them for our upcoming tournaments (Centennial HS in Maryland) but we are trying them out in our practices right now.

QUESTION:
Is there any way to have 5 or 6 players on a team who are all answering questions (i.e., they are not subs)? I keep getting a SUB.ERR when I try to do this. I know this is made for tournaments and you can only have 4 players on a team at one time for a tournament. But we are trying to use these sheets for practices right now and we don't limit the number of players on a team for practice.

Thanks so much!
Paula Swann
Centennial High School
Ellicott City, MD
Coach's Assistant
User avatar
CPiGuy
Yuna
Posts: 992
Joined: Wed Nov 16, 2016 8:19 pm
Location: Ames, Iowa

Re: TJ Electronic Scoresheets

Post by CPiGuy »

An extremely minor issue: TJ Sheets does not currently export overtime games correctly to Yellowfruit.

To be more specific, if it helps fix the issue, here are the three problems I saw:

-- Yellowfruit has a specific field per team for overtime tossups. TJ Sheets exports nothing here, rather than the results of the tiebreaking question (s).

-- Player stats are correctly exported, except TUH, which remains at 20.

-- The exception to this is when a player is substituted after TU20 and before overtime. In this case, the player joining is correctly listed with one TUH and the player leaving is incorrectly listed with only 19 TUH.
Conor Thompson (he/him)
Bangor High School '16
University of Michigan '20
Iowa State University '25
jonah
Auron
Posts: 2364
Joined: Thu Jul 20, 2006 5:51 pm
Location: Chicago

Re: TJ Electronic Scoresheets

Post by jonah »

CPiGuy wrote: Sat Feb 12, 2022 2:47 pmAn extremely minor issue: TJ Sheets does not currently export overtime games correctly to Yellowfruit.
I haven't used TJ Sheets, and I don't know if the thing I'm about to mention is exactly relevant, but my impression is that YellowFruit is under active development and not making guarantees about compatibility/versioning of its proprietary data format, so it's probably best not to use that format for data interchange. It would be better to use QBJ (which is designed for interchange and makes guarantees about compatibility/versioning) or, in a pinch, SQBS (since SQBS is clearly not being worked on so compatibility won't break, though even when it was being worked on, Chris Sewell apparently cared a lot about compatibility) (also, this only works if the tournament data are compatible with the limitations of SQBS, e.g., no more than eight players active on a team in a game).
Jonah Greenthal
National Academic Quiz Tournaments
nicole.
Wakka
Posts: 196
Joined: Sat May 04, 2019 11:16 am

Re: TJ Electronic Scoresheets

Post by nicole. »

jonah wrote: Sat Feb 12, 2022 3:10 pm
CPiGuy wrote: Sat Feb 12, 2022 2:47 pmAn extremely minor issue: TJ Sheets does not currently export overtime games correctly to Yellowfruit.
I haven't used TJ Sheets, and I don't know if the thing I'm about to mention is exactly relevant, but my impression is that YellowFruit is under active development and not making guarantees about compatibility/versioning of its proprietary data format, so it's probably best not to use that format for data interchange. It would be better to use QBJ (which is designed for interchange and makes guarantees about compatibility/versioning) or, in a pinch, SQBS (since SQBS is clearly not being worked on so compatibility won't break, though even when it was being worked on, Chris Sewell apparently cared a lot about compatibility) (also, this only works if the tournament data are compatible with the limitations of SQBS, e.g., no more than eight players active on a team in a game).
TJSheets only allows 6 active players per game so that SQBS limitation never comes up. SQBS writing almost always works from TJSheets, Yellowfruit is hit and miss depending on the file.
Nicole (Nic) Pruitt (she/her/they/them)

Founder of the Maine Quiz Bowl Alliance (MEQBA)
Bates College '25
Ridgewood '21
Director of Outreach, American Quizbowl League, and Long Island Quiz Bowl Alliance
User avatar
Ndg
Rikku
Posts: 380
Joined: Tue Oct 30, 2012 3:24 pm
Location: Wisconsin

Re: TJ Electronic Scoresheets

Post by Ndg »

jonah wrote: Sat Feb 12, 2022 3:10 pm
CPiGuy wrote: Sat Feb 12, 2022 2:47 pmAn extremely minor issue: TJ Sheets does not currently export overtime games correctly to Yellowfruit.
I haven't used TJ Sheets, and I don't know if the thing I'm about to mention is exactly relevant, but my impression is that YellowFruit is under active development and not making guarantees about compatibility/versioning of its proprietary data format, so it's probably best not to use that format for data interchange. It would be better to use QBJ (which is designed for interchange and makes guarantees about compatibility/versioning) or, in a pinch, SQBS (since SQBS is clearly not being worked on so compatibility won't break, though even when it was being worked on, Chris Sewell apparently cared a lot about compatibility) (also, this only works if the tournament data are compatible with the limitations of SQBS, e.g., no more than eight players active on a team in a game).
I'm flattered that "nothing in over a year" counts as "active development" ;)

In general, this is also my position, although I realize it's dumb to say that given how I've been dragging my feet on actually implementing the functionality to use qbj files.

I'm planning to release a version of YF next week that can import individual games as qbj files (1 game per file). Due to how flexible the qbj schema is, importing an entire tournament is not the easiest lift, so I would encourage anyone looking for more functionality to post over in that thread -- how much time I devote to this will depend on how much demand I see for it.
Andrew Nadig

Mannhiem Mannheim Manheim Township, 2005-11
Carnegie Mellon University, 2011-15
Post Reply