Creating Google Forms Quiz FREE From Excel (Unlimited Questions)

Creating Google Forms Quiz FREE From Excel (Unlimited Questions)

1. Start a new spreadsheet by creating a blank Google sheet.

   

2. After starting a google sheet you need to add your all questions and respective correct answers with multiple options in the sheet as shown. As shown in the picture, you need to;
   • Add all of your questions in column A,
   • Add all the correct answers in column B
   • Add all other remaining 3 options in columns C, D, and E.

   

   (Script will randomly mix all the options while building quiz form)
3. Now to add a script, you need to go to Extensions >> Apps Script.

   

4. A new tab of Apps Script will open. Here you need to copy & paste the script code into the project. Code is given below. Name the code and save it.

   

Script code:
If you see any previous code in to that box, just remove it and add the following code:


function popForm() {
 
  var ss = SpreadsheetApp.getActive();
  // var sheet = ss.getActiveSheet();
  var sheet = ss.getSheetByName('Sheet1');
  var numberRows = sheet.getDataRange().getNumRows();
 
    
  var myQuestions = sheet.getRange(1,1,numberRows,1).getValues();
  var myAnswers = sheet.getRange(1,2,numberRows,1).getValues();
  var myGuesses = sheet.getRange(1,2,numberRows,4).getValues();
 
  
  var myShuffled = myGuesses.map(shuffleEachRow);
  Logger.log(myShuffled);
  Logger.log(myAnswers);
 
  
  var form = FormApp.create('Free Online Quiz By ProBlogBooster');
  form.setIsQuiz(true);
 
  
  for(var i=0;i<numberRows;i++){
    if (myShuffled[i][0] == myAnswers[i][0]) {
      var addItem = form.addMultipleChoiceItem();
      addItem.setTitle(myQuestions[i][0])
      .setPoints(1)
      .setRequired(true)
      .setChoices([
        addItem.createChoice(myShuffled[i][0],true),
        addItem.createChoice(myShuffled[i][1]),
        addItem.createChoice(myShuffled[i][2]),
        addItem.createChoice(myShuffled[i][3]),

      ]);
    }
    else if (myShuffled[i][1] == myAnswers[i][0]) {
      var addItem = form.addMultipleChoiceItem();
      addItem.setTitle(myQuestions[i][0])
      .setPoints(1)
      .setRequired(true)
      .setChoices([
        addItem.createChoice(myShuffled[i][0]),
        addItem.createChoice(myShuffled[i][1],true),
        addItem.createChoice(myShuffled[i][2]),
        addItem.createChoice(myShuffled[i][3]),


      ]);
    }
    else if (myShuffled[i][2] == myAnswers[i][0]) {
      var addItem = form.addMultipleChoiceItem();
      addItem.setTitle(myQuestions[i][0])
      .setPoints(1)
      .setRequired(true)
      .setChoices([
        addItem.createChoice(myShuffled[i][0]),
        addItem.createChoice(myShuffled[i][1]),
        addItem.createChoice(myShuffled[i][2],true),
        addItem.createChoice(myShuffled[i][3]),


      ]);
    }
    else if (myShuffled[i][3] == myAnswers[i][0]) {
      var addItem = form.addMultipleChoiceItem();
      addItem.setTitle(myQuestions[i][0])
      .setPoints(1)
      .setRequired(true)
      .setChoices([
        addItem.createChoice(myShuffled[i][0]),
        addItem.createChoice(myShuffled[i][1]),
        addItem.createChoice(myShuffled[i][2]),
        addItem.createChoice(myShuffled[i][3],true),


      ]);
    }
    else {
      var addItem = form.addMultipleChoiceItem();
      addItem.setTitle(myQuestions[i][0])
      .setPoints(1)
      .setRequired(true)
      .setChoices([
        addItem.createChoice(myShuffled[i][0]),
        addItem.createChoice(myShuffled[i][1]),
        addItem.createChoice(myShuffled[i][2]),
        addItem.createChoice(myShuffled[i][3]),


      ]);
    }
  }
}


function shuffleEachRow(array) {
  var i, j, temp;
  for (i = array.length - 1; i > 0; i--) {
    j = Math.floor(Math.random() * (i + 1));
    temp = array[i];
    array[i] = array[j];
    array[j] = temp;
  }
  return array;
}

5. Once you saved the script code it's time to execute the script. Click on RUN on the Apps script page as shown. Once you RUN the code, you will get the warning message as "Google hasn’t verified this app" or otherwise, google will ask "This project requires your permission to access your data." Here you need to click on "Advanced" and click on go to your code to allow permissions to proceed.
   
   
6. Now go to Google Forms. Your expected Google Forms Quiz is created. You can customize the title and setting as you want.