🚀 Creating a Quiz Form with Google Sheets and Apps Script

Step 1: Set Up Your Google Sheet

1. Open Google Sheets and create a new spreadsheet.
2. In the first column, add your quiz questions. In the second column, add the correct answers. Add possible answer choices in the next four columns (four choices per question).
3. Save your sheet and name it, e.g., ‘QuizSheet’.

Sample Table:

Step 2: Write the Google Apps Script

1. Open the Script Editor by going to Extensions -> Apps Script.
2. Clear the default myFunction and paste the provided script below.

Google Apps Script:

function doGet() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('QuizSheet'); // Replace 'QuizSheet' with your sheet name.
  var numberRows = sheet.getDataRange().getNumRows();

  // Read the sheet data into 3 arrays.
  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();

  // Shuffle the 4 choices horizontally.
  var myShuffled = myGuesses.map(shuffleEachRow);

  // Create the form as a quiz.
  var form = FormApp.create('Quiz Form'); // Set your form title.
  form.setIsQuiz(true);

  // Write out each multiple-choice question to the form.
  for(var i=0;i<numberRows;i++){
    // Add a new multiple-choice item for each question.
    var addItem = form.addMultipleChoiceItem();
    addItem.setTitle(myQuestions[i][0])
      .setPoints(1)
      .setChoices([
        addItem.createChoice(myShuffled[i][0], true),
        addItem.createChoice(myShuffled[i][1]),
        addItem.createChoice(myShuffled[i][2]),
        addItem.createChoice(myShuffled[i][3]),
      ]);
  }

  // Log the form URL.
  var formUrl = form.getPublishedUrl();
  Logger.log('Quiz created: ' + formUrl);

  // Store question data and form URL for later use.
  PropertiesService.getDocumentProperties().setProperty('formUrl', formUrl);

  // Return an HTML response to the user.
  return HtmlService.createHtmlOutput('Quiz created: ' + formUrl);
}

// This function, called by popForm, shuffles the 5 choices.
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;
}

Step 3: Run the Script

1. In the Script Editor, select the function doGet in the toolbar.
2. Click the play button (▶️) to execute the function.
3. If prompted, review and grant necessary permissions.

Step 4: Access Your Quiz Form

1. After running the script, check the logs for the generated form URL.
2. Open the URL to access your quiz form. Test it to ensure questions are shuffled.

Step 5: Embed the Quiz Form on Medium

1. Copy the form URL.
2. In your Medium article, add a new block, and select ‘Embed’.
3. Paste the form URL and hit ‘Embed’.
4. Preview or publish your article to share your quiz!

🎉 Congratulations!

You’ve created a quiz form using Google Sheets and Google Apps Script!

👏 Enjoyed the Quiz Tutorial? Clap to show your appreciation! 💬 Drop a comment with your thoughts or questions. 🚀 Follow for more exciting tutorials, and share this article to spread the knowledge! Let’s build and learn together! 🌟