Apps Script Basics – Creating & updating a multiple question form

Following on from my previous post on creating and updating Google Forms, here we’ll look at adding and updating multiple questions to a form.

Creating multiple questions in a Google Form

Here in a Google Sheet, I have a set of reading comprehension questions, which I want to add to a Google Form. There are 5 questions and each question has 4 multiple-choice options. For simplicity, I’ve just put options a to d, but you could add different possible answers here, including full sentences. I’ve also included a row for the answers but in this example we’re just setting up the form and won’t be using that row.

From the Sheet, open the Script Editor.

The code we’re going to write is going to get the data from Sheet1, then store the questions and options in variables. Then it’s going to create a new form and add those questions and options to it.

1.	function createFormFromData() {
2.	  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
3.	//Get data, number of questions and options info
4.	  const data = sh.getDataRange().getValues(),
5.	  numOfOptions = data.length-3,
6.	  numOfQs = data[0].length;

Let’s look at it line by line.

Line 1: Set up a function called “createFormFromData()”.

Line 2: Get the sheet called “Sheet1” and store it in the variable sh.

Line 5: Get all the data on the sheet using the getDataRange() method and store it in data.

Here’s what the contents of data looks like.

Line 6: We need to know how many options there are. Getting the length of the array data, we will see there are 7 rows but we are only interested in the options, so we subtract 3 as there are 3 rows that are not needed (header, questions, and answer rows), leaving us with the number of options.

Line 7: Now, we need the number of questions. We can use the length method again, but this time we’re not interested in the rows, but the columns. So, we look at the first row of data in our array by stating ‘row 0’ and then get the length of it.

Then, store it in the numOfQs variable. This will find 6 columns, which is 1 more than the number of questions we have but we’ll deal with that later.

9.	  //Get questions
10.	  const questions = sh.getRange(2, 2, 1, numOfQs).getValues();

Line 10: Now, let’s get the questions and add all of them in one go to an array called questions. We do this by getting the range of cells from row 2, column 2, up until the last column, which is the number we just worked out in numOfQs. Remember to get the values in those cells.

Here, we can see the 5 questions stored in the questions array.

12.	  //Get options and store in an array
13.	  var allOptions = [];
14.	  for (q=2;q<=numOfQs;q++){
15.	    let options = sh.getRange(3, q, numOfOptions).getValues();
16.	    allOptions.push(options);
17.	  }

Now, we need to get the options for each question, and store them in an array, so that question 1’s options are stored together, then question 2’s, and so on.

Line 13: First let’s create an empty array, where we’re going to store the options and call it allOptions.

Line 14: We’re going to need to loop through the options, so set up a for loop and start at column 2 and end at the final column (numOfQs). So, basically, it will get the data from the 5 question columns.

Line 15: Each time around the loop, it will get the list of options, starting from row 3, column q, and the number of rows is dictated by the number of options we found out earlier, in numOfOptions variable. And finally, we’re only getting 1 column each time we go around.

Line 16: We then need to push that set of data into our empty array. To do so, we use the push method and pass the data stored in options into it.

This is what the data in allOptions looks like. We can see it’s got all 5 sets of options and grouped them by question (notice the double squared brackets).

19.	  //Create the form
20.	  const form = FormApp.create("New form");

Line 20: Then we create a new form.

Finally, we need to add the questions and the options to our newly-created form.

22.	  //Add questions and options to form  
23.	 for (qq=0;qq<numOfQs-1;qq++){
24.	   let formQ = form.addMultipleChoiceItem();
25.	   formQ.setTitle(questions[0][qq]);
26.	   formQ.setChoiceValues(allOptions[qq]);
27.	  }
28.	}

Line 23: We’re going to loop through the questions and allOptions arrays. We set up a for loop and as we’re looping through arrays, our starting point is 0 (i.e. the first position) and we want to loop through the number of questions there are, which is numOfQs-1 as originally, we included the first column in our sheet, which we need to ignore.

Line 24: During each loop, we first need to add a multiple-choice question, using the addMultipleChoiceItem() method to our form.

Line 25: Then, we need to set the question using the setTitle() method. We get the question from the questions array and as we only have 1 ‘row’ in the array, we use [0] first and then use the counter qq to move long the array each time.

Line 26: Finally, we need to add the options by using the setChoiceValues() method. We get the options from the allOptions array using the counter qq to get the appropriate set of options.

Run the script.

In your My Drive you’ll find the new form.

Opening it, we can see it’s been populated with our questions and options.

Updating multiple questions in a Google Form

This time let’s update an existing form. Note, this script is to update a form with the same number of questions and options. I use this type of code for exams that are in a set format so, the number of questions is the same but of course, the content is different, but the number of options is also the same.

Here on Sheet2 we have a similar set of data, but this time I’ve decided I want to rearrange the questions and I want to change the options from letters to numbers. You of course, could use this to use different questions and possible answers.

The first part of this code is similar to above but from line 49, we’re getting an existing form and updating it with the questions and options from the table above.

Let’s look at it line by line, although as the top part is basically the same as above, I will briefly go through those parts.

30.	function updateFormFromData() {
31.	  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

Lines 30-31: Set up the new function and get “Sheet2”.

33.	//Get data, number of questions and options info
34.	const data = sh.getDataRange().getValues(),
35.	    numOfOptions = data.length-3,
36.	    numOfQs = data[0].length;

Lines 33-36: Get the data on Sheet2, the number of questions and options that we’re going to work with.

38.	//Get questions
39.	const questions = sh.getRange(2, 2, 1, numOfQs).getValues();

Line 39: Get the questions and store them in the questions array.

41.	//Get options and store in an array
42.	var allOptions = [];
43.	  for (q=2;q<=numOfQs;q++){
44.	    let options = sh.getRange(3, q, numOfOptions).getValues();
45.	    allOptions.push(options);
46.	  }

Lines 42-46: Get the options for each question and store them in blocks in the allOptions variable.

48.	//Get existing form
49.	const form = FormApp.openById('FORM ID'),
50.	  allItems = form.getItems();

Line 49: This time, let’s get a form I’ve already created by its ID. (Insert your own form ID here).

Line 50: Then get the number of items (in this case just questions) already in the form, using getItems().

52.	//Add questions and options to form  
53.	for (qq=0;qq<numOfQs-1;qq++){
54.	  let formQ = allItems[qq].asMultipleChoiceItem();
55.	  formQ.setTitle(questions[0][qq]);
56.	  formQ.setChoiceValues(allOptions[qq]);
57.	  }
58.	}

Line 53: We’re going to loop through the data five times (i.e. the number of questions: numOfQs-1).

Line 54: We then need to set the existing questions as multiple-choice questions using this time asMultipleChoiceItem().

Line 55: Then, we set the questions, using setTitle() as before, looping through the questions array.

Line 56: Finally, set the choices using setChoiceValues() and looping through the allOptions array.

Run the script.

As you can see it’s updated the form with the new questions and numbered answers.

It’s very easy to make and update forms using data from Google Sheets and usually quicker and easier to do than manually making the questions directly on the forms.

You can make a copy of the Google Sheet here.

a