Request form – Sending automatic emails

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:

1. Setting up the Form (inc Email collecting and Data validation)
2. Setting up the Sheet
3. Writing the code

---

Part 1 – Setting up the Form to record details of class request

Open the Sheet and select Tools>Create a new 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 Responses then click the “Collect email addresses” toggle.

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 name questions and 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 “Response validation”. I then change the data validation drop-down option to “Number” and “Is number”.

Then I add start and finish date questions. With the 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.

---

Part 3 – Writing the code

In the Sheet, click on Extensions>Apps Script.

Click on “Untitled project” and give it a new name. Here, I’ve called it “Class request”.

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.

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.

1. //This sends an automatic email to those on the shEmails tab, whenever a Form is submitted
2. function onFormSubmit(){

Line 2: First we set up a function called onFormSubmit() and open the function with the curly brackets.

3. //Get active Spreadsheet
4.   const ss = SpreadsheetApp.getActiveSpreadsheet();

Line 4: 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.

6. //Get Sheet called 'Form responses 1'
7.   const fr1 = ss.getSheetByName("Form responses 1");

Line 7: Then we get the Form responses 1 sheet by getting the spreadsheet (stored in ss) and calling the getSheetByName() method.

9. //Get URL of active spreadsheet
10.  const urlOfSS = ss.getUrl();

Line 10: 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.

12. //Set message in email and link to spreadsheet URL
13.   const message = "Hello. There's a new request for a private class. Click on the link below to see the details." + 
14.       "\n " + urlOfSS;

Line 13: Here let’s create a variable message to store the message. We put the text we want within quote marks, 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.

Line 14: 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.

16.  //Get all emails from 'Emails' tab
17.  const emailTo = "bazrobertsbooks@gmail.com, brgablog@gmail.com";

Line 17: We state the email address we want to send the email to and store it in the variable emailTo. If there is more than one email address, just separate them by a comma within the same quote marks.

19.  //Email subject line
20.  const subject = "Private Class Request";

Line 20: Now we need a subject line for our email. We store the text we want in the variable subject.

22.  //Send it 
23.  MailApp.sendEmail(emailTo, subject, message);
24. }

Line 23: Finally, we of course want to send the email. Here we use the MailApp 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.

This program uses the on Form Submit 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 from the menu on the left-hand side.

This will open the Triggers page. Click “Add Trigger”.

Under “Select event type”, change the default “On open” to “On form submit”.

Click “Save”.

If you haven’t already authorised the script, click on your account.

Click “Advanced”.

Click on “Go to Class request (unsafe)”.

Click on “Allow”.

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.

https://docs.google.com/spreadsheets/d/1fVkLj-dI4ig9MJT7uihhgiwWD7MDtANANsDOKmOQSLU/copy

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.

---