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 form, so that it is automatically linked to this spreadsheet.

class-request-33

To set up a question to collect the user’s email. Click on “Settings”.

class-request-2

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”.

class-request-1

Back in the Questions part, you can see it’s automatically created a question which will check for a valid email.

class-request-4

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”.

class-request-3

I then change the data validation option to “Number”.

class-request-5

Then click on “Greater than” and change it to “Is number”.

class-request-6

class-request-7

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”.

class-request-8

class-request-9

Finally, I add a Details questions, which automatically changes it to a Paragraph style question.

class-request-10


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.

class-request-11

Add a second tab and rename it ‘Emails’.

class-request-13

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.

class-request-12


Part 3 – Writing the code

In the Sheet, click on Tools>Script Editor.

class-request-14

Click on “Untitled project” and give it a new name.

class-request-15

Then click “OK”.

class-request-17

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:

class-request-24

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.

class-request-25

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.

class-request-26

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.

class-request-28

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.

class-request-29

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.

class-request-30

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

class-request-31

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.

class-request-32

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.

class-request-18

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.”.

class-request-19

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.

class-request-20

Click on “Time-driven” and change it to “From spreadsheet”. This will change the other menus.

class-request-21

Click on “On open” and change this to On form submit.

class-request-22

You should be left with this. This runs the function “onFormSubmit” when a form is submitted to the spreadsheet. Click “Save”.

class-request-23

Forgetting to set up the trigger is a common mistake to make.


Here’s the email that is sent out.

class-request-34-1


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.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


Advertisements

3 thoughts on “Request form – Sending automatic emails

  1. […] Here we’ll look at how to set up automatic emails, which contain information submitted by the Google Form user. The beauty of this is that the information is sent to you (and others) without you having to do anything and without you having to check the spreadsheet to see if there has been a submission. This is building on the code from my previous post, so the areas which are the same I will only briefly describe here. If you want more details see my previous post. […]

  2. Hi, I am trying to send form responses to a single email address as Google Forms only allows you to send 50 per day through creating a rule. I am no coder but have been trying to follow your instructions. They’re really good and simple but I think because you were trying to achieve something different I am struggling to work out what I need to do.

    • Hi Karl-If you’re just trying to send the form responses to an email address, you can replace lines 16-19 with var emailTo = “the email address”; With personal Gmail accounts, you can send up to 100 per day

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s