Online exam maker using Apps Script

With all our classes moving from 99% face-to-face to 100% online, we had the situation where we needed to carry out all our exams online too. A relatively easy solution was to use Google Forms, which was easy for students to fill out and for us to receive the answers.

The only issue is the time to create these. So, I created a little exam maker program to speed up the form making part as much as possible without changing the format of the exams.

In this post, we’re going to use a Google Sheet to store the exam We’re going to use a Google Sheet to store the exam questions, options, and form parts. Plus, possibly the answers and points per question if we make this a quiz. Then run the program to create the form and automatically link it to a newly-created spreadsheet.

We’ll have the option of making this a quiz or not.

1. Non-quiz version: Creates an exam where the student fills in the Google Form and their answers appear in a Google Sheet.
2. Quiz version: Creates a quiz where the answers are also marked automatically and different points can be set per question. Note, this can only be used for answers with specific options and not text or paragraph questions where the answers are open.

Files

24-Online Exam Maker – The form questions and answers will be entered into this Google Sheet and then the form will be made from that.

At the top we state the form and sheet name. Plus, we choose if we want to make this a quiz or not.

In this example there are 3 sheets. A master blank sheet which is to be duplicated to then edit. The other two have been copied from the master, and one has been set up as a quiz and the other not.

Then going from left to right, we have a dropdown menu, which will state the type of question or section to add, the question, how many points per questions (if a quiz), the answer (if a quiz), is the question required, and the possible options (up to 10 options).

This code can produce title headers, text and paragraph questions, multiple-choice questions, or drop-down menu (list) questions.

It will then produce a form and a connected spreadsheet, which we’ll look at after the code below.

Script project

This just contains one script project.

