Apps Script Basics – Adding different types of questions to a Form

In this post, we’re going to look at how we can add different types of questions to a Google Form from a Google Sheet. As an example, we’re going to create a questionnaire with 9 different question types.

In our sheet we have the questions and various options we’re going to use. Column B has the question types, which will tell our script which type of question to set up. Column C has the questions. Columns D to K, contain the various options for each question.

Overview of script

Our script will:

1. Create a form
2. Get the data from the sheet
3. Loop down each row checking the type of question
4. Run the appropriate function to make the corresponding question and options

Here, we’re also going to how we can use the forEach(), slice(), indexOf(), and filter() methods.

---

Creating a new Google Form

1.  //Create new form - Global variable so it can be seen by all functions
2.  const FORM = FormApp.create("Questionnaire");

Line 2: Create a new form using FormApp. I’ve put it outside the main function to create a global variable, so that the form is visible throughout the script and in the different functions. Here it’s stored in the variable FORM. In capital letters to show it’s a global variable.

---

Getting the data from the Sheet

4. function makeQuestionnaire() {
5. //Get data and last row from spreadsheet
6.   const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
7.   const data = sh.getDataRange().getValues();

Line 4: Set up the main function, makeQuestionnaire.

Line 6: Get the sheet called “Sheet1”, where the questions are stored.

Line 7: Get all the data on the sheet, using getDataRange() and getValues(), then store it in the variable, data.

Note, with this we’re only making one call to the Sheet as we’ll then be working with the data array, so this will make the code run much faster than getting the data bit by bit from the sheet.

---

Looping down the rows of questions

9.  //Loop through each question and check what question type it is
10.   data.forEach(checkQuestionType)
11. }

Line 10: Now, we need to loop through the questions. To do this, we can use the forEach() method, which loops through all the items in an array and run the function named in the brackets. So, here it will get the items in the data array (i.e. the rows on the sheet), then run the function called checkQuestionType, which will do just that, check the question type and set up the appropriate question.

Line 11: Close this function.

---

Checking the question type

Above we referred to a function to check the question type, so let’s set that up now.

13. //function to check question type
14. function checkQuestionType(rowData){
15.   let qType = rowData[1];
16.   if(qType === "text"){  
17.   makeTextQ(rowData);
18. }

Line 14: Set up the function and pass the data from the variable rowData into it. Note, the data in the data array is being sent row by row by the forEach loop.

Line 15: We’re going to check the question type across multiple of or else of statements, so let’s simplify it a little and call rowData[1] qType.

Line 16: Now, we check the type of question. The question will be in position 1 of the array data (i.e. column B). So, we check if qType equals “text”. Note, as we are using the forEach() method, we don’t need to state the row, as this is already known as it loops down the rows.

Line 17: If the question type is text, we run the function called makeTextQ() and pass the rowData variable to it.

Line 18: Close the function.

19.  else if (qType === "date") {
20.    makeDateQ(rowData);
21.  }
22.  else if (qType === "duration") {
23.    makeDurationQ(rowData);
24.  }
25.  else if (qType === "multiplec") {
26.    makeMultipleCQ(rowData);
27.  }
28.  else if (qType === "list") {
29.    makeListQ(rowData);
30.  }
31.  else if (qType === "scale") {
32.    makeScaleQ(rowData);
33.  }
34.  else if (qType === "checkbox") {
35.    makeCheckboxQ(rowData);
36.  }
37.  else if (qType === "grid") {
38.    makeGridQ(rowData);
39.  }
40.  else if (qType === "paragraph") {
41.    makeParagraphQ(rowData);
42.  }
43. }

Lines 19 to 43: We do the same for each of the question types. We check the question type then if it matches, we run the function associated with that question type.

---

Functions to make the questions

In the final part, we set up the functions for each of the question types. The basic format for most question types, is to add the question item, then set its title.

Making a text question

The only information we need for a text question is the question title, which is in the third column.

45. ////Functions to make different types of questions
46. //Make text question
47. function makeTextQ(rowData) {
48.  let text = rowData[2];
49.  let item = FORM.addTextItem();
50.  item.setTitle(text);
51. }

Line 47: Set up a function called makeTextQ() and pass the rowData variable to it.

Line 48: Get the question title, which is in position 2 of the rowData array (i.e. the third column).

Line 49: Get the form and add the text item to it. Store this in the item variable.

Line 50: Set the question title, using the information in the text variable.

Line 51: Close the function.

---

Making a date question

53. //Make date question
54. function makeDateQ(rowData) {
55.  let date = rowData[2];
56.  let item = FORM.addDateItem();
57.  item.setTitle(date);
58.  item.setRequired(true);
59. }

Lines 54-57: This is exactly the same as the function above to make a text question, except that we need to add a date item, so we use addDateItem() in line 56.

Line 58: I’ve added an extra line, showing how we can also set questions to be required to be filled out. This is simply done with the setRequired() method on the item and stating true in the brackets. This of course is optional.

