Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Forms: Create a Quiz from a Question Bank

Google Forms: Create a Quiz from a Question Bank

Screen Shot 2014-12-12 at 7.51.32 AM

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.

Google Sheets

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.” This works 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.
two tabs

 

Look Up Formula

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.
Screen Shot 2014-12-12 at 7.29.24 AM

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.

Paste Formula

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.
Screen Shot 2014-12-12 at 7.37.46 AM

Generic Google Form

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.
Make 2 questions

Pre-Filled URL

In the Form edit screen use the “Responses” menu to choose “Get pre-filled URL.”
Get pre-filled URL Google Forms

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…
Screen Shot 2014-12-12 at 7.15.05 AM
Screen Shot 2014-12-12 at 7.15.14 AM

Press the submit button at the bottom of the screen. Copy the pre-filled URL that is displayed.
Copy Pre-Filled URL

Hyperlink in Spreadsheet

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

=hyperlink(“pre-filled URL”,”Click Here”)

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.
Screen Shot 2014-12-12 at 7.40.26 AM

“&&”

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 &&

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.
Screen Shot 2014-12-12 at 7.44.59 AM

Copy the Formula

Copy the hyperlink formula down the column. This will give you multiple URL’s that each link to a unique quiz.

Distributing the Quizzes

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.
Hide Columns

Sample

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.

 

 

 

12 thoughts on “Google Forms: Create a Quiz from a Question Bank

  1. Just install script called Flubaroo on your form. It grades it for you, gives you item analysis, and will email your students their scores if you want. It’s like magic.

  2. “A more complicated formula can solve this, but for the purposes of this tutorial we will not worry about that.” Are you able to help with the more complicated formula?

    1. I am available for consulting. “More complicated formulas” would be need to be customized to your particular situation. I would not be able to make a generic template.

  3. I have been researching a particular task for hours and have not made much headway. I am trying to create a self-grading Google Forms quiz that will take random questions from a master question bank. Is this possible? I am thinking that I can perhaps tie each answer to the specific question that was pulled, but I am not too sure how to do that. I have thus far followed your instructions and have been able to create a quiz that generates random questions, but the answers have to be graded manually (I am trying to provide instant feedback to the quiz taker). Would you perhaps be able to assist me?

    1. Do not try to squeeze a square peg into a round hole. Google Forms is awesome for Formative Assessment but it is not a quiz making tool. I use quia.com for giving tests and that is a great platform. My tutorial here is a hack and definitely has some fun applications. However, make your life easier and use a tool that is designed for this purpose.

  4. Great tutorial. I have been able to set up everything I need except for one thing. I have not been able to get the + sign to show up on my math questions on my Google Form quiz. Every other symbol formats correctly, but the + sign is just a blank. I have used every thing I know of – g(Math), copy equation editor from GDocs, Word, GDocs. I may be trying to do something that is not possible? Or is there another way? Thanks for all of your guidance!

    1. The plus sign translates to the spacebar.
      Try using these codes: http://www.obkb.com/dcljr/charstxt.html
      %2B is the code for the plus symbol.
      For example, when I need a hashtag symbol (#) I have to use %23 and the at symbol (@) is %40.
      So in the spreadsheet my twitter handle is %40alicekeeler instead of @alicekeeler. Then it translates correctly when populating the form.

  5. I don’t want them to get the same question more than once. Can you help me with the formula for avoiding duplicates? I have tried figuring it out myself, but it is not working.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.