I’m sure many of you have wondered how to turn a Google Sheet into a Google Form configured as a quiz. I know I did and that along with a desire to learn the boundaries of ChatGPT/GPT-4 gave me the impetus to try implementing a Google Apps Script project that would do exactly that:
- Take a GSheet of questions and answers and create as many quizzes as required based on the number of available questions and how many questions are required per quiz
- shuffle the questions per quiz
- shuffle the answers per question
- display answers once the quiz was submitted for a grade
With the useful (and sometimes useless) help of ChatGPT I was able to get this fairly trivial example working.
When you run the addon a sidebar opens that asks for:
- the base name for the quiz (it appends a number to the name in case more than one quiz is generated)
- a short description that will appear in all the quizzes
- the number of questions per quiz (if you have 50 questions and you want 10 questions per quiz it will create 5 quizzes)
- the delimiter string for possible answers. The possible answers column needs a delimiter so all the answers can be placed in one field:
- for example: a delimiter of XX would take a possible answer string of 1XX2XX3XX4 and add them to the question as 1, 2, 3, and 4
- Shuffle questions within one quiz when it is taken again
- Shuffle answers within one question when the quiz is taken again
The format of the GSheet is as follows (all columns are text fields and must be in this order):
- a header row (doesn’t matter what the headers say)
- Question Text: The text of the question to be asked.
- Question Type: The type of the question (RADIO or CHECKBOX). At the moment I have only tested support for “RADIO” for single answers (multiple choice).
- Possible Answers: The possible answers for the question. Multiple answers should be separated by a predefined delimiter (for example, “XX”).
- Actual Answer(s): The correct answer(s) for the question. In the case of multiple answers, these should be separated by the same delimiter as the possible answers.
- IsRequired: Indicates whether the question is mandatory. This should be either “True” or “False”.
- Points: The number of points the question is worth in the quiz.
- When Right Feedback: The explanation or feedback provided when the question is answered right.
- When Wrong Feedback: The explanation or feedback provided when the question is answered wrong.
When the quiz or quizzes are generated they are moved into a single folder on your GDrive with an HTML page that simply has the URLs to the quiz that someone would take and submit for a score.
I might put this up in the Google Marketplace.
Remember, if you decide to copy-and-paste this into an Apps Script project, you will need a GCP project so that you can enable the various APIs needed to make this work.
The GitHub repo is named GSheet to GForm Quiz Generator. The code was written by ChatGPT/GPT-4 and me.
Code
If you understand how Apps Script works here is the code for Code.gs and appsscript.json:
appsscript.json
{
"timeZone": "America/New_York",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/forms",
"https://www.googleapis.com/auth/forms.body",
"https://www.googleapis.com/auth/spreadsheets.readonly",
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/script.container.ui",
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/drive"
],
"addOns": {
"common": {
"name": "GSheet to GForm Quiz Creator",
"logoUrl": "https://www.dropbox.com/s/xgkygjsqge8u9lw/gsheet-quiz-creator-24x23.png?dl=1",
"homepageTrigger": {
"runFunction": "onHomePage"
}
},
"sheets" : {}
}
}Code.gs
function onHomePage(e) {
return createHomePage();
}
function createHomePage(completionMessage) {
var section = CardService.newCardSection();
if (completionMessage) {
section.addWidget(
CardService.newTextParagraph()
.setText(completionMessage)
);
var returnButton = CardService.newTextButton()
.setText('Process another GSheet')
.setOnClickAction(
CardService.newAction()
.setFunctionName('onHomePage')
.setParameters({ 'completionMessage': '' })
);
section.addWidget(returnButton);
} else {
var createQuizButton = CardService.newTextButton()
.setText('Generate Quizzes')
.setOnClickAction(
CardService.newAction()
.setFunctionName('createQuizFromSheet')
);
var baseQuizNameInput = CardService.newTextInput()
.setFieldName('baseQuizName')
.setTitle('Quiz Name')
.setValue("Quiz");
var shortDescriptionInput = CardService.newTextInput()
.setFieldName('shortDescription')
.setTitle('Quiz Description');
var questionsPerQuizInput = CardService.newTextInput()
.setFieldName('questionsPerQuiz')
.setTitle('Questions per Quiz')
.setValue("5");
var answerDelimiterInput = CardService.newTextInput()
.setFieldName('answerDelimiter')
.setTitle('Multiple Choice Delimiter')
.setValue("XX");
var shuffleQuestionsCheckbox = CardService.newSelectionInput()
.setType(CardService.SelectionInputType.CHECK_BOX)
.addItem("Shuffle questions", "shuffle_questions", true)
.setFieldName("shuffleQuestions");
var shuffleAnswersCheckbox = CardService.newSelectionInput()
.setType(CardService.SelectionInputType.CHECK_BOX)
.addItem("Shuffle answers per question", "shuffle_answers", true)
.setFieldName("shuffleAnswers");
var createQuizButton = CardService.newTextButton()
.setText('Create Quiz')
.setOnClickAction(
CardService.newAction()
.setFunctionName('createQuizFromSheet')
);
var section = CardService.newCardSection()
.addWidget(baseQuizNameInput)
.addWidget(shortDescriptionInput)
.addWidget(questionsPerQuizInput)
.addWidget(answerDelimiterInput)
.addWidget(shuffleQuestionsCheckbox)
.addWidget(shuffleAnswersCheckbox)
.addWidget(createQuizButton);
}
var card = CardService.newCardBuilder()
.addSection(section)
.build();
return card;
}
// Main function to create a quiz from the sheet data
function createQuizFromSheet(e) {
var combinedHtmlContent = '<html><head></head><body><h1>Links to the ready-to-go quizzes</h1>';
var baseQuizName = e.formInput.baseQuizName;
var shortDescription = e.formInput.shortDescription;
var questionsPerQuiz = parseInt(e.formInput.questionsPerQuiz);
var answerDelimiter = e.formInput.answerDelimiter;
var shuffleQuestions = e.formInput.shuffleQuestions == "shuffle_questions";
var shuffleAnswers = e.formInput.shuffleAnswers == "shuffle_answers";
var folderName = baseQuizName;
var quizFolder = DriveApp.createFolder(folderName);
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = activeSpreadsheet.getActiveSheet();
var data = sheet.getDataRange().getValues();
var questionData = data.slice(1);
var numberOfQuizzes = Math.ceil(questionData.length / questionsPerQuiz);
for (var quizIndex = 0; quizIndex < numberOfQuizzes; quizIndex++) {
var quizName = baseQuizName + ' - Quiz ' + (quizIndex + 1);
// Create a new form with the quiz name
var newForm = FormApp.create(quizName);
newForm.setDescription(shortDescription);
newForm.setShuffleQuestions(shuffleQuestions);
var newFormId = newForm.getId();
// Set up the OAuth2 authentication
var oauthToken = ScriptApp.getOAuthToken();
// Set form description and configure it as a quiz
apiUrl = 'https://forms.googleapis.com/v1/forms/' + newFormId + ':batchUpdate';
options = {
'method': 'post',
'headers': {
'Authorization': 'Bearer ' + oauthToken
},
'contentType': 'application/json',
'payload': JSON.stringify({
'requests': [
{
'updateSettings': {
'settings': {
'quizSettings': {
'isQuiz': true
}
},
'updateMask': '*'
}
}
]
})
};
UrlFetchApp.fetch(apiUrl, options);
// Prepare items to create using the Google Forms API
var start = quizIndex * questionsPerQuiz;
var end = start + questionsPerQuiz;
var quizItems = questionData.slice(start, end);
var items = quizItems.map(function (row, index) {
var question = row[0];
var questionType = row[1];
var possibleAnswers = row[2].split(answerDelimiter);
var actualAnswer = row[3];
var isRequired = row[4];
var points = row[5];
var explanationWhenRight = row[6];
var explanationWhenWrong = row[7];
console.info('explanationWhenRight:', explanationWhenRight);
console.info('explanationWhenWrong:', explanationWhenWrong);
// Use the helper functions based on the question type
console.info('questionType:', questionType);
if (questionType === "RADIO") {
return createRadioItem(question, shuffleAnswers, possibleAnswers, actualAnswer, isRequired, points, index, explanationWhenRight, explanationWhenWrong);
} else if (questionType === "CHECKBOX") {
return createCheckboxItem(question, shuffleAnswers, possibleAnswers, actualAnswer, isRequired, points, index, answerDelimiter, explanationWhenRight, explanationWhenWrong);
}
});
try {
// Create items using the Google Forms API
options.payload = JSON.stringify({ requests: items });
options.muteHttpExceptions = true; // Add this line to mute exceptions
var res = UrlFetchApp.fetch(apiUrl, options);
if (res.getResponseCode() === 200) {
console.log(res.getContentText());
// Collect the published URL
var formUrl = newForm.getPublishedUrl();
// Shorten the URL
var shortUrl = newForm.shortenFormUrl(formUrl);
// Add the shortened URL to the combined HTML content
combinedHtmlContent += '<p><a href="' + shortUrl + '">' + baseQuizName + ' - Quiz ' + (quizIndex + 1) + '</a></p>';
try {
var formFile = DriveApp.getFileById(newFormId);
var parentFolder = formFile.getParents().next();
parentFolder.removeFile(formFile);
quizFolder.addFile(formFile);
console.log('Form moved to the folder:', newFormId);
} catch (error) {
console.error('An error occurred while moving the form:', error);
// Add retry logic here, if needed
}
} else {
console.error('Error:', res.getResponseCode(), res.getContentText());
// Handle the error based on the response code and content
}
} catch (error) {
console.error('An error occurred:', error);
// Handle other types of errors
}
}
// Save the combined HTML content to Google Drive
combinedHtmlContent += '</body></html>';
saveHtmlToDrive(combinedHtmlContent, quizFolder);
// Return the completion message as navigation action
var action = CardService.newAction()
.setFunctionName('onHomePage')
.setParameters({ 'completionMessage': 'Quiz generation is complete' });
var nav = CardService.newNavigation().pushCard(createHomePage('Quiz generation is complete'));
return CardService.newActionResponseBuilder()
.setNavigation(nav)
.build();
}
// Save HTML content to Google Drive
function saveHtmlToDrive(htmlContent, folder) {
var fileName = 'Published Quizzes.html';
var mimeType = 'text/html';
var file = folder.createFile(fileName, htmlContent, mimeType);
return file.getUrl();
}
function createRadioItem(question, shuffleAnswers, possibleAnswers, actualAnswer, isRequired, points, index, explanationWhenRight, explanationWhenWrong) {
console.info('Start: createRadioItem()');
return {
createItem: {
item: {
title: question,
questionItem: {
question: {
choiceQuestion: {
options: possibleAnswers.map(function (value) {
return { value: value };
}),
shuffle: shuffleAnswers,
type: 'RADIO'
},
grading: {
correctAnswers: {
answers: [{ value: actualAnswer }]
},
whenRight: {
text: explanationWhenRight
},
whenWrong: {
text: explanationWhenWrong
},
pointValue: points
}
}
}
},
location: { index }
}
};
}
function createCheckboxItem(question, shuffleAnswers, possibleAnswers, actualAnswers, isRequired, points, index, answerDelimiter, explanationWhenRight, explanationWhenWrong) {
console.info('Start: createCheckboxItem()');
var actualAnswersArray = actualAnswers.split(answerDelimiter);
return {
createItem: {
item: {
title: question,
questionItem: {
question: {
choiceQuestion: {
options: possibleAnswers.map(function (value) {
return { value: value };
}),
shuffle: shuffleAnswers,
type: 'CHECKBOX'
},
grading: {
correctAnswers: {
answers: [{ value: actualAnswer }]
},
whenRight: {
text: explanationWhenRight
},
whenWrong: {
text: explanationWhenWrong
},
pointValue: points
}
}
}
},
location: { index }
}
};
}
