Using Google Sheets for a tournament? Use qams.

Dormant threads from the high school sections are preserved here.
Locked
User avatar
Cody
2008-09 Male Athlete of the Year
Posts: 2891
Joined: Sun Nov 15, 2009 12:57 am

Using Google Sheets for a tournament? Use qams.

Post by Cody »

I've written a script to track and manage set production in a Google Sheets answer spreadsheet. It's intended to streamline tracking of completion and writer question totals. The script can be found here.

You probably want the master copy of the spreadsheet. To use the master copy, simply go to File > Make a Copy and create a copy for your own use. There is an extensive README included in the file. You may have to close and re-open the file (and wait a few seconds) for the “qams” menu to pop-up. You’ll also have to go through a few dialogs to authorize the script since it isn’t verified by Google.

April 20, 2022 update:
I have updated the master copy of the spreadsheet to add packet template generation to the spreadsheet. It should work for non-standard distributions, including side events with more than 20 questions. Templates are based on satisfying user-generated constraints. See the sheet for details. It uses a min-conflicts algorithm with restart to avoid local minima.

Image

March 20, 2021 update:
I have updated the master copy of the spreadsheet to add Subcategory completion to the spreadsheet. The code has been reformatted and the script has been renamed to something a bit more obvious. I believe I've eliminated erroneous counting of empty cells for completion, and removed the simply tally features from the script.

Image

November 13, 2019 update:
I’m releasing a master copy of an answer spreadsheet on Google Sheets. There are various versions floating around since ~2015 because I never created a master copy available to the public (sorry! – don’t know why I failed to do that). This master copy supersedes any existing spreadsheets as a template for new tournaments, and integrates some clever work by JinAh Kim for deadlines and packet submission. (If you want help porting any features to an existing spreadsheet, which would mainly be the timeline, please contact me.)

To use the master copy, simply go to File > Make a Copy and create a copy for your own use. There is an extensive README included in the file. You may have to close and re-open the file (and wait a few seconds) for the “qams” menu to pop-up. You’ll also have to go through a few dialogs to authorize the script since it isn’t verified by Google.

The following text is 5 years old, and is essentially deprecated. I’ve kept it in case someone needs to use the simply tally options, or wants to include the script in their own answer sheet:
Cody wrote: Thu Oct 30, 2014 4:34 pmAn example answer sheet you can look at (make a copy if you want to test it), as well as an image of the named ranges used for the "Claims & Written" option. You can use the script in any spreadsheet created in the new version of Google Sheets by going to Tools > Script Editor; Create Blank Project; paste raw script. You will have to either run onOpen or close and reload the spreadsheet before the "Format Count" menu will pop up; you then have to give the script authorization to access Drive the first time you run it in a spreadsheet.

In brief, it works as follows:
  • You create a named range (default: "answers") that covers your entire answer space.
  • If all you want to do is count the number of bolded / italicized / struck-through / background colored cells, use one of the first four options and define a 1-cell named range (default: "tally") where the total number of written questions can be placed. These options are very primitive and you'll have to do any extensions (TU vs. B) yourself. See the Color: Hex Pairs sheet in the above spreadsheet for hex codes for background color purposes. There is no required formatting of your spreadsheet.
  • If you want to use "Claims & Written", read the stuff at the top of the script and format your spreadsheet as the linked spreadsheet is formatted. If there's enough interest in a different answer sheet layout (sp. bonuses under tossups instead of next to), it'd be fairly easy to implement. Claims are represented by a cell w/ a background color; bold indicates a written question.
If you have questions that can't be answered by the answer spreadsheet or reading the script, this is the thread for them.
Attachments
qams-packet-templates.png
(72.38 KiB) Not downloaded yet
qams.png
(113.68 KiB) Not downloaded yet
Last edited by Cody on Wed Apr 20, 2022 1:58 pm, edited 11 times in total.
Cody Voight, VCU ’14.
User avatar
Cody
2008-09 Male Athlete of the Year
Posts: 2891
Joined: Sun Nov 15, 2009 12:57 am

Re: Using Google Sheets for a tournament? Use countformat.

Post by Cody »