1-examMakerQuiz: This contains the main function makeExam, plus the functions called from it, setQReqPts (which sets the question title, sets the required status, and any points allocated, and setOptions, which adds the options if it’s a multiple-choice or dropdown list question, and if it’s a quiz will set the answer key.

It will also contain the onOpen function which will create a menu.

The Code

1-examMakerQuiz

onOpen

We start off with the onOpen function to create the menu to run the script from.

1. function onOpen() {
2.  const ui = SpreadsheetApp.getUi();
3.	  ui.createMenu('ADMIN')
4.	    .addItem('Make exam', 'makeExam')
5.	    .addToUi();
6. }

L1-6: This will run when the sheet is opened. Open the function, get the spreadsheet UI, create a menu, and add the menu item and add it to the UI.

makeExam

8.	function makeExam() {
9.	  //Get data and create form
10.	  const ss = SpreadsheetApp.getActiveSpreadsheet(),
11.	  sh = ss.getActiveSheet();

L8: Open the makeExam function that will be run from the menu.

L10-11: Get the active spreadsheet and active sheet.

13.	  let data = sh.getDataRange().getValues();
14.	  const formName = data[0][1],
15.	        isQuiz = data[0][4],
16.	        formC = FormApp.create(formName),
17.	  form = FormApp.openById(formC.getId());
18.	data.splice(0, 2);

L13: Get the data on the sheet.

L14: Get the form name.

L15: Get a Y or N to see if this will be a quiz or not.

L16: Create the form giving it the title of the form name.

L17: Get its ID and open it so we can work with the form object.

L18: Remove the top 2 lines of the data, as these aren’t needed for the form questions and sections.

20.	//Set up as quiz or not
21.	if (isQuiz === "Y") { form.setIsQuiz(true); };

L21: Check to see if it’s a quiz or not, and if so, to set it as a quiz.

Next, we set up the general settings for the form.

23.	//Set general settings
24.	form.setConfirmationMessage('Your answers have been sent. Thank you.')
25.	    .setTitle(examName)
26.	    .setAllowResponseEdits(false)
27.	    .setShowLinkToRespondAgain(false);

L24: Set the confirmation message, i.e. the message the user will receive when they submit the form.

L25: Set the form title.

L26: Don’t allow the user to edit their response.

L27: Don’t allow the user to fill out the form again.

Now, we loop through the sheet data and create the appropriate question or section types, depending on what type of question or part it is.

29.	//Loop through and set up each question
30.	data.forEach((d) => {
31.	
32.	  //Set up variables
33.	  const qType = d[0], qTitle = d[1],
34.	  points = d[2], answer = d[3], required = d[4],
35.	  optionsAll = d.splice(5, 10);

L30: Loop through the data (here I’m using the new arrow function).

L33-34: Set up the variables from the current row of data.

L35: Get all the options from column F to O (including any blank cells).

Next, we check what question type the current question row is.

37.	 //Set section/question title and required status
38.	 if (qType === "SECTION") {
39.	   const section = form.addSectionHeaderItem();
40.	   section.setTitle(qTitle);41.	 }

L38: Here, we check to see if it’s a section.

L39: If so, we add the section to the form.

L40: Set the section title.

L41: Close the if statement.

In a similar way, we check to see if it’s a text, paragraph, multiple-choice, or dropdown list question.

43.	 else if (qType === "TEXT") {
44.	   const textItem = form.addTextItem();
45.	   setQReqPts(textItem, qTitle, required, points);
46.	 }
47.	
48.	 else if (qType === "PARAGRAPH") {
49.	   const paraItem = form.addParagraphTextItem();
50.	   setQReqPts(paraItem, qTitle, required, points);
51.	 }
52.	
53.	 else if (qType === "MC") {
54.	   const mcItem = form.addMultipleChoiceItem();
55.	   setQReqPts(mcItem, qTitle, required, points);
56.	   setOptions(optionsAll, isQuiz, answer, mcItem);
57.	 }
58.	
59.	 else if (qType === "DROPDOWN") {
60.	   const ddItem = form.addListItem();
61.	   setQReqPts(ddItem, qTitle, required, points);
62.	   setOptions(optionsAll, isQuiz, answer, ddItem);
63.	 }
64.	});

L43-44: If it’s not a section, we check if it’s a text question. If so, we add a text question to the form.

L45: We then call the setQReqPts function, which will set the question title, set the required status, and set the points for the question, if applicable. I’ve put this in a separate function, as we will do the same thing for the other question types. Then close the if statement.

L48-51: Similarly, we check if it’s a paragraph question and of so, add a paragraph question and set the question title, etc.

L53-57: We also check if it’s a multiple-choice question. Here, if it is, we also need to add the possible options, if it’s a quiz, and the answer. So, we call the setOptions function to do that.

L59-63: Finally, we check to see if it’s a dropdown list question. The same as the multiple-choice question, we need to add the options too.

L64: Close the forEach loop.

This carries on looping through until it gets to the end of the data. Then, I want to create a spreadsheet and add the form to it.

66.	  //Create New SS and add Form
67.	  const ssNew = SpreadsheetApp.create(formName);
68.	  form.setDestination(FormApp.DestinationType.SPREADSHEET, ssNew.getId());
69.	  ssNew.getSheets()[0].setName(formName);70.	}

L67: Create a new spreadsheet and give it the same form name.

L68: Add the form responses to the spreadsheet, by setting its destination as that new spreadsheet.

L69: The form responses sheet will always be added as the first sheet, so we can get the first sheet and change its name to the exam name.

L70: We close the function.

setQReqPts

Now, let’s look at the setQReqPts function, which sets the question title, sets the required status, and sets the points if there are any.

72.	//Set question, required status and points
73.	function setQReqPts(qItem, qTitle, required, points) {
74.	  qItem.setTitle(qTitle);
75.	  if (required === "Y") { qItem.setRequired(true); };
76.	  if (points !== "") { qItem.setPoints(points); };
77.	}

L73: Open the function.

L74: Set the title of the question.

L75: Check to see if the question is required. If it is, set the question as required.

L76: Check to see if points have been awarded to the question by seeing if the points cell isn’t blank. If so, set those points to the question.

L77: Close the function.

setOptions

The final function sets up the options for multiple-choice and dropdown list questions.

79.	//Set options
80.	function setOptions(optionsAll, isQuiz, answer, qItem) {
81.	  //Filter blank cells
82.	  const options = optionsAll.filter(op => op);

L80: Open the function.

L82: In the optionsAll array we have the values of all 10 options cells but of course we may have fewer options. So, we need to remove the empty spaces, if there are less than 10 options. So, we can filter those out by returning anything that isn’t a blank, and store the options that there are in the options array.

Finally, we need to add the possible options to the question. If it’s not a quiz, we just add the options, but if it is a quiz, we need to add the options and pass the answer key, which is whether it matches the answer true or false.

84. //Loop through options & add to question & if question option matches answer or not 
85.	  const ch = options.map((option) => {
86.	    if (isQuiz === "Y") {
87.	      const tf = (option === answer) ? true : false;
88.	      return qItem.createChoice(option, tf);
89.	    }
90.	    else { return qItem.createChoice(option); }
91.	  });
92.	  qItem.setChoices(ch);
93.	}

L85: First we set up a map method to loop through the options array. The returned values will be stored in the ch array.

L86: Next, we check if it’s a quiz.

L87: If it is, we check to see if the current option in the loop matches the answer. We use a ternary operator here, where if it does match it sets the variable tf as true, otherwise it sets it as false.

L88: It then creates the choice for the question and true or false value to the question and returns it to the ch array.

L89: Close the if statement.

L90: If it’s not a quiz, then run the else statement. This will create the option for the question and return that to the ch array.

L91: Close the map loop.

L92: Finally, we add the set of options to that question using the ch array.

L93: Close the function.

Run it once and authorise the code.

Then all the user (e.g. a teacher) needs to do is enter the following:

• question type
• question or section title
• points (if it’s a quiz)
• answer (if it’s a quiz – although sometimes I add them here anyway just for reference)
• if it’s required (leave it blank if not)
• options (if setting up multiple-choice or drop-down menu questions)

Here’s an example of a non-quiz form:

And here’s an example of one set up as a quiz:

They then run the program and it creates the form for them and connects it to the spreadsheet where the answers will appear. The form and connected spreadsheet will appear in My Drive.

The only difference between the non-quiz spreadsheet and the quiz one, is that the quiz one has an extra column for the score.

If you don’t use the quiz functionality, I usually submit the answers so they appear in the first submission row on the spreadsheet. Then the students’ submissions could be checked against that.

Opening the form, we can see it’s created all the questions in the order they were on the sheet.

All the options have been added too.

We can see it’s set certain questions as required.

In the non-quiz version, we can see there’s no answer key set up.

Whereas in the quiz version, we can see it has the answer key and the number of points that question carries.

We can also see that the points can be different per question depending on what we set in the Sheet.

In the Settings we can see the quiz version has indeed been set up as a quiz.

We can also see that the confirmation message has been set up and that we’ve turned off being able to send another response.

As I mentioned above, the quiz function works really well for multiple-choice and dropdown questions where you can set specific options and set the answer key. However, answers can’t be added to text or paragraph questions. That said, Google have recently released the Forms API, which does allow you to do that but working with that API is very different from the script above, but if it’s something you need, I would investigate it further.

Click on this LINK to make a copy of the online exam maker file.

This post is taken from my book “Google Apps Script Projects 2“, available on Amazon here.