One of the most useful things I’ve learnt to do with Google Apps Script, is to email people automatically when a form is submitted. It has countless uses and in this example, we have a user requesting a private class via a Google Form. The relevant parties will receive an email which will contain a short message and a link to the sheet containing the details.
There are three parts to this:
- Setting up the Form (inc Email collecting and Data validation)
- Setting up the Sheet
- Writing the code
Part 1 – Setting up the Form to record details of class request
Open the Sheet and select Tools>Create a form, so that it is automatically linked to this spreadsheet.
To set up a question to collect the user’s email. Click on “Settings”.
Click “Collect email address. If you want the user to receive a copy of their request, then click “Response receipts”. You then have a choose of always sending them a copy or letting them choose this option in the Form. Here I always want them to receive it. Click “Save”.
Back in the Questions part, you can see it’s automatically created a question which will check for a valid email.
Then I add a telephone question with a short answer. Here I want it to check the user has only entered numbers. Click on the 3 dots on the bottom-right of the question, and then click “Data validation”.
I then change the data validation option to “Number”.
Then click on “Greater than” and change it to “Is number”.
Then I add start and finish date questions. With the new AI, when I type “Start date, it automatically changes the question type to “Date”.
Finally, I add a Details questions, which automatically changes it to a Paragraph style question.
Part 2 – Setting up the spreadsheet
Open the spreadsheet and on the ‘Form Responses 1’ tab you’ll see the questions in row 1. I like the fact that the sheets with a form linked to it, now have a GForm symbol on it.
Add a second tab and rename it ‘Emails’.
In that sheet, type in the email addresses you want the request to go to. In column A I’ve added their names and in column B their emails.
Part 3 – Writing the code
In the Sheet, click on Tools>Script Editor.
Click on “Untitled project” and give it a new name.
Then click “OK”.
We want the program to run when there’s a form submission, to get the spreadsheet URL and then email a group of people a short message telling us there’s a new request, including the URL, so we can easily click on it to open the sheet to see the request details. Here’s the code we’re going to use:
Let’s look at the code section by section. Note that you don’t need to add the comments (the parts after //) but it can help you follow the code and remember what each bit does.
First we set up a function called onFormSubmit() and open the function with the curly brackets.
Then we want to get the active spreadsheet. Here we set up a variable called ss, which we will refer to throughout the program. Then we use the SpreadsheetApp class with the getActiveSpreadsheet method.
Now we want the spreadsheet URL. Here we set up a variable urlOfSS, which is where we will store the URL. Then we use the ss variable we just created and use the getUrl() method to get the URL.
Now we need a message in our email.
Here let’s create a variable message to store the message. We put the text we want within inverted commas, as it is a piece of text. I also want to add the URL, so I add a + at the end to show it’s connected to the text.
Then I want to put the URL on a new line, so I add “\n “ which in HTML is a line break. Then I add another + and add the variable urlOfSS.
Now we want to get the email address of those we’re going to send the email to. Let’s look at this line by line.
Line 17: Using the getSheetByName method we get the Emails sheet. Put the name of the sheet in the brackets between inverted commas. We’ll store this in the variable emails.
Line 18: We want to get the last row on the sheet that has data in it. Assuming the sheet is set up as describing in part 2 above, then this will tell us that the last row is row 3. This uses the Sheet class followed by the getLastRow() method. We store this in the variable numRows. We’ll use this info to get the correct range in the next line.
Line 19: Now we need to get the email addresses in sheet. We use the Sheet class again and this time get a specific range by using the getRange method. This has 4 parts: starting row, starting column, number of rows, number of columns, So, in our example, we’re:
- starting in row 2
- starting column 2
- getting the number of rows from the variable numRows (which in this case is 3)
- there is only 1 column
So, in the brackets we write (2, 2, numRows, 1)
Important: This will get the ranges but not the values within those ranges. So, we need to add another method, getValues() which will stored the actual email addresses in the variable emailTo.
Now we need a subject line for our email. We store the text we want in the variable subject.
Finally, we of course want to send the email. Here we use the GmailApp class followed by the sendEmail method. In the brackets we add the three pieces of information we collected earlier:
- emailTo (Email addresses we’re sending to)
- subject (Email subject line)
- message (Email message)
To close the function we always add a curly bracket at the end.
Note that all the lines except lines 2 and 26 have a semi-colon at the end.
This program uses the onFormSubmit trigger, which allows the program to run automatically when a Form is submitted. This needs setting up, otherwise it won’t run automatically, but it’s simple to do.
In the Script Editor, click on the clock icon.
This will open the Current project’s triggers menu. A new program won’t have any set up. Click on the blue “No triggers set up. Click here to add one now.”.
Here you need to tell it what to run and how to run it. All of these are simple drop down menus. By default the onFormSubmit program will be selected. Now let’s add the event details.
Click on “Time-driven” and change it to “From spreadsheet”. This will change the other menus.
Click on “On open” and change this to On form submit.
You should be left with this. This runs the function “onFormSubmit” when a form is submitted to the spreadsheet. Click “Save”.
Forgetting to set up the trigger is a common mistake to make.
Here’s the email that is sent out.
Here’s a link to make a copy of the above spreadsheet. The linked form will be copied automatically and the code will already be in the Script Editor.
The email in this post is quite basic and performs a similar function to the automatic email you can receive when setting up Notification rules, found in the Tools menu. However, this does allow you to email a group of people, plus it’s a springboard for my next post, which will focus on how you can extract data from the latest form submission and include it in the email.
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
Follow my Google+ Learning Google Apps Script Collection