Since I had to implement this, if you have an answer sheet where the tossups & bonuses are in alternating rows instead of alternating columns, you can uncomment line 109 and comment line 106 in the current script on github.
Cody Voight, VCU ’14.
User avatar
Cody
2008-09 Male Athlete of the Year
Posts: 2891
Joined: Sun Nov 15, 2009 12:57 am

Re: Using Google Sheets for a tournament? Use countformat.

Post by Cody »

As the quizbowl production year starts in earnest, I'm bumping this. It's been used with great success (so far) on 2015 MUT, 2015 VCU Open, 2015 VCU Novice, and presumably others I don't know about. It's been proclaimed "invaluable" by Joelle Smart. Don't wait—bring your non-QEMS2 tournaments into the 21st century with countformat!
Cody Voight, VCU ’14.
User avatar
Cody
2008-09 Male Athlete of the Year
Posts: 2891
Joined: Sun Nov 15, 2009 12:57 am

Re: Using Google Sheets for a tournament? Use countformat.

Post by Cody »

Since it's been over a year, here's yet another bump. countformat was used with great success for ACF Regionals 2017 and is presumably being awesome right this very moment for the editors of ACF Nationals 2017.
Cody Voight, VCU ’14.
User avatar
theMoMA
Forums Staff: Administrator
Posts: 5993
Joined: Mon Oct 23, 2006 2:00 am

Re: Using Google Sheets for a tournament? Use countformat.

Post by theMoMA »

Confirming that this is indeed an awesome bit of qb tech.
Andrew Hart
Minnesota alum
User avatar
Auroni
Auron
Posts: 3145
Joined: Thu Nov 15, 2007 6:23 pm

Re: Using Google Sheets for a tournament? Use countformat.

Post by Auroni »

To add to the list of endorsements, a blatant ripoff of this produced by myself was used to great success at 2016 MLK, and is being used to write (This) Tournament is a Crime.
Auroni Gupta (she/her)
User avatar
Cody
2008-09 Male Athlete of the Year
Posts: 2891
Joined: Sun Nov 15, 2009 12:57 am

Re: Using Google Sheets for a tournament? Use countformat.

Post by Cody »

This was used by a few tournaments last year, so here's a reminder that it exists.
Cody Voight, VCU ’14.
User avatar
Cody
2008-09 Male Athlete of the Year
Posts: 2891
Joined: Sun Nov 15, 2009 12:57 am

Re: Using Google Sheets for a tournament? Use countformat.

Post by Cody »

I have updated the OP with a fully implemented master copy of an answer spreadsheet, which I didn't realize wasn't publicly available (sorry!). It's been used for most (all?) ACF tournaments in the past 3 years and is pretty great.
Cody Voight, VCU ’14.
User avatar
Cody
2008-09 Male Athlete of the Year
Posts: 2891
Joined: Sun Nov 15, 2009 12:57 am

Re: Using Google Sheets for a tournament? Use qams (previously countformat).

Post by Cody »

I have updated the OP with a new feature to track subcategory completion.
Cody Voight, VCU ’14.
User avatar
Cody
2008-09 Male Athlete of the Year
Posts: 2891
Joined: Sun Nov 15, 2009 12:57 am

Re: Using Google Sheets for a tournament? Use qams (previously countformat).

Post by Cody »

I have updated the OP with a new feature to generate packet templates that you can paste into a word processor.
Cody Voight, VCU ’14.
User avatar
meebles127
Tidus
Posts: 572
Joined: Mon Nov 20, 2017 9:27 am
Location: Charlottesville, Virginia

Re: Using Google Sheets for a tournament? Use qams (previously countformat).

Post by meebles127 »

Cody wrote: Wed Apr 20, 2022 1:44 pm I have updated the OP with a new feature to generate packet templates that you can paste into a word processor.
your work is amazing
Em Gunter
Club President, University of Virginia
Tournament Director, 2023 Chicago Open
Assistant Tournament Director, 2022 and 2023 ACF Nationals

Author of: My Guide to High School Outreach and So You Want to Buy a Buzzer System

"That's got to be one of the most useful skills anyone has ever gotten from quizbowl." -John Lawrence
Locked