Search code examples
google-apps-scriptgoogle-sheetsgoogle-forms

Slow Google app script causing strange behavior in Google form

I recently got into Google app script and wrote a script that picks 20 random questions from a question bank in Google sheets and puts them into Google form for people to take as a quiz. The script is triggered to run on form submit. However, I've noticed the script seems to take a long time and if someone is accessing the form while it's running, they'd see less than 20 questions. While this is somewhat avoidable by refreshing the page until they see 20 questions, in some instances, even though there are 20 questions on the page, their score feedback will be xx/17, but in the Google sheet of responses, it shows their score as xx/20. Even more troubling is if person A is taking the quiz, and person B triggers the script, person A's responses get cleared and gets 0/20. Wondering if there anyone has attempted this before and has any tricks to prevent people from accessing the form until it's fully populated?

Thanks!

Here's my code:

function createQuiz() 
{
  let file = SpreadsheetApp.openById([sheetID]);
  let sheet = file.getSheetByName("Questions");
  
  // Instead of getting individual ranges, it is more efficient
  // to get all the data in one go, and then operate on the two
  // dimensional array in memory.
  let range = sheet.getDataRange();
  let values = range.getValues();
  question_total = sheet.getLastRow() - 1;

  var question_req = 20; 

  var index = questions(question_total,question_req);

  // Here I am using a existing form to test, but you can just
  // create a new one if you want.
  var form = FormApp.openById([formID]);
  var items = form.getItems();
  while(items.length > 0){
    form.deleteItem(items.pop());
  }
  
  form.setIsQuiz(true);

 
  for(r=0;r<question_req;r++)
  {
    let title = sheet.getRange(index[r]+1,1).getValue();
    let nchoices = sheet.getRange(index[r]+1,2).getValue();
    let choices = [sheet.getRange(index[r]+1,3).getValue(),sheet.getRange(index[r]+1,4).getValue(),sheet.getRange(index[r]+1,5).getValue(),sheet.getRange(index[r]+1,6).getValue()];
    let feedback = sheet.getRange(index[r]+1,7).getValue();

    // Calling function to create multiple choice question
    createShuffledChoices(form, title, nchoices, choices, feedback)
  }
}


function createShuffledChoices(form, title, nchoices, choices, feedback)
{

  let item = form.addMultipleChoiceItem();

  item.setTitle(title)
  item.setPoints(1)
  item.setRequired(true);

  // Setting up the array that will be passed into item.setChoices()
  let shuffledChoices = [];
  // Making sure that the correct answer is only marked once
  let correctAnswerChosen = false;

  // I found I had to shuffle the questions within the process of
  // creating choices as it made it easier to maintain the spreadsheet
  for (let i = nchoices; i != 0; i--) {
    let rand = Math.floor(Math.random() * (i - 1));
    // If the first answer is chosen, it is the correct one.
    if (rand == 0 && correctAnswerChosen == false) {
      // Combination of push and splice to remove from ordered array
      // to the shuffled one
      shuffledChoices.push(item.createChoice(choices.splice(rand, 1)[0], true));
      // Marking the correct answer as chosen,
      // so that no others are marked correct.
      correctAnswerChosen = true;
    } else {
      shuffledChoices.push(item.createChoice(choices.splice(rand, 1)[0]));
    }  
  }
  
  // Finally setting the choices.
  item.setChoices(shuffledChoices);

  // Creating the feedback
  let formFeedback = FormApp.createFeedback().setText(feedback).build();
  item.setFeedbackForIncorrect(formFeedback);
}

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


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

  • About tricks to prevent people from accessing the form until it's fully populated, in this case, how about temporarily closing the Google Form until your function createQuiz() is finished? When this is reflected in your script, how about the following modification?

    Modified script:

    Before you use this script, please set your Google Form ID.

    function sample() {
  // const form = FormApp.openById("###formId###"); // Please set your Google Form ID.

  // Close Google Form.
  form.setAcceptingResponses(false).setCustomClosedFormMessage("Please wait to finish a script.");

  createQuiz(); // This is your function.

  // Open Google Form.
  form.setAcceptingResponses(true);
}
    • Your function createQuiz() is run in this script.
    • The flow of this script is as follows.
      1. Run the function sample().
      2. Close your Google Form.
        • By this, the users cannot access your Google Form.
        • At that time, you can show a message like Please wait to finish a script.. Please modify this message to your actual situation.
      3. Run your function createQuiz().
      4. When your function createQuiz() is finished, your Google Form is opened.
        • By this, the users can access your Google Form.

    Note:

    • In my answer, it supposes that your function createQuiz() works fine. Please be careful about this.

    References: