Apps Script Basics – Using Form Responses

In this post, we’re going to look at how we can work with the responses a form user submits.

We’ll look at two main ways, 1) Getting the form responses from a Google Sheet, 2) Getting the form responses directly from a Google Form.

To show some practical uses of this, we’ll do the following:

1. Set up a simple problem-reporting log, which will email the relevant parties the problem in a classroom. We’ll do this via the Sheet and via the Form.
2. Get students’ pieces of writing submitted via a Google Form and copy them to their individual sheets, ready to have feedback added.
3. Set up a simple appointment system, which will update itself as people take the appointments, leaving only the available ones on the Form.

Problem-reporting log & email (Sheet version)

Here, we have a Google Form which a teacher can fill in if they have a problem in their classroom. They fill in the problem and click on the classroom, then submit the form.

The Form is linked to a Google Sheet and the form response is stored on the sheet called ‘Form Responses 1″. This is where we’ll get the response and then email it to the IT department, so they can come and sort the problem out.

The code

The code will get the form submission from the Sheet, then create an email containing the information from the submission, then email it to the relevant people.

1.	function sendProblem() {
2.	  const ss = SpreadsheetApp.getActiveSpreadsheet(),
3.	  fr = ss.getSheetByName("Form Responses 1"),
4.	   lastRowValues = fr.getRange(fr.getLastRow(), 1, 1, fr.getLastColumn()).getValues(),
5.	   problem = lastRowValues[0][1],
6.	   room = lastRowValues[0][2];

Line 1: Set up the function.

Line 2: Get the active spreadsheet.

Line 3: Get the sheet where the responses are and store it in the variable fr.

Line 4: We’re interested in just the final row, which is the latest response. So, we get the range (lastRow, first column, 1 row, to the last column), then get the values on that row.

Line 5: First, we want the problem submitted, which is in column B (array position 1).

Line 6: Secondly, we want the room, which is in column C (array position 2).

Now, let’s create the email. It has 3 parts: email subject, who you’re sending it to, email body. We’ll assign them to 3 variables, emailSubject, emailTo, and emailBody.

8.	//Create email
9.	const emailSubject = "Problem",
10.	    emailTo = 'brgablog@gmail.com, brgablog2@gmail.com',
11.	    emailBody = "Problem: " + problem + "<br /> \
12.	  Room: " + room;

Line 9: Let’s state the subject as “Problem”.

Line 10: We’re going to send it to two people, so we put the email addresses in an array.

Finally, we need to create the body, which will contain the information from our form submission. I want one line with the problem and the other with the room.

Line 11: We add the text “Problem: ” then add the variable problem, which will add the submitted problem. At the end I’ve added a HTML line break <br />. Use a backslash to show the next line is connected to this one.

Line 12: Then we add the text “Room: ” and add the variable room.

14.	//Send email
15.	MailApp.sendEmail(emailTo,emailSubject,'',{
16.	  htmlBody: emailBody});   
17.	}

The final part is to send the email. This will use the 3 components above.

Lines 15-16: As this is a basic email, let’s use the MailApp service and the sendEmail() method. In the brackets we need to pass the 3 components we created earlier. The email addresses we’re sending to, the email subject, the third parameters, we leave a blank pair of quote marks, as the email body will be set by a little bit of HTML. I’ve included a bit of HTML, so we’ll have to use the htmlBody object, which is added between the curly brackets and its value is the emailBody variable we set up.

To make this run automatically when a teacher fills in the form, we need to set up a trigger, which will run the code when a form is submitted.

Click on Triggers from the menu on the left-hand side.

To add a new trigger, click on “Add Trigger”.

Select “sendProblem”, “From spreadsheet” and “On form submit” from the drop-down menus and click Save. This will add the trigger.

Authorize the script, then fill in the form and submit it and as you can see, it’s emailed the appropriate people with the problem and room.

Simple, yet so effective and quicker and easier than writing out an email every time.

Problem-reporting log & email (Form version)

Now, let’s look at how we can do the same thing but this time get the form response information directly from the form and send the email from within Forms, without the need of a Google Sheet. The form is the same format we used above.

From the form, click on the 3-dot menu and select the Script Editor.

The code

The first part of the code will be different from that above.

