In my previous posts we looked at how we can create Google Forms from Google Sheets. This time we’ll 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:
- 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.
- Get students’ pieces of writing submitted via a Google Form and copy them to their individual sheets, ready to have feedback added.
- 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 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.
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.
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 teh end I’ve added a HTML line break.
Line 12: Then we add the text “Room: ” and add the variable room.
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. Note, as I’ve included a bit of HTML, we’ll have to use the htmlBody parameter, which is added between the curly brackets.
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 the clock icon on the toolbar.
Click “No triggers set up…”.
Select “sendProblem”, “From spreadsheet” and “On form submit” from the drop-down menus and click Save.
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-dots menu and select the Script Editor.
The first part of the code will be different from that above.
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.
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 and then we get the response.
Line 9: We do the same for the room, except this is the second question, i.e. itemResponses.
Lines 11-20 are identical to what we saw in the previous example.
We set up a trigger again, with the drop-down menus as above.
Move students’ pieces of writings 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 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:
Lines 1-2: Set the function up and get the active spreadsheet.
Line 5: Get the form responses sheet.
Line 6: Get the latest form submission, which is on the last row.
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 if the name from the latest assignment (lastRowValues) 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.
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:
Enter the following code in the form script editor.
Lines 1-2: Set up the function and get the active form.
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 (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.
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.
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.
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.
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!
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 find the code on GitHub. Here are the links:
You can make a copy of the files using the links below:
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