"This operation is not supported" error when creating a multiple-choice question in Google Forms via Apps Script after randomly selecting rows from a Sheet
פדר למהדרין
I'm trying to automate the creation of Google Forms from data in a Google Sheet using Google Apps Script. The script is intended to randomly select a number of questions from the sheet and add them to the form as multiple-choice questions.
The issue I'm encountering is that when the script attempts to create the Choice objects (answers) for the randomly selected questions, it fails with the error: "Exception: This operation is not supported". This error occurs on the line where the createChoice() method of the MultipleChoiceItem object is called.
The code previously worked when I created a form with specific questions from the sheet (not randomly selected). The problem only arises when I try to pick questions randomly. I've tried various approaches to creating the Choice objects (including using map, for loops, cleaning data) but the issue persists.
My current code is attached below
function createRandomQuizWithIdQuestion(numberOfQuestions) {
// 1. Get the spreadsheet and data - using a specific ID
var spreadsheetId = '1cU2KkN8dtrxRr_Og-Zxc-Di53Ls95hW_unf0gzih3og';
var ss = SpreadsheetApp.openById(spreadsheetId);
var quizSheet = ss.getSheetByName('1');
Logger.log("Value of quizSheet inside the function: " + quizSheet);
if (!quizSheet) {
Logger.log("Error: Could not find the sheet named '1'!");
return;
}
var data = quizSheet.getDataRange().getValues();
// Remove the header row if it exists
var questionsData = data.slice(1);
var totalQuestions = questionsData.length;
Logger.log("Total questions in the quiz sheet: " + totalQuestions);
// 2. Select the desired number of questions randomly
var numberOfQuestionsToSelect = Math.min(numberOfQuestions, totalQuestions);
var selectedIndices = [];
while (selectedIndices.length < numberOfQuestionsToSelect) {
var randomIndex = Math.floor(Math.random() * totalQuestions);
if (selectedIndices.indexOf(randomIndex) === -1) {
selectedIndices.push(randomIndex);
}
}
Logger.log("Indices of selected questions: " + selectedIndices);
// 3. Create a new form and set it as a quiz
var formTitle = "Random Quiz - " + numberOfQuestions + " Questions (New Sheet - Attempt to Fix)";
var form = FormApp.create(formTitle);
form.setIsQuiz(true);
form.collectFormResponses = true; // Enable collecting email addresses
form.setRequireLogin(false); // Allow users to answer without logging in
// Add a first question to identify the user (short text)
form.addTextItem()
.setTitle("If you wish to participate in the draw, please enter your username/email address (optional):")
.setRequired(false);
Logger.log("New form created with title: " + formTitle + ", ID: " + form.getId());
// 4. Loop through the selected questions and add them to the form
for (var i = 0; i < selectedIndices.length; i++) {
var rowIndex = selectedIndices[i];
try {
var questionText = String(questionsData[rowIndex][2]).trim(); // Question in column C (index 2)
var correctAnswerText = String(questionsData[rowIndex][3]).trim(); // Correct answer in column D (index 3)
var incorrectAnswers = [String(questionsData[rowIndex][4]).trim(), String(questionsData[rowIndex][5]).trim()]; // Incorrect answers in columns E and F (indices 4 and 5)
var allOptions = [...incorrectAnswers, correctAnswerText];
var questionItem = form.addMultipleChoiceItem()
.setTitle(questionText)
.setRequired(true);
// Creating choices without shuffling - keeping the order from the sheet
var choices = allOptions.map(function(option) {
var trimmedOption = option.trim();
return questionItem.createChoice(trimmedOption, trimmedOption === correctAnswerText.trim());
});
questionItem.setChoices(choices);
// Set the correct answer separately
var correctChoice = choices.find(function(choice) {
return choice.getValue() === correctAnswerText.trim();
});
if (correctChoice) {
questionItem.setCorrectAnswer(correctChoice);
}
questionItem.setPoints(1);
} catch (e) {
Logger.log("Error processing question at index " + rowIndex + ": " + e);
}
}
Logger.log('New random quiz created: ' + form.getTitle() + '. Link: ' + form.getPublishedUrl());
return form.getPublishedUrl(); // Returning the form link instead of the ID
}
// Function to shuffle an array (same function as before)
function shuffleArray(array) {
for (var i = array.length - 1; i > 0; i--) {
var j = Math.floor(Math.random() * (i + 1));
[array[i], array[j]] = [array[j], array[i]];
}
}
// Function to run loading the sheet and creating the quiz
function runCreateFirstQuiz() {
var quizUrl = createRandomQuizWithIdQuestion(3); // Requesting 3 random questions
Logger.log("Link to the created quiz: " + quizUrl);
}