1.	function sendProblemFromForm() {
2.	//Get submitted problem and room from form
3.	  const form = FormApp.getActiveForm(),
4.	    formResponses = form.getResponses(),
5.	    latestFR = formResponses[form.getResponses().length-1];

Line 1: Set up the function.

Line 3: Get the active form using FormApp.

Line 4: First, we get all the responses form the form, using getResponses(). We’ll store those in formResponses.

Line 5: Now, let’s get the latest form response. We just get the last response in the formResponses array, using the length of the array to find the last one. We minus one as it’s an array.

getResponses() makes an array of all the responses from that form. getResponses()[n] gets the responses from the nth submission, for example, the latest submission.

We then need to get the response for each individual question within that submission. We do that by getting the itemResponses() for that submission, then getting a response for a particular position within that array.

7.	  const itemResponses = latestFR.getItemResponses(),
8.	    problem = itemResponses[0].getResponse(),
9.	    room = itemResponses[1].getResponse();

Line 7: First, we get all the responses for the latest form submission and store it in the array, itemResponses.

Line 8: Now, we just pick the particular question response we want. The problem is the first question, so will be itemResponses[0] and then we get the response.

Line 9: We do the same for the room, except this is the second question, i.e. itemResponses[1].

Lines 11-20 are identical to what we saw in the previous example.

11.	//Create email
12.	  const emailSubject = "Problem",
13.	    emailTo = ‘brgablog@gmail.com, brgablog2@gmail.com’,
14.	    emailBody = "Problem: " + problem + "<br /> \
15.	  Room: " + room;
16.	 
17.	//Send email
18.	MailApp.sendEmail(emailTo,emailSubject,'',{
19.	  htmlBody: emailBody});   
20.	}

We set up a trigger again, with the drop-down menus as in the previous example.

When the form is submitted, it will send an email as we saw in the previous example, except without the need to register the response on the Google Sheet.

Move students’ pieces of writing to their individual sheets

Google Forms is great for receiving students’ work but if you use the same form for all your students, all that work appears on the same sheet and you may not want to share that with your students. A simple solution is to have a separate sheet for each student, where all their work is in one place, which you could share or send to them.

Here, we’re going to set up a simple way for the script to get the latest form submission and to copy it to the student’s individual sheet. The main idea of this script is to show how easy this is and I’m sure you could think of other applications for your own situation.

We have a form where the student selects their name from a drop-down list and fills in their piece of writing. I highly recommend using a list instead of the student typing in their name, not just because it’s quicker for them but more importantly it makes sure the format of their name is correct, as we’re going to use this to move their submission. A simple typo would stop this working.

On the Google Sheet we have 5 pages, the form responses sheet called “Assignment1” and the sheets for each of the students.

Their submissions appear on the “Assignment1” sheet.

The script then appends them to their individual sheets:

The code

1.	function assignments() {
2.	  const ss = SpreadsheetApp.getActiveSpreadsheet();

Lines 1-2: Set the function up and get the active spreadsheet.

4.	//Get assignment  
5.	  const fr = ss.getSheetByName("Assignment1");
6.	  const lastRowValues = fr.getRange(fr.getLastRow(), 1, 1, fr.getLastColumn()).getValues();

Line 5: Get the form responses sheet.

Line 6: Get the latest form submission, which is on the last row.

8.	//Get the sheet of the student who's just submitted and append their assignment on that sheet
9.	  const sheets = ss.getSheets();
10.	  for (var sh = 0 ; sh < sheets.length ; sh++) {
11.	   let sheetName = sheets[sh].getSheetName();
12.	   if(lastRowValues[0][1] === sheetName){
13.	ss.getSheetByName(sheetName).appendRow(lastRowValues[0]);
14.	   }
15.	  }
16.	}

This next part will get the sheets on the spreadsheet, then loop through them and if it finds a match to the student in the latest submission, it will get the submission and append it to that sheet.

Line 9: Get all the sheets and store them in the array sheets.

Line 10: Set up the for loop to loop through the sheets. We start at 0 and continue for the length of the array sheets.

Line 11: Each loop, we get the sheet name.

Line 12: Then we just check if the name from the latest assignment (lastRowValues[0][1]) is the same as the sheet name.

Line 13: If it is, we get that sheet by its name and append the content of the latest form submission to it.

This is better than hard coding the name checks, check if sheetName === “Paul”, etc, as this will work regardless of the names of sheets and the number of sheets.

If you want to share the sheet with each of your students but you don’t want to share the other students’ info, you could set up individual files for them and send the form responses into them, by getting their file, the sheet and setting the range with the response data.

Alternatively, without code you could use the importrange function in their individual files, to import the data from their sheets automatically into their file.

