Google Script - Create a Multiple Choice Quiz from Questions in a Spreadsheet

Recently I was asked if there was a way to create a multiple choice quiz from a spreadsheet which had the questions and answers already in it.  A colleague (thank you Andy Mann!) found a script created by Jason Jurotich which we were able to simplify and adapt for our own purposes with the REMC project 21Things4Students

Our modified script creates a Google Form multiple choice quiz from the active sheet (the one you are currently looking at) in a Google Spreadsheet.  If you have multiple sheets with questions (say 1 sheet for each chapter), you can create a new quiz just by running the script again on a different sheet! 

Below is how to add the script, what it does, and what you may want to modify -  

Adding the script

1. First, while in the spreadsheet you are going to use (whether the questions are set up or not), go to the Tools Menu - Script Editor



2. Delete out the code that is there. Copy and paste in the script found in the document Script - SS to Quiz.  Ge sure to copy and paste it right into the script editor - some of the characters may be changed by a word processor app/program and it might not work properly. 

3. Save it by clicking the save icon



Spreadsheet Setup

If you are using the script as is, you will need your data set up in the following way - 

  1. Set up the question in Column C
  2. Set up the answer choices in Columns D-G - each question should either have 2 or 4 possible choices
    1. Heading for Column D is A or True
    2. Heading for Column E is B or False
    3. Heading for Column F is C
    4. Heading for Column G is D
  3. Setup the answer in Column H 
    1. for 4 options, entries should be A (col D), B (col E), C (col F) or D (col G)
    2. for 2 options, entries should be T (col D) or F (col E)

This leaves columns A & B available for your own uses. 

What it does & what you may modify

Part 1 - Add a menu option

This section adds a menu to the spreadsheet called Make Quiz, which has the option to create a quiz for the current sheet. You can change the name of the menu (Make Quiz) or the menu item (Create Quiz for This Sheet) if you'd prefer something else.

//Add menu to document 

function onOpen() {

  SpreadsheetApp.getUi()

  .createMenu('Make Quiz')

  .addItem('Create Quiz for This Sheet', 'makeQuiz')

  .addToUi();

}

Part 2 - Set up the variables and grab data for the Quiz

First we are setting our variables by grabbing the data from the sheet we are looking at - 

//Create the Quiz in Google Forms

function makeQuiz() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  var numberRows = sheet.getDataRange().getNumRows();

Next we are having it pull arrays for the questions, possible answers and correct answer -

If you put your Questions, possible answers and correct answer in different columns than mine, you will need to adjust the 3, 4 and 8 accordingly so it grabs the information from the correct column.

  var myQuestions = sheet.getRange(1,3,numberRows,1).getValues();

  var myAnswers = sheet.getRange(1,8,numberRows,1).getValues();

  var myGuesses = sheet.getRange(1,4,numberRows,4).getValues();

Part 3 - Create the Quiz

The resulting form's "Quiz options" are different from a manually created quiz.  Be aware (and change manually) in the form if needed! 

This will create a quiz with the name My Quiz: (the sheet name) - so if you do not change the sheet name it will be My Quiz: Sheet1.  You can modify the My Quiz part in the single quotes if you want the name to start differently.  Also set the sheet name to something else PRIOR to creating your quiz if you want it to be something like Chapter1, or Polynomials, etc. 

  var form = FormApp.create('My Quiz: ' + sheet.getSheetName());

  form.setIsQuiz(true);

This part is the set up for creating questions with 4 options (answers entered as "A", "B", "C", "D" in the answer column) -

  for(var i=0;i<numberRows;i++){

    if (myAnswers[i][0] == "A") {

      var addItem = form.addMultipleChoiceItem();

      addItem.setTitle("(" + identifier[i][0] + ") " + myQuestions[i][0])

      .setPoints(1)

      .setChoices([

        addItem.createChoice(myGuesses[i][0],true),

        addItem.createChoice(myGuesses[i][1]),

        addItem.createChoice(myGuesses[i][2]),

        addItem.createChoice(myGuesses[i][3])

      ]);

    }

    else if (myAnswers[i][0] == "B") {

      var addItem = form.addMultipleChoiceItem();

      addItem.setTitle("(" + identifier[i][0] + ") " + myQuestions[i][0])

      .setPoints(1)

      .setChoices([

        addItem.createChoice(myGuesses[i][0]),

        addItem.createChoice(myGuesses[i][1],true),

        addItem.createChoice(myGuesses[i][2]),

        addItem.createChoice(myGuesses[i][3])

      ]);

    }

    else if (myAnswers[i][0] == "C") {

      var addItem = form.addMultipleChoiceItem();

      addItem.setTitle("(" + identifier[i][0] + ") " + myQuestions[i][0])

      .setPoints(1)

      .setChoices([

        addItem.createChoice(myGuesses[i][0]),

        addItem.createChoice(myGuesses[i][1]),

        addItem.createChoice(myGuesses[i][2],true),

        addItem.createChoice(myGuesses[i][3])

      ]);

    }

    else if (myAnswers[i][0] == "D") {

      var addItem = form.addMultipleChoiceItem();

      addItem.setTitle("(" + identifier[i][0] + ") " + myQuestions[i][0])

      .setPoints(1)

      .setChoices([

        addItem.createChoice(myGuesses[i][0]),

        addItem.createChoice(myGuesses[i][1]),

        addItem.createChoice(myGuesses[i][2]),

        addItem.createChoice(myGuesses[i][3],true)

      ]);

    }


This part is the set up for questions with 2 options (answers entered as "T", "F" in the answer column) -

    else if (myAnswers[i][0] == "T") {

      var addItem = form.addMultipleChoiceItem();

      addItem.setTitle("(" + identifier[i][0] + ") " + myQuestions[i][0])

      .setPoints(1)

      .setChoices([

        addItem.createChoice(myGuesses[i][0], true),

        addItem.createChoice(myGuesses[i][1])

      ]);

    }

    else if (myAnswers[i][0] == "F") {

      var addItem = form.addMultipleChoiceItem();

      addItem.setTitle("(" + identifier[i][0] + ") " + myQuestions[i][0])

      .setPoints(1)

      .setChoices([

        addItem.createChoice(myGuesses[i][0]),

        addItem.createChoice(myGuesses[i][1],true)

      ]);


After you have made any changes/modifications to the script, make sure to save it again.  

Run the Script

Click on the Run icon to run it, and the menu option should appear.  Whenever you want to create a quiz, click on the menu and choose to create your quiz.  

When the script finishes running, your quiz will be in your Google Drive - 

That's it!  Happy scripting :-)


Comments

Popular posts from this blog

Google Script - Create a Drop-Down List from Spreadsheet Column

Google Script - Create Calendar Events from Spreadsheet Data

Google Forms - How to Add a Video to Your Form

Google Calendar - How to Share Your Calendar Via Link

Google - Reset a Form for Reuse with the new School Year