APPS SCRIPT BASICS – Creating & updating a Google form

In this post, we’re going to look at how to create a Google Form with a multiple-choice question. We’ll look how you can create the form from data in the script, then look how it can be done taking data from a Google Sheet. In the final example, we’ll look at how we can update the question with data from a Sheet.

Creating a Google Form with a multiple-choice question

Here we’re going to create a new form called “New form” and add the question “Where do you want to go on holiday?” and add two options “Seville” and “London”.

1.	function createForm() {
2.	  const form = FormApp.create("New form");
3.	  const formQ1 = form.addMultipleChoiceItem();
4.	  formQ1.setTitle('Where do you want to go on holiday?');
5.	  formQ1.setChoiceValues(['Seville', 'London']);
6.	}

Let’s look at the code line by line:

Line 1: Set up a function called createForm.

Line 2: Use FormApp class then the method create() with the title of the form in the brackets to create a new form. To then work with it, we store it in the variable form.

Line 3: Now let’s add the multiple-choice question. We get the form and then use the addMultipleChoiceItem() method. We then store this in the variable formQ1, to then add the question and possible options.

Line 4: To add a question, we get the ‘multiple-choice item’ and use setTitle() to set the question.

Lines 5-6: Finally, we need to set the choices that will appear for the question. Use setChoiceValues() and in the brackets list the options as an array with a comma between each option. Close the function in line 6.

Run the createForm function and the first time authorize the program.

Your newly-created form will appear in you’re my Drive.

Opening the form, we can see it’s given the form a title, added a multiple-choice question and added 2 options.

Opening the form view we can see the question we’ve set up.

Creating a Google Form with data from a Google Sheet

Often you don’t want to have to ‘hard-wire’ the form questions and options, i.e. adding all that information within the script. A more flexible way, is to use a Google Sheet and write the question and options there and then get the script to read that information and add it to your form.

Here we have a page called “newQ”.

On the page in column A, we have a question and two options.

The script below is going to get the spreadsheet, get the question and options, then create a form, with a multiple-choice question, using the question and options info from the sheet.

8.	function createFormFromData() {
9.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("newQ"),
10.	    question = ss.getRange(2, 1, 1, 1).getValue(),
11.	    options = ss.getRange(4, 1, 2).getValues();
12.	  
13.	const form = FormApp.create("New form"),
14.	    formQ1 = form.addMultipleChoiceItem();
15.	formQ1.setTitle(question);
16.	formQ1.setChoiceValues(options);
17.	}

Let’s look at it line by line:

Line 8: Create a function called “createFormFromData”.

Line 9: Get the active spreadsheet and get the sheet called “newQ”.

Line 10: Get the question from cell A2 (i.e. row 2, column 1) and store it in the variable question. Note, here you could actually shorten the getRange part to ss.getRange(2,1).getValue() as we are only getting 1 row and 1 column.

Line 11: Get the options from cells A4 and A5 (i.e. rows 4 to 5, column 1) and store it in the array options.

Line 13: As we saw before, we use FormApp to create the new form.

Line 14: Again as before, we need to add a multiple-choice question.

Line 15: We set the title of the question using the variable question.

Lines 16-17: Set the choices using the array options, then close the function.

Run the function “createFormFromData”.

The same as before, we get a new form with the question added.

Updating a question in a Google Form from data in a Google Sheet

We can use similar code to update an already existing Form and change the question and options within that form.

This time our question and options are on a page called “updateQ”.

Here, we have a different question and 2 different options.

The script is going to get the question and option info as we did before, then it’s going to open an existing form, then change the first question to the information we collected from the Sheet.

19.	function updateFormFromData() {
20.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("updateQ"),
21.	  question = ss.getRange(2, 1, 1, 1).getValue(),
22.	  options = ss.getRange(4, 1, 2).getValues();
23.	
24.	  const form = FormApp.openById('1ANwqzhIGiYQf3a4VBa_Ul2Et16NHwROupCoxURrKPxo'),
25.	  allItems = form.getItems(),
26.	  formQ1 = allItems[0].asMultipleChoiceItem();
27.	  formQ1.setTitle(question);  
28.	  formQ1.setChoiceValues(options);
29.	}

Let’s look at it line by line:

Line 19: Set up the function.

Line 20: Get the sheet called “updateQ”.

Line 21: Get the question. You could shorten this to ss.getRange(2,1).getValue().

Line 22: Get the options. You could shorten this to ss.getRange(4,1,2).getValues();

Line 24: Get the existing form by opening it by its ID and store it in the form variable. You could do the same by getting the form’s URL with openByUrl(). Obviously, use your own form ID here.

Line 25: This time, we can’t just add a multiple-choice item, as we would just create a second question. Instead, we first need to get the number of items in our form. To do so, we use getItems(). It gets the items as an array. Here, I’ve stored it in allItems.

Line 26: Now, we need to get our question. It’s the first question, so this will be position 0 in our allItems array. We need to state what type of question we want. So, here we get it as a multiple-choice item. Note, the difference from the previous example, where we used addMultipleChoiceItem().

Line 27: Then, we set the question title as before using setTitle() and the question variable.

Lines 28-29: Then, we set the options as before using setChoiceValues() and the options variable. Close the function.

Run the function “updateFormFromData”.

As we can see it’s changed the question and options in our previous form.

So, as you can see, it’s very easy to create questions in a form and to update them from data stored in a Google Sheet.

You can find more information about item and its methods here: https://developers.google.com/apps-script/reference/forms/item

You can make a copy of the Google Sheet with the code here.

a

a