This page describes the use of a Google Apps spreadsheet and script to automatically create a Google Apps form that is a multiple choice quiz. The intended use is for education. The quiz utilises a confidence grid and respondents are immediately emailed bespoke formative feedback on their answers. In addition, the spreadsheet records a customisable “score” for each respondent so that relative performance can be quickly (if simplistically) judged, if desired.
The spreadsheet and the script are offered freely for download and use. This work, by Stuart Billington, is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
A live example of the quiz is here. The rest of this page will probably make more sense if you have a go before continuing reading.
The respondent’s name and email address are collected and then the questions follow. The respondent answers via a confidence grid, so rather than simply selecting the answer(s) they think to be correct, they can indicate how sure they are of their answer, including an honest “I don’t know”. This allows the quiz to be more formative than summative.
Once the quiz is complete, two things happen. The respondent is emailed automatically-generated bespoke feedback for every question, based upon their own answers and the level of confidence they answered with. Also, behind the scenes, the respondent is assigned a score that is recorded on the spreadsheet. This is so that a teacher can quickly obtain an overview of their class.
Google Forms also allows for the respondent to see a summary of all of the previous answers, as graphs, which is nice. This is disabled in the live demo quiz above, however, as all answers are revealed — including the email addresses provided by the respondents. This isn’t an issue if used privately, however (for instance a teacher with their class).
Setting up the quiz
The spreadsheet used to configure the quiz looks like this (or see in full screen):
The number of questions is configurable (up to a maximum of 10) and so is the number of possible answers for the student to choose from (up to a maximum of 5). More than one answer can be correct. The wording of the “confidences” in the grid can also be configured.
The questions are simply typed into the relevant cells of the spreasheet, together with a hint/comment (if desired) and the answer options available for the respondent to choose from. More than one of these can be correct, indicated by the “Y” flag. Finally, each answer is accompanied by some formative feedback that will be given to the respondent if they answer incorrectly.
Creating the form
The spreadsheet comes with a script that creates the quiz as a Google Form, from the spreadsheet.
Once the script has run, a new form will be in your “My Drive”. (This, and the spreadsheet, can both be moved into their own folder together if you want.) The link to the form is given after the script has finished creating the quiz and this can be shared with anyone who needs to do the quiz, for instance emailed to all of the students in a class.
Note that the new form can be opened and customised; for instance, an image (e.g. a diagram, photograph, map, etc) can be added to any question. If the form is customised in any way, though, it is important that the questions or responses aren’t altered, as the feedback email is generated from the original spreadsheet parameters rather than the form and it may not make sense if it doesn’t match the quiz.
A new tab, called “Responses” will also be created on the spreadsheet. This is (a copy of) all of the responses given to the quiz, but may not be very useful as it is perhaps too much detail. More useful will probably be the summary:
This will allow the strengths and weaknesses of the class to be quickly identified, which can inform future lesson planning.
Once the respondent has completed the quiz, they will receive a bespoke email containing formative feedback on every one of their answers. For example:
At the same time, the third sheet of the spreadsheet records a blunt score for each respondent for the teacher to make use of:
Be default, the scores run from +2 to -2 for each answer (ie +100 to -100 for a 10-question, 5-option quiz), but this is configurable by changing the value of correct and incorrect answers at the top of the setup spreadsheet.
The template spreadsheet (which has the script packaged with it) is just a Google Spreadsheet and is available here:
You will need to make a copy of this in your own Google Drive and then you will be able to edit your own copy. When you run the “Create your quiz” script for the first time Google will ask your permission to run it and you will need to agree.
You can take a look at the script by clicking on the “Tools” menu and then on “Script Editor…”. It’s not that complicated a script and if you know what you’re doing you’ll be able to customise it to alter the structure if you need to. However, this isn’t necessary in order to use it as it comes as the spreadsheet controls all of the parameters of the quiz.
I’d love to hear if you make use of this, as it is or if you have improve it all. (Or if there are any bugs!) Please leave comments below. Thank you!