---

Making a duration question

61. //make duration question
62. function makeDurationQ(rowData) {
63.  let duration = rowData[2];
64.  let item = FORM.addDurationItem();
65.  item.setTitle(duration);
66. }

Lines 62-66: Again adding a duration question is similar to adding a text question, you just need to use the addDurationItem() method.

---

Making a multiple-choice question

Adding a multiple-choice question is a bit more complex than adding just a text question, as we also have to add the possible options. As I’ve set up the sheet to allow the user to add however many options they want, we need to also to know how many options there are and to ignore any blank cells.

In this example, we have a question asking for the student’s “Level” and then 3 options, A1, A2, and B1. After the options there are blank cells, which we’ll need to ignore otherwise we’ll add blank options to our question on the form.

The first part of this function is similar to the text one, in that we’re going to get the question, add the item to the form and set the question title.

68. //make Multiple-Choice question
69. function makeMultipleCQ(rowData) {
70.  let mcQuestion = rowData[2];
71.  let item = FORM.addMultipleChoiceItem();
72.  item.setTitle(mcQuestion);

Line 69: Set up the function called makeMultipleCQ() and pass the rowData variable into it.

Line 70: Get the question from the rowData variable.

Line 71: Add the multiple-choice item to the form.

Line 72: Set the question title using the mcQuestion variable.

At the moment, the rowData variable contains the whole row of data, which may also include some blank cells at the end. In our example, this is the content of data for this particular row:

As we don’t want blank options on our form, we’re going to need to get rid of them. To do this, we’re going to use a combination of an array filter and function to look for spaces and if it finds any it filters them out. I’ve taken this piece of code from the StackOverflow site here, and it’s an example of where you can find useful pieces of code without having to try to invent it yourself. It’s also something that even if you don’t fully understand the details of it, you can see how it works and so use it as a complete block of code.

74.74.  //Getting options
75.75.  let trimmedData = rowData.filter((str) => {
76.76.    return /\S/.test(str);
77.77.  });

Line 75: We get the rowData array and use the filter method. Then we set up a function with the parameter called str. We’ll store the end result in the variable trimmedData.

Line 76: In our function, we’re going to test to see if there is a space in the array. We use the test() method to do that and we look for a space by using the regular expression /\S/. If the returned result is true, i.e. it’s found a space, it filters it out of the array.

Line 77: Close the function.

As we can see, it’s got rid of those spaces at the end. Now, we need to get just the options from our row.

78.  //Getting options without spaces
79.  let slicedData = trimmedData.slice(3, trimmedData.length);

Line 79: Using the variable trimmedData, use the slice method to get certain values in our array. We need the values from position 3 until the end of the array. So, in the brackets we state 3 (starting position), and use the length method to find the end of our array.

As you can see are left with just the options:

81.  //Adding option to form
82.  item.setChoiceValues(slicedData);
83. }

Line 82: Finally, we add this sliced data into the options for our multiple-choice question, by using setChoiceValues().

---

Making a list question

This is almost exactly the same as creating a multiple-choice question, except that in line 88 we use the addListItem() method.

85. //make List question
86. function makeListQ(rowData) {
87.  let listQuestion = rowData[2];
88.  let item = FORM.addListItem();
89.  item.setTitle(listQuestion);
90.  let trimmedData = rowData.filter((str) => {
91.    return /\S/.test(str);
92.  });
93.
94.  let slicedData = trimmedData.slice(3, trimmedData.length);
95.  item.setChoiceValues(slicedData);
96. }

---

Making a scale question

A scale question is where you have a scale, for example 1 to 5, and the user clicks on a number. So, we need to set the bottom end and the top end of the scale, plus, we have the option of adding labels to that scale, indicating what the numbers mean, for example, “bad” to “excellent”.

In our example, we have a question “Did you enjoy the classes?”, then have a scale 1 to 5, and we’re going to label the scale, so that 1 is “Not at all” and 5 is “Absolutely”. The layout on the sheet is as follows:

The first part where we set the question title is the same as we’ve seen before, except that we’re going to use the addScaleItem() method in line 101.

98. //make Scale question
99. function makeScaleQ(rowData) {
100.  let scaleQ = rowData[2];
101.  let item = FORM.addScaleItem();
102.  item.setTitle(scaleQ);

I’ve included the trimming of the blank cells here, but in reality, it’s not required as we’re going to use specific cells to get our scale and labels, but I’ve left it in anyway, and as you can see it’s identical to the code we saw in the multiple-choice question.

104.  let trimmedData = rowData.filter((str) => {
105.    return /\S/.test(str);
106.  });

The final part is different as this is where we will get the scale and the labels.

108.  //Get and set lower and upper bounds of scale
109.  let lower = trimmedData.slice(3, 4);
110.  let upper = trimmedData.slice(4, 5);
111.  item.setBounds(lower, upper);
112.  //Set labels to lower and upper bounds
113.  item.setLabels(trimmedData.slice(5, 6), trimmedData.slice(6, 7));
114. }

