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.
From the Sheet, open the Script Editor.
Here’s the full piece of code we’re going to use. It’s 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.
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 options there are. Getting the length of the array data, we will see there are 7 rows (note, the 7th row are the answers not pictured in the Sheet screenshot above), 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).
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.
Line 10: Now, let’s get the questions and add all of the in one go to an array called questions. We do this by get 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.
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).
Line 20: Then we create a new form.
Finally, we need to add the questions and the options to our newly-created form.
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  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 by clicking the play button.
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.
Here’s the full script. The first part is similar to above but form 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.
Lines 30-31: Set up the new function and get “Sheet2”.
Lines 33-36: Get the data on Sheet2, the number of questions and options that we’re going to work with.
Line 39: Get the questions and store them in the questions array.
Lines 42-46: Get the options for each question and store them in blocks in the allOptions variable.
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().
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.
Here’s the script on GitHub.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” - iBooks store / Kindle store
- "Beginner's Guide to Google Sheets" - iBooks store / Kindle store
- "Beginner's Guide to Google Docs" - iBooks Store / Kindle store
- "Beginner's Guide to Google Slides" - iBooks Store / Kindle store
- "Google Sheet Functions - A step-by-step guide" - iBooks Store / Kindle Store