As powerful as a script is on its own, it can be hugely more useful when combined with a Google Form and Spreadsheet.Take a look at this Google spreadsheet, and click File > Make a Copy to have a copy for yourself to work from.When you copy a Google spreadsheet that is a Response form, you are also automatically copying the Form itself and any Scripts that may be attached to the spreadsheet. To see the Form, click Form > Edit form. To see the Script attached to it, click Tools > Script Editor…
The script you see here is very similar to the one from the previous example, with one important difference: the values that are coming into the script are not typed into the script itself, but triggered from submitting the Google form.
To do this, we are using a special function called onSubmit, as well as installable triggers. See the Google documentation for a more in depth explanation of triggers.
Basically, a trigger responds to an event, whether a document opening, a spreadsheet getting edited, a form submitted, etc. There are two types of triggers, simple and installable. Simple triggers can run basic functions, but nothing that requires authorization (like sending an email). This is to protect the user from a program acting in the background without his/her knowledge.
To get this script to function properly, you need to click Resources > Current project’s triggers. Then click on “Click here to add new one now”. Your trigger should have these properties:
- Run:
- onSubmit
- Events:
- From spreadsheet
- On form submit
While you are at it, click on ‘notifications’ and set it so that you get an email immediately if there is an error. This will be very helpful when debugging and troubleshooting scripts.
Notice that a parameter is being passed into onSubmit, an object called ‘e’ for event. This object has several methods, but the one we care about now is e.values
We can use the e.values method to return an array of all the values that were just submitted in the form. This will allow us to input data into our script directly from the form submission, either your submission or that of someone else.
Now we have a basic version of the Student Feedback Script. This can be used as is, or it can be customized and enhanced.
Google Scripts Lessons 1-10
Click on a lesson below for a written tutorial demonstrating the lesson.
- Lesson 1 – “Hello World!”
- Lesson 2 – Challenge: Calling Functions
- Lesson 3 – Variables
- Lesson 4 – Variable Passing
- Lesson 5 – Variable Passing Challenge
- Lesson 6 – Arrays of Variables
- Lesson 7 – Modifying a Google Doc
- Lesson 8 – Modify Doc and Send Email
- Lesson 9 – Simple Feedback script
- Lesson 10 – Full-scale feedback
