Quiz Answer Scoring on
Google Sheets
Preface
I run a quiz night once in a while. This article describes answer sheets and an automated scoring mechanism I use. Using this system, answer marking is done instantly and accurately. And the scores can be shown immediately. It's implemented solely on Google Sheets. The organizer needs to use a desktop web browser for scoring operation. Participants can use either a web browser or Google Sheets mobile application to enter answers.
Quiz Night Format
Answer sheets and scoring depend on the format of a quiz night. Here's the format.
It's a team competition among a few (typically 6) teams. Each team consists of a few members.
There are 6 rounds of quizzes.
Each round has up to 10 questions.
All questions are multiple choices.
This is an example of a slide deck of a quiz round. As you see, an answer is to be chosen from A, B, C, ...
A quiz event will be run by the quiz mater (presenting quizzes) and the operator (dealing with scoring). The operations described in this article are for the operator. The files are supposed to be owned by the operator.
High-level Picture
Before getting into details, let me show the high-level picture of answering and scoring.
Answer Sheets
A Google Sheets file is made for each team to answer. The organizer shares the answer sheet of a team with its members so that they can edit it. An answer sheet file has Round 1 through 6 sheets corresponding to each round of quizzes. A participant enters answers at A1 through A10.
Scoring
The organizer uses the "Score" Google Sheets file for scoring. This file is not shared with participants. It has Round 1 through 6 sheets corresponding to each round of quizzes. The sheet of a round has answers of the round copied from the answer sheets of the teams.
The Score file has the Home sheet collecting scores of each round and then calculate total scores of the teams.
Before an Event
Now that you have some idea about how answers and scoring work, here's what to do before a quiz event.
Copy Files
The answer sheet and score files are in this Google Drive folder. It's accessible by anybody knowing the link. You need to copy "Score" and "Team A" at least. Please note that the copied files will be put in your My Drive because the source folder is not writable for you, The files Team A through Team F are almost identical. You can either copy other team files from the folder or you can make copies from your own copy of Team A. Either way, please have answer sheets ready.
Refer to Answer Sheets
As mentioned at the High-level Picture section, the Score file refers to answer sheet files. For that, you need to put URLs of answer sheet files on the D sheet of the Score file. To get the link to a file on Google Drive, 1) right-click the file and then select "Get link"; 2) click "Copy link".
After pasting URLs of the answer sheets, click the A2 cell and then click "Allow access". Then do the same thing for A3, A4, ... If team names are obtained are brought in, reference from Score to answer sheets is set.
Run Script and Unmark
The Score file has some scripts requiring explicit permission. Select "Round 1" sheet and then click Custom→Unmark.
Click "Continue".
Click the account you are using now.
Click "Allow".
Now that the Unmark script has a necessary permission, clicking Custom→Unmark causes unmarking.
Please see that the cells B2 through B11 above are blank. And the points of all teams at C13 through H13 are zero. Please Unmark Round 2 through 6 as well.
Put Answers
On the Score file on sheets Round 1 through 6, the cells B16 through B25 (marked by a red rectangle) have answers. An answer needs to be an uppercase letter (A through Z). If you have less than 10 questions in a round, answers of non-existing questions need to be left blank. Please delete the content of the cell rather than putting white spaces. The marking logic handles blanks in a special manner.
You can change the points per question at the E15 cell.
Enable Scripts on Answer Sheets
Not only the Score file, but also answer sheet files have scripts and you need to enable it on every answer sheet file. Please do the following on all answer sheet files.
Click Extensions→App Script.
Click "Run".
Click "Review permissions".
Click the account you are using now.
Click "Allow".
"Run" completed. Close the Apps Script tab on the web browser.
Now the answer sheet has the "Custom" menu.
Click Custom→Reset Ans on Round 1 through 6 sheets.
Share Answer Sheets
You need to share the answer sheet of a team with the team members. Though it's not secure, it's handy to make answer sheets editable by anybody as long as they know the URLs and inform the URL of a team's answer sheet only to the team members.
During the Event
At the Beginning
Open all answer sheet files and the Score file on a web browser. When you open an answer sheet file, the "Custom" menu is not set automatically. You need to open Apps Script and "Run".
Close a Round
After all quizzes are presented in a round, the quiz master declare the closure of the round. Then the sheet of the round of all answer sheet need to be "Protected" so that the answers of the round cannot be changed.
While a sheet of a round is protected, the padlock icon is shown before the sheet name.
Mark a Round
After closing the round of all answer sheets, you do the marking operation. On the Score file on the sheet of a round, click Custom→Mark.
That causes B16 through B25 to be copied to B2 through B11. That causes the following.
The scores of the round are shown at C13 through H13.
Right answers get green background.
How Marking Works
Formulae and scripts used on the Score file are explained here.
Bringing Answers
For marking, answers need to be brought from answer sheets. The following screenshot how it's done.
At the cell C2, the following formula is there.
=importrange(D!B2,concatenate(sheetName(),"!A1:A10"))
On the D sheet, URLs of answer sheets are put. "D!B2" brings the URL of the answer sheet of the first team. "sheetName()" is a function defined in Apps Script. It returns the name of the current sheet. In this case, it's "Round 1".
D2 through H2 have the same formula as C2 but the first argument of importrange() is "D!B3", "D!B4", ... instead of "D1B2".
Calculate Points
The points gained in the round are calculated as follows.
At the cell C13, the following formula is there.
=sumproduct(--($B2:$B11=C2:C11),arrayformula(if(len($B2:$B11)>0,1,0)))*$E$15
sumproduct() calculates the sum of the products of corresponding entries in 2 or more equally sized arrays.
$B2:$B11=C2:C11 yields an array of TRUE or FALSE. If the C? cell is equal to the corresponding B? cell, the array element corresponding to it is TRUE, otherwise FALSE. By applying the unary - (minus) operator twice, that array is converted into an array of 1 or 0.
arrayformula(if(len($B2:$B11)>0,1,0)) yields an array of 1 or 0. If the B? cell is a blank, the corresponding array element is 0, otherwise 1. Without this formula, at the beginning of an event, when no answers are entered (= all answers are blank) and B2 through B11 are blank, all teams have perfect score because B2 through B11 are equal to C2 through C11, D2 through D11, etc.
D13 through H13 have the same formula having $B2:$B11=D2:D11, $B2:$B11=E2:E11, etc. instead of $B2:$B11=C2:C11.
Change Background of Correct Answers
Conditional formatting feature is used to change background of correct answers. To see how it's set, select a cell in C2:H11, then click Format→Conditional formatting.
Then click the rule.
Then, you see the definition of it.
It's of the range C2:H11.
The format is applied if a custom formula returns TRUE. The formula is:
=if(len($B2)>0,$B2=C2,FALSE)
Again, blank in B2:B11 is taken into account. If B? is blank, the formula always yields FALSE for C? through H?. Only if B? is not blank, C? through H? are compared with B? and if they are the same, the formula yields TRUE.
The formatting style applied is background color green.
Apps Script
You can see Apps Script of the Score file by clicking Extension→Apps Script.
onOpen() function is executed when the file is opened. It adds "Mark" and "Unmark" to the Custom menu.
markAnswers() copies B16:B25 5o B2:B11.
unmarkAnswers() makes B2:B11 blank.
sheetName() returns the name of the current sheet.
How Answer Sheet Works
Data Validation
On an answer sheet, you select and answer rather than entering arbitrary text. You can see how the data validation is set by clicking Data→Data validation.
In the above, you see the sheet "D" is referred to. If you click the sheet list icon at the bottom left corner, the sheet "D" is listed grayed out, meaning it's hidden.
The "D" sheet has A through Z at the A1 through A26 cells.
Apps Script
On an answer sheet file, onLoad() function is not defined. Instead customMenu() function is defined as the first function. This is because onLoad() function is executed regardless of the user who open the file. The custom menu is useful only for the operator. As such, instead of showing the custom menu to all participants, the operator opens the Apps Script editor and run customMenu() function manually.