Line 109: Get the lower end of the scale, which in our example is the “1”. We do this by getting position 3 in our trimmedData array using the slice() method. This will return the one figure.

Line 110: We do the same for the upper end of the scale, this time getting position 4.

Line 111: Now, we need to set the bounds of our scale, by using setBounds() and adding the two figures we just collected.

Line 113: Finally, we set the labels similar to the way we got the scale bounds. We get the lower label from position 5 and the upper label from position 6.

Note, here I’ve not added them to variables first like the lower and upper values, I’ve directly put them in the brackets. You could do the same in line 111 and in the brackets put the trimmedData and slice parts there, to save a couple of lines of code.

Line 114: Close the function.

---

Making a checkbox question

This is essentially the same as the multiple-choice question, with the only difference being the addCheckboxItem() method in line 119.

116. //make Checkbox question
117. function makeCheckboxQ(rowData) {
118.  let checkboxQ = rowData[2];
119.  let item = FORM.addCheckboxItem();
120.  item.setTitle(checkboxQ);
121.  let trimmedData = rowData.filter((str) => {
122.    return /\S/.test(str);
123.  });
124.  let slicedData = trimmedData.slice(3, trimmedData.length);
125.  item.setChoiceValues(slicedData);
126. }

---

Making a grid question

A grid question is where there is a row of options and a column of options and the user selects from several options, for example, giving their opinion on several topics.

On our sheet, I’ve added the question title in column C, as per the other questions. Then the rows are next, in this case there are 4 options. Then I’ve included a cell, “Columns:” telling the user where the column options start, but which I will also use in the code to tell the script, where to get the column options. Then the column options are to the right of it.

The first part is the same as we’ve seen before, except we use the addGridItem() method in line 131.

128. //make Grid question
129. function makeGridQ(rowData) {
130.  let gridQ = rowData[2];
131.  let item = FORM.addGridItem();
132.  item.setTitle(gridQ);

As the number of options could vary, we could also have blank cells, which we need to get rid of, so we use the same filter method as we saw earlier. As this bit of code has been repeated, I would normally put this in a separate function and call it.

134.  let trimmedData = rowData.filter((str) => {
135.    return /\S/.test(str);
136.  });

Now, we need to find where the column options start and where the row options finish. We will use the cell called “Columns:” to do this for us.

138.  //Find where "Columns:" text is in array
139.  let columnPosition = trimmedData.indexOf("Columns:");

Line 139: We can find the position of a specific item by using the indexOf() method. This will look for the item stated in the brackets, in this case the text “Columns:” and return the position of it in the array as a number. We’ll store that in the variable columnPosition, to be used below.

141.  //Get row values for grid
142.  let slicedDataRows = trimmedData.slice(3, columnPosition);
143.  //Get column values for grid
144.  let slicedDataColumns = trimmedData.slice(columnPosition + 1, trimmedData.length);

Line 142: We get the row options by slicing the trimmedData array. We start at position 3 (column D) and get the values up to but not including the position of the “Columns:” text.

Line 144: Next, we get the column options by slicing the trimmedData array. We start at the position after the “Columns:” text, so columnPosition+1, and get the values up to the end of the trimmedData array, using the length method to determine the end of the array.

146.  item.setRows(slicedDataRows);
147.  item.setColumns(slicedDataColumns);
148. }

Line 146: To add the rows to our grid question, we use the setRows() method and pass the slicedDataRows variable we just set up.

Line 147: We do the same for the columns, this time using the setColumns() method.

---

Making a paragraph question

This is like the text question we started with, except for the use of the addParagraphTextItem() method in line 153.

150. //make Paragraph question
151. function makeParagraphQ(rowData) {
152.  let paragraphText = rowData[2];
153.  let item = FORM.addParagraphTextItem();
154.  item.setTitle(paragraphText);
155. }

---

Running the code

Now, we’re ready to run our code and set up the questionnaire. Select the “makeQuestionnaire” function and click play. The first time you run it, you’ll have to go through the permissions screens.

As we can see it’s created our questionnaire in our My Drive.

Opening the file up in view mode, we can see the different questions it’s set up:

Note, this example has a different question type for each question, but you can have multiple questions with the same question type and can leave some of the question types out. I also added some data validation to the question column, so that the question types can be selected from a drop down menu, allowing it to be entered quicker and ensuring the types were correctly entered.

The good thing about this piece of code is that you can easily create different types of forms with the same piece of code, and all you need to do is set up the Google Sheet with the question types, questions and options and the code will do the rest.

---

Further information

You can find the full code here on GitHub.

Plus, make a copy of the file here.

For further information on some of the methods we used in this code, check out the W3Schools site:

forEach(), slice(), indexOf(), filter(), Regular Expressions