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.