Page 1 of 1

TJ Electronic Scoresheets

Posted: Wed Aug 26, 2020 5:34 pm
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!

Re: TJ Electronic Scoresheets

Posted: Tue Sep 15, 2020 3:47 pm
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.

Re: TJ Electronic Scoresheets

Posted: Tue Sep 15, 2020 10:52 pm
by biofanat
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.

Re: TJ Electronic Scoresheets

Posted: Mon Sep 28, 2020 4:54 pm
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.

Re: TJ Electronic Scoresheets

Posted: Tue Sep 29, 2020 7:40 pm
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 ... 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.")

Re: TJ Electronic Scoresheets

Posted: Thu Oct 01, 2020 12:26 pm
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.

Re: TJ Electronic Scoresheets

Posted: Sat Oct 17, 2020 9:15 pm
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.

Re: TJ Electronic Scoresheets

Posted: Wed Oct 28, 2020 9:36 am
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.