Appointment system

In this final example, we’ll create a simple appointment system, where the user selects a time on the Google Form and submits it. The form is then updated, so when the next person opens it, the times previously selected don’t appear, so aren’t available. This will show how we can get the latest form responses from the form and then update the form based on that submission.

Here’s the form:

We are just getting two pieces of information, their name and the appointment time.

The code

Enter the following code in the form script editor.

1.	function meetingTimes() {
2.	  const form = FormApp.getActiveForm();

Lines 1-2: Set up the function and get the active form.

4.	//Get current times on Form
5.	  let timesArray = [];
6.	  const questions = form.getItems();
7.	  const timeQ = questions[1].asMultipleChoiceItem();
8.	  const choices = timeQ.getChoices();
9.	  for (var i = 0; i < choices.length; i++) {
10.	   timesArray.push(choices[i].getValue());
11.	  }

Line 5: Set up an empty array called timesArray. This is where we will put the appointment times.

Line 6: We get the questions from the form using getItems().

Line 7: Get the question which contains the appointment time, i.e. question[1] (2nd question) and we get it as a multiple-choice item.

Line 8: We need to get the choices linked to that question, by using getChoices().

Line 9: Now, we loop through all the choices (from 0 to the number of choices, i.e. the length of the array choices).

Line 10: Each loop, we get the value of the choice (i.e. what it says), and add it to the timesArray array, by using the push method.

As we can see, in timesArray above we have all the times. Now, we need to get the latest form submission and the time submitted. Lines 13-20, we’ve seen before in the form example above.

13.	//Get all form responses and the latest one  
14.	  const formResponses = form.getResponses(), 
15.	  latestFR = formResponses[form.getResponses().length-1];

Line 14: First, we get all the responses on the form.

Line 15: Then, we get the latest response by getting the last response in formResponses.

17.	//Get submitted time from form
18.	const itemResponses = latestFR.getItemResponses(),
19.	 itemResponse = itemResponses[1],
20.	 submittedTime = itemResponse.getResponse();   

Line 18: Now, we need to get the specific response, which has the submitted time. We get the item responses from the latest form response.

Line 19: Then, we get the second item response, which is the time.

Line 20: Finally, we get the response of that specific item, i.e. the actual time. We place it in the variable submittedTime.

Now, we need to loop through timesArray and match the submitted time with one of the times in the array. Once found, we get the position of it, then remove it from the array.

22.	//Remove submittedTime from array
23.	  for (x in timesArray){
24.	   if(timesArray[x] === submittedTime) {      
25.	    const indexT = timesArray.indexOf(submittedTime);
26.	    timesArray.splice(indexT, 1);   
27.	   }    
28.	  }

Line 23: This time will use the for in loop to go through all the items in the timesArray array.

Line 24: Each loop will check to see if the time in the array (at position x) is equal to the one submitted.

Line 25: If so, it will get the position of the time in the array by using the indexOf() method. This will return a number related to the array position.

Line 26: We can then use that number to remove the item from the array at that position by using the splice() method. This will get the item at that index and will remove one item from the timesArray array.

As we can see it’s removed the “10:00” option as that was the last one submitted by the user.

30.	//Replace time question on form with array
31.	  timeQ.setChoiceValues(timesArray);     
32.	}

Line 31: The final part is to update the form, so others can’t select the same time. As we previously accessed the form and got the question with the times, we can use the same variable timeQ and we just need to set the choices using the timesArray array.

To make it work automatically on a form submission, we’ll need to set up the trigger within the form as we saw earlier.

As we can see, if we open the form again, it has removed the 10:00 option.

If you want to re-set up the form with the times again, here’s a little piece of code to do that, so you don’t have to retype them in!

1.	function setUpForm() {
2.	  const form = FormApp.getActiveForm(),
3.	    questions = form.getItems(),
4.	    timeQ = questions[1].asMultipleChoiceItem();     
5.	  timeQ.setChoiceValues(['09:00','09:30','10:00','10:30','11:00','11:30','12:00']);  
6.	}

There is one potential problem and that is if more than 1 person has opened the form at the same time, the form won’t update while it’s open, so there is the potential that 2 people could choose the same time.

Hopefully, you can see that with very little code, you can do some really useful things with form responses.

You can make a copy of the files using the links below:

Problem log: Sheet Form

Assignments: Sheet Form

Appointments: Form

If you want to know more about the JavaScript methods used, click on the links below:

splice, indexof

a