Using Google Sheets for a tournament? Use countformat.

The scariest thing of all is Protobowl

Using Google Sheets for a tournament? Use countformat.

Postby Cody » Thu Oct 30, 2014 4:34 pm

I've written a script for Google Sheets that "creates a 'Format Count' menu to the right of the Help Menu, presenting options for totaling the number of formatted cells in a named range." It's intended to streamline tracking of completion and writer question totals. It can be found here, along with associated information detailing the workings at the top. An 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 reading the script, this is the thread for them.
Cody Voight – CBGS ‘09, VCU ‘14. I write lots of science and am an electrical engineer.
VCU Tournament Director ‘13-pres. HSAPQ President ‘15-16.
Hero of Socialist Quizbowl Labor (NSC ‘14). “esteemed colleague” of Snap Wexley, ca. 2016. Stats Hero (Nats ‘16).
Quizbowl at VCU
User avatar
Cody
2008-09 Male Athlete of the Year
 
Posts: 2013
Joined: Sun Nov 15, 2009 12:57 am
Location: Richmond

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

Postby Cody » Thu Nov 20, 2014 9:31 pm

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 – CBGS ‘09, VCU ‘14. I write lots of science and am an electrical engineer.
VCU Tournament Director ‘13-pres. HSAPQ President ‘15-16.
Hero of Socialist Quizbowl Labor (NSC ‘14). “esteemed colleague” of Snap Wexley, ca. 2016. Stats Hero (Nats ‘16).
Quizbowl at VCU
User avatar
Cody
2008-09 Male Athlete of the Year
 
Posts: 2013
Joined: Sun Nov 15, 2009 12:57 am
Location: Richmond

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

Postby Cody » Wed Aug 12, 2015 1:36 pm

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 – CBGS ‘09, VCU ‘14. I write lots of science and am an electrical engineer.
VCU Tournament Director ‘13-pres. HSAPQ President ‘15-16.
Hero of Socialist Quizbowl Labor (NSC ‘14). “esteemed colleague” of Snap Wexley, ca. 2016. Stats Hero (Nats ‘16).
Quizbowl at VCU
User avatar
Cody
2008-09 Male Athlete of the Year
 
Posts: 2013
Joined: Sun Nov 15, 2009 12:57 am
Location: Richmond

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

Postby Cody » Mon Feb 20, 2017 4:23 pm

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 – CBGS ‘09, VCU ‘14. I write lots of science and am an electrical engineer.
VCU Tournament Director ‘13-pres. HSAPQ President ‘15-16.
Hero of Socialist Quizbowl Labor (NSC ‘14). “esteemed colleague” of Snap Wexley, ca. 2016. Stats Hero (Nats ‘16).
Quizbowl at VCU
User avatar
Cody
2008-09 Male Athlete of the Year
 
Posts: 2013
Joined: Sun Nov 15, 2009 12:57 am
Location: Richmond

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

Postby theMoMA » Mon Feb 20, 2017 9:13 pm

Confirming that this is indeed an awesome bit of qb tech.
Andrew Hart
Minnesota alum
User avatar
theMoMA
Forums Staff: Administrator
 
Posts: 5437
Joined: Mon Oct 23, 2006 2:00 am

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

Postby Auroni » Mon Feb 20, 2017 9:19 pm

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
Michigan '17
"I love Milf Money" - Will Nediger
User avatar
Auroni
Auron
 
Posts: 2904
Joined: Thu Nov 15, 2007 6:23 pm
Location: ann arbor


Return to Databases and Quizbowl Software

Who is online

Users browsing this forum: No registered users and 2 guests