qams² answer spreadsheet for set production and packetization

[Cody]

qams was redesigned last year in order to keep up with modern set production practices. I am now releasing it to the public as qams². qams² is designed for sets with more question states and writers than qams, and colors the answer sheet based on question state rather than writer/editor. If your set is the product of few writers, you may (or may not) prefer qams.

The default question states are claimed, written, edited, rewrite, and final. The first three are based on writer tags while the latter two are based on cell formatting (underline and bold, respectively). These states can be renamed or even left unused to fit your particular production desires.

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. To authorize the script, you’ll need to select a function from the menu (it won’t execute), click continue, choose the Google account you’ll use the script with, and select “Allow”.

qams² includes all of the features from qams, including packet template generation. Subcategory completion is based on a formula rather than requiring a dedicated function. Packet template generation is set up for up to 30 questions and up to 30 packets. It should work for non-standard distributions, including side events, because templates are based on satisfying user-generated constraints. See the sheet for details.

If you have questions that can’t be answered by the answer spreadsheet or reading the script, this is the thread for them. Direct feature requests to [email protected].

Some screenshots:

[CPiGuy]

This is very cool.

you should prefer balance by half

A – balance by total questions / questions with identical category & subcategory ID
B – balance by half
C – balance by no criteria

What exactly is the difference between options A, B, and C here?

[Cody]

This is very cool.

you should prefer balance by half

A – balance by total questions / questions with identical category & subcategory ID
B – balance by half
C – balance by no criteria

What exactly is the difference between options A, B, and C here?

There are two levels of balancing, category and subcategory. Category balancing is specified by the first two characters of the alphanumeric code while subcategory balancing is specified by the full four characters.

"A" splits the packet into sections based on the total number of questions and ensures one question in each section.
"B" splits the packet in half and ensures an equal number of questions in each half (as near as possible).
"C" does not look for any balance.
(Subcategories are the same but use lowercase letters.)

More constraints may lead to statistical oddities (despite randomizing the starting order) or metagaming, so I recommend "B" for categories and "c" for subcategories. Additionally, the lookup for paste-able packets relies on 1:1 mapping of (full) code to name . Therefore, balancing subcategories requires you to use the same name for both subcategories to get paste-able packets.

Example 1: If you have 20-question packets, literature as category "1", and there are four questions, then balance code "A" will put one question each in 1-5, 6-10, 11-15, and 16-20 while "B" will put two questions each in 1-10 and 11-20.

Example 2: If you have 20-question packets, fine arts as category "4", and there are three questions, then balance code "A" will put one question each in 1-7, 8-13, and 14-20 (approximately) while "B" will put two questions in one of 1-10 and 11-20 and one question in the other. Additionally, if you have "visual" and "audio" fine arts with identical codes (e.g. 4B1b) with "other" fine arts with a different code (e.g. 4B2c) then you would get "visual" and "audio" fine arts in opposite halves while "other" fine arts can go anywhere. Note in this case, the paste-able packets will have duplicate category names because I've distinguished the two 4B1b as "visual" and "audio" instead of calling them both "Visual or Audio". You'd need to replace one at random in the final packet (e.g. "Visual" -> "Audio").

edit: added 20-question packet specification to the examples since the packet template generator does not assume 20-question packets and you can have as many questions as you want.

[Scone]

Hey, I'm a bit confused on how to edit this spreadsheet if you want to use a different distribution, say, if I wanted 22/22 packets with extra Trash questions. I get that I can add a row in Answers, but how do you make the formulas and script work?

[Cody]

Hey, I'm a bit confused on how to edit this spreadsheet if you want to use a different distribution, say, if I wanted 22/22 packets with extra Trash questions. I get that I can add a row in Answers, but how do you make the formulas and script work?

as long as you insert the row about the tiebreakers row, everything will work. if not, you may need to adjust the named range "answers" to ensure it covers the answer space. for the formulas on the 'Progress' sheet, you update the number of packets (Cell N1), number of questions per packet (Cell N2), and final deadline (Cell N4). if you're using the timeline, you update the dates and number of packets due as well.

[yeah viv talk nah]

You probably want the master copy of the spreadsheet.

qams² includes all of the features from qams, including packet template generation. Subcategory completion is based on a formula rather than requiring a dedicated function. Packet template generation is set up for up to 30 questions and up to 30 packets. It should work for non-standard distributions, including side events, because templates are based on satisfying user-generated constraints. See the sheet for details.

The qams² master sheet has now been updated to version 2.0.0. The main new features (alongside a few bug fixes) are scripts to automate (1) submission tracking for packet submission tournaments and (2) calculating character counts for each question in the set, allowing editors to set character-wise length caps instead of line-wise length caps.

The code for each of these utilities is open-source and available at this gist. I wrote these utilities during the production of 2025 ACF Regionals (packets from which are used for the demos below), based on code that Jordan Brownstein wrote for 2024 Chicago Open. I think the editing team would agree that it saved us a huge amount of time and effort for packet assembly and editing.

More information about each feature is available in the README of the master. The procedure for using qams² remains the same: create a copy of the master and allow each script some permissions to run.

Submission Tracking



When run, the tracker will automatically populate the cells below each header row with the type and answerline(s) for each question in the submitted packet. Editors can then use a dropdown in the leftward cell to indicate their preference in the cell: (Y) for definite usage, (M) for potential usage, (N) for rejection, and (C) for usage with conversion of type (e.g. tossup-to-bonus or vice versa). Running the tracker again will color-code all cells. Packets can then be assembled at-a-glance by fitting submissions together based on which of their questions are green (Y) / teal (C) cells.

Character Counting



The character counter will automatically calculate the total number of characters in the body of each question (all lines for tossups, lead-in + E + M + H for bonuses), excluding pronunciation guides and common meta-text like "Description acceptable". It displays the count data in two tables: one displaying mean and max counts per category and one displaying count per question. The editing team can set length caps on the mean and max length per category, and the cells are color-coded based on these length caps to alert editors about questions to trim.