Creating a Multiple choice quiz from google forms

I am trying to create a multiple choice quiz,that takes questions from a question bank,using GOOGLE FORMS and the results are stored in a spreadsheet. I already know how to create a quiz and store the data in the spreadsheet,but I don't know how to use a question bank. Can anyone help me with this?

Thanks in advance.

回答1:

Recently Google announced that they added several methods to Google Apps Script Forms service to make it able to handle Google Forms quizzes programmatically.

Thanks to this, now it's possible to use Google Apps Script to create quizzes from a Google Sheets question bank. This it's practically the same than creating a Google form from a spreadsheet.

Related questions:

Creating a google form from a google spreadsheet - Beginner
Script to update Google Form from Spreadsheet

回答2:

I'm interested in this topic too, but I'm a complete newbie. Taking inspiration from existing bits of code I hashed up a simple script which, to my great surprise, seems to work. I'm describing it below, but keep in mind that this post is more a question than an answer. I'd like to have the opinion of more experienced programmers, because I'm not sure I'm doing everything all right.

So in my simple scheme the question bank looks like this (please make your own copy). It should be pretty self-explanatory, but

Column A: Id (not used so far, but perhaps useful in the future);

Column B: should contain the text of the question (so far only a placeholder like Q1, Q2 and so on)

Column C: number of multiple choices for each question

Column D: the correct choice (its order number in the following columns)

Column E and following: the text of the multiple choices, as many as in column C (again, only placeholders for the moment being. A3Q2 is answer 3 to question 2);

The script is as follows. I put a lot of comments in it. Basically it creates a form by the name "QBANK_1_form", containing a subset of the multiple-choice questions listed in the spreadsheet. The form is created in the folder specified by the variable destinationFolder. The script makes sure that there are no duplicate files (I got a lot of those while debugging).

Again, I'd really like some insight from more serious programmers. This is my very first attempt, and might be very clumsy. I'm even surprised it works.

   function myFunction() {
  // Id of the spreadsheet containing the question bank
  var questionBankId = "1QCO-W2PxR9sLf2HtXreaEslyGTBdjswTgqxWDFycgKc"; 
  // name of the output form
  var formName = "QBANK_1_form"; 
  // Id of the destination folder (not root to keep things tidy)
  var destinationFolder = "ID_OF_YOUR_FOLDER_HERE"; // <-- change accordingly
  // variable containg the total number of questions in the question bank (read from spreadsheet)
  var question_num;
  // number of questions in the output form (question_req <= question_num)
  var question_req = 3; // chosen programmatically for the moment
  // other variables
  var r;
  var c;
  var ans;
  var item;
  var content;
  var choices =[];
  var nchoices;
  var correct;
  var iscorrect;

  // ======================================================================================
  // Delete possible files by the same name from target folder, to avoid duplicates
  // ======================================================================================
  // get target folder by ID
  var myFolder = DriveApp.getFolderById('0Bw56O_ircsfpSWM2N2FQbm1fUWM');
  // check if other files with the chosen name exist in the folder, 
  var thisFile = myFolder.getFilesByName(formName);
  // if this is the case, iterate over them
  while (thisFile.hasNext()) {
    // get next file
    var eachFile = thisFile.next();
    // get its Id
    var idToDLET = DriveApp.getFileById(eachFile.getId());
    // delete the file
    DriveApp.getFolderById(destinationFolder).removeFile(idToDLET);
  } 
  // --------------------------------------------------------------------------------------
  // ======================================================================================    
  // CREATE AND POPULATE THE FORM    
  // ======================================================================================
  // --------------------------------------------------------------------------------------
  // create the form
  var form = FormApp.create(formName);
  // ======================================================================================    
  // move the form to the desired folder (just for the sake of "housekeeping")    
  // ======================================================================================
  // get Id of the file
  var formFile = DriveApp.getFileById( form.getId() );  
  // add the to the desired folder
  DriveApp.getFolderById(destinationFolder).addFile( formFile );
  // delete the file from the roor folder
  DriveApp.getRootFolder().removeFile(formFile);
  // ======================================================================================    
  // start populating the form
  // ======================================================================================
  // open spreadsheet containing the question bank
  var ss = SpreadsheetApp.openById(questionBankId);
  // get number of questions in the question bank (number of rows-1);  
  var sheet = ss.getSheets()[0];
  question_num = sheet.getLastRow() - 1;
  // if question_req > question_num then set question_req=question_num, and the following is just a reshuffling of
  // the questions in the question bank
  if(question_req>question_num) {
     question_req=question_num;
  }
  // get random indexes for the question subset
  var index = questions(question_num,question_req);

  // Make sure the form is a quiz.
  form.setIsQuiz(true);


  // iteration of reading spreadsheet and writing form accordingly
  for(r = 0; r<question_req  ; r++){
     // create new multiple choice question
     item = form.addMultipleChoiceItem();      
     // set value if correct
     item.setPoints(10);
     // get question text from question bank and write it in form
     content = sheet.getRange(index[r]+1,2).getValue();
     item.setTitle(content);
     // get number of choices from question bank
     nchoices = sheet.getRange(index[r]+1,3).getValue();
     // get position of correct choice from question bank
     correct = sheet.getRange(index[r]+1,4).getValue();
     // create choice array
     for(c = 1; c<nchoices+1;c++){
        // determine whether the choice is correct
        iscorrect = (c==correct);
        // read choice from question bank
        content = sheet.getRange(index[r]+1,c+4).getValue();
        // add choice to choice array
        choices[c-1] = item.createChoice(content,iscorrect);           
     }
     // set choices
     item.setChoices(choices);

  }
}
// ------------------------------------------------------------------------------
// this function extracts question_req unique integers between 1 and question_num
// ------------------------------------------------------------------------------
function questions(question_num,question_req){
  var index = [];
  var ilist = [];
  var i;
  var n;


  // create and populate index list
  for (i = 0; i < question_num; i++) {
      ilist[i]=i+1;
  }
  // create indexes of random questions
  i = 0;
  while(i<question_req){
      n = Math.floor(Math.random() * (question_num-1));
      if(ilist[n]==n+1){
         index[i]=n+1;
         ilist[n]=-1;
         i++;
      }
  }
  return index;
}