Library Subject Guides
In the fourth part of the course, we will start working with Google Forms, as well as learn about events and triggers to make our Apps Script code run.
Work through the page in order if you're learning, or use the links below to jump to specific bits:
So far, we've been running all of our Apps Script code either by hitting the Run button from within the Apps Script editor, or creating a menu in our spreadsheet and using that to choose to run a particular function. This is very useful when you want to run code manually, but what if you want your Apps Script code to happen when a certain thing happens, rather than when a user presses a button?
To make Apps Script code run when something happens, you can use a trigger which runs a function in response to a particular event.
What this means is that you tell Apps Script that when something happens (an event), like a file is opened or edited, a form is submitted, or a certain amount of time passes, a certain function should run. You do this by setting up a trigger, where you tell Apps Script the event that should happen and which function should run when it does happen.
When an event happens that causes a trigger to tell a function to run, you can get the data from that event and use it, for example getting the data submitted to a form. We'll look at how to do this later in this section.
Let's take a look at how to actually set up a trigger and the options you get.
Triggers can be found via the left hand menu in the Apps Script editor. You will need to hover your cursor over the icons to see the text attached to each one - by default, you are in the Editor section, and you might have been to the Executions section to check the logs for previous runs of your code. Clicking on Triggers opens the Triggers screen and we'll take a look in depth at what we can do with this screen.
When you open the Triggers screen in an Apps Script project, you won't see any results if no triggers have been created yet, but if you're opening an existing project or one that someone else has created, you might see results here. Triggers are all owned by a Google account, and that is the Google account that the code will run under.
Before adding a trigger, you should always check that there isn't already a trigger that triggers the same function with the same event (so the same trigger, basically), as this will cause the script to run multiple times each time the event happens.
To add a trigger once you're in the Triggers section of the Apps Script project, click on the + Add Trigger button in the bottom right hand corner.
Clicking on the Add Trigger button will open a new dialogue window, with the options for setting up your trigger. Depending on your screen size, you might not be able to see the whole box and the Save button, but you can scroll as needed.
Let's take a look at all of the options in the box:
Once you've chosen your settings, click on Save to create the trigger. You might have an authorisation prompt from Google if you've not run the function before. You should now see the trigger in your list of triggers.
You might need to go and tweak a trigger if it needs to be changed, or you might want to remove the trigger entirely if you are stopping using the script, changing it, or want the script to run under someone else's account.
If you click on a trigger from the list of triggers to select it, you will get a pencil icon and a three dots icon on the right hand side. Clicking on the pencil icon reopens the Add Trigger box from when the trigger was created, allowing you to change all of the options given there. This can be useful if you accidentally set up the trigger with the wrong options, but still want to keep that trigger.
If you click on the three dots icon, you get a range of options for the trigger. One of those is Delete trigger, which is pretty self-explanatory! Use this if you no longer want the trigger to run. You can also check executions and failed executions from here.
In this livecoding exercise, we are gong to have a go at setting a trigger. It will be one that monitors if the sheet is edited, and runs a function when it is edited.
Working with Google Forms is a common use for Apps Script, especially working with a Google Form that has a response Google Sheet created as well. You can use Apps Script to automate things to happen whenever a response comes in to a Form, allowing you to extend the functionality of Forms and reduce repetitive processes.
In this course, we're going to look at creating Apps Script that is bound to the response sheet (so a Google Sheets file) rather than the Form itself. This allows us to work beyond the form, adding new data into the spreadsheet like a document link once a document has been created.
When you work with Forms in Apps Script, you typically want something to happen when the form is submitted. This means the event that happens is On form submit and then we can get what we call the event values from that, which means the data that comes from the event. This means that we can easily work with the data from the submitted form without accidentally getting the wrong line of the spreadsheet (if we were using the values by finding the row of the spreadsheet, rather than by getting them directly from the form submission event).
Once you're working with event values, you cannot just use the Run button to test your code, as it won't have a value for the event values because the form submit event hasn't happened. Instead, you'll need to set up your form submit trigger and submit the form whenever you want to test your code.
Let's take a look at how to work with these event values when creating Apps Script for Google Forms submissions.
To get and work with event values, there are a few things we must do. Firstly, we need to create a function that will have a trigger set up to run when the form is submitted, and we need to make sure that function has a parameter for the event object. What that means is that within the round brackets at the end of the function name, where we have previously passed variables to the function, we want to give a variable with the letter e, which is the usual way of referring to the event object.
This is what it might look like in practice (the function doesn't have to be called onFormSubmit, but it is useful to make it clear that it is the function to run when the form is submitted):
function onFormSubmit(e){
// function code here
}
Inside our function, we need to do something with the event values! As you might have guessed, we are going to store them in a variable, using the command e.namedValues. Note that this command is unusual as it doesn't have round brackets at the end of it - this is because you aren't actually accessing something you can do with e like we've done before (which is called a method), but a property of the event object e, which is some information that is contained within it. You don't need to know why this is the case, but it is useful to note that you shouldn't put brackets on the end of it like you might be used to!
function onFormSubmit(e){
var formValues = e.namedValues;
}
Now that we have the values from the form stored in a variable, we can access the individual values and store them in variables. Unlike an array, we are going to be working with a JavaScript object, which means we need to use a different way of accessing them (we'll take a look at this further in the next box in this section). To access a form value, you need the exact text of the question from the form, which you can get from the response spreadsheet if you copy and paste what appears in the column header (or you can get it directly from the form itself, too).
Give your Form questions sensible names that aren't too long, are unique in that form, and don't have any 'space' characters you might not notice. You can turn on the description option on a Google Form to put longer text, so you can have a short question (e.g. "Name") and then some further details (e.g. "This is the name you want listed on the register.").
This text needs to go into square brackets, like an array, and then inside quote marks, because it is a string. After these square brackets, you also have to put [0] (like we did when we were getting a single line of a spreadsheet, to just get the first array) because technically the data you would get otherwise is in an array that only has one value, and we just want that value itself.
function onFormSubmit(e){
var formValues = e.namedValues;
var userName = formValues['Email address'][0];
var date = formValues['Date'][0];
}
Now we have the individual form values, we can use them in our Apps Script code as we need. There's one other useful thing you might need to know: the row of the spreadsheet that this form submission has gone onto. Having this means that you can add more data to the spreadsheet, like adding a document link or a timestamp to say when an email was sent. To do this, you can use:
var row = e.range.getRow();
Once you've created your function to run when the form is submitted, you need to create a trigger that runs from the spreadsheet using the event On form submit. You will need this to be created before you can test your function, so it is good to set up the trigger once you've got the event values in your Apps Script project, to be able to test submitting the form and make sure that is working.
For testing, you will need to keep submitting the form. If you have a very long form (or are doing a lot of testing), you can create a prefilled URL for a Google Form that you can bookmark, and then just click on that link to have the form already filled out, ready to submit.
As we discussed when looking at the Form values, sometimes in Apps Script we need to use something called an object, which is a feature of JavaScript that allows you to store lists of data in name:value pairs. These are useful because you can store a range of data within a variable, but know what the name of each item of data is, rather than relying on the position in the list like with an array.
For example, if we had a car, we might want to store various kinds of information about it, all within a car variable. We would do this like:
var car = {type:"Fiat", model:"500", colour:"white"};
And then we could access individual information about the car and, if we wanted, store it in a variable:
var carModel = car['model'];
The reason this is useful to know about is because the event values from a Form submission come in the form of an object, so that's why we have to use the name of the question within square brackets and quote marks to access the individual responses to a form. Technically, what you would see if you were looking at whole formValues object is something like:
var data = {
'Email address': ['test@york.ac.uk'],
'Event title': ['Test event title'],
'Proposed date': ['17/03/2024'],
'Target audience': ['School students']
}
So to access individual form values, you need to first specify which item in the object using ['text of question'] and then access the first (and only) item in the array using [0].
Knowing the syntax for getting form values is all you need to know to work with these objects, but if you'd like to know more about objects, JavaScript guides can help you.
In this livecoding exercise, we are going to have a go at getting the event values from a Google Form submission. You will need to set up a form and response sheet to use, create the Apps Script code, and make sure you set the trigger so it runs when the form is submitted!
And now we've reached the end of section 4 and the final project of this course, as the next section is covering additional features and where to go next with Apps Script. Once again, have a go at this project before moving onto section 5.
For Project 4, we're going to be creating documents about event proposals from submissions of a Google Form. We'll be using the skills from this section, as well as making documents from section 2.
As with the previous projects, start with the guidance document which has all the information you'll need to get started. If you get stuck, you can access the solution file to take a look at one possible solution - you'll need to make a copy of this file to see the Apps Script project and compare your code. Don't forget to look back at previous sections' work as well, and the livecoding exercises from this section.