Google Forms is a survey tool, not a quiz generator. Many teachers utilize Google Forms for formative assessment since they are easy to administrate and the data is easily accessible in a Google spreadsheet. Using the Add-On Flubaroo will automatically grade the multiple choice questions and provide the student with their score. Google Forms allows you to scramble the answer choices and the questions, but it does not allow you to pull questions from a question bank. This is a work around to allow you to create dynamic quizzes.
Go to Google Drive and create a new Google Spreadsheet. You will need 2 tabs. One tab to link to the quiz you create and one for the list of questions. Double click on the tabs to rename them. I named my tabs “quiz” and “questions.” Thisworks for free response questions only. On the “questions” tab you will need to have a column for the question number and for the question. Optionally you can include a column for the answer.
To keep this simple we will ask the spreadsheet to choose a random question from your list of questions for each question. This will allow for the same question to be asked twice. A more complicated formula can solve this, but for the purposes of this tutorial we will not worry about that.
On the “quiz” tab, create a column for the quiz hyperlink and a column for each question you are going to ask on your quiz. You will write a formula to look up a random question from your question list for each question.
In the blank cell under the first question column write the formula
=vlookup(randbetween(1,max(questions!$A:$
A)),questions!$A$2:$B,2,false)
| formula element | explanation |
| =vlookup | This looks up the random question from the table |
| randbetween( | This generates a random number between 2 values |
| 1,max(questions!$A:$A) | You may want to add questions to your question list. Rather than having to rewrite all of your formulas have the spreadsheet check for the largest number in your question list in column A. |
| questions!A$2:$B | The table of questions is on the “questions” tab and the range of values is from columns A and B. By omitting the number on column B the table values will be the entire column B. |
| 2 | This number tells the lookup which column to return. Since the questions are in the 2nd column we use the number 2. |
| false | This field is not necessary, however it tells the lookup to search the entire table for the lookup value. If you included the column header in your table range you would need this field. |
You want this exact formula in all of the cells that will be choosing a random question. To obtain multiple versions of the quiz you will want to repeat this for multiple rows.
Create a generic Google Form to use as the quiz. For the first few questions ask the students name and other student information you need. For each question create TWO paragraph text boxes.
In the Form edit screen use the “Responses” menu to choose “Get pre-filled URL.”
Choosing “Get pre-filled URL” will open up a screen that looks like the live form. This screen allows you to fill out the parts of the form you want to pre-fill. In the question boxes type in capital letters “QUESTIONONE”, “QUESTIONTWO”, “QUESTIONTHREE”, etc…
Press the submit button at the bottom of the screen. Copy the pre-filled URL that is displayed.
Back in the spreadsheet on the “quiz” tab you will want to modify the URL to grab the questions from the spreadsheet. In the hyperlink column next to the random quiz questions type the formula
Replace the pre-filled URL in the above formula with the URL you obtained from the Google Form. The URL does need to be in quotations. The “Click Here”, which also needs to be in quotations, can be any text you would like to be displayed in the spreadsheet.
The key to creating a pre-filled URL from a spreadsheet is quotation ampersand ampersand quotation. Go through the pre-filled URL and replace all of the “QUESTIONONE”, “QUESTIONTWO” etc… with “&&”
In between the ampersands you will want to write the name of the cell that contains that question. For example “&B2&” is where I will find question 1 and “&C2&” is where I will find question 2.
Copy the hyperlink formula down the column. This will give you multiple URL’s thateach link to a unique quiz.
To distribute the quizzes to the students you can create a QR code from each of the unique URL’s if the students have tablets. You can also HIDE the question tab and HIDE the columns that contain the random questions so that students only see the list of hyperlinks.
CLICK HERE to see a sample spreadsheet. Make a copy to use this as your template. Note: You will need to replace the pre-filled URL in my formulas with the pre-filled URL from your own Google Form.
