Automatically emailing info from a form submission

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.

This is the email that we’re going to send automatically:

Let’s look at it step by step.

1. function onFormSubmit() {
2.  //Get active Spreadsheet and Form Responses sheet
3.  const ss = SpreadsheetApp.getActiveSpreadsheet();
4.  const fr1 = ss.getSheetByName("Form responses 1");
5.  const urlOfSS = ss.getUrl();
6.  const timeZone = Session.getScriptTimeZone();

Line 1: First, we set up the function.

Line 3: Then get the active spreadsheet and store it in the variable ss.

Line 4: Then we get the sheet which contains the information we want, which in this case is on ‘Form Responses 1’.

Line 5: Then, get the URL of the spreadsheet, which we will include in the email.

Line 6: Finally, let’s get the time zone we’re working in. We’ll need this to change the format of the dates later on.

8.   //Get lastest submission
9.   const lastRow = fr1.getLastRow();
10.   const lastColumn = fr1.getLastColumn();
11.  const lastSub = fr1.getRange(lastRow, 1, 1, lastColumn).getValues()[0];

We want to get the values on the last row, which is the latest form submission. There are 3 steps to do this. We need to get the last row on the sheet, the last column and then get the range using that information.

Line 9: We get the last row of the form responses 1 sheet (using the getLastRow method) and store it in the variable called lastRow.

Line 10: Similar to the lastRow we get the last column (using the getLastColumn method) and store it in the variable lastColumn.

Line 11: Now we want to get the values in the last row, from the first column to the last column. We do that by getting the range (using the getRange method). This takes 4 pieces of information; starting row, starting column, number of rows, number of columns). So we start on the lastRow, we start at the first column, we only want 1 row, and we want to collect data until the lastColumn.

Then we add getValues to get the values in that range. getValues() returns a 3D array, and to simplify things, we can simply convert it to a 2D one, i.e. an array with one line, by adding the [0] at the end.

Now we have an array variable called lastSub which contains all the data from that last row. Now, let’s assign a variable for each piece of information on that row, so that we can use the variables later in the code and so we can clearly see what’s in those variables.

13.  //Get info from specific cells in last row and assign variables
14.  const name = lastSub[1];
15.  const email = lastSub[2];
16.  const phone = lastSub[3];
17.  const timetable = lastSub[4];
18.  let startDate = lastSub[5];
19.  let finishDate = lastSub[6];
20.  const details = lastSub[7];

Lines 14-20: Here, we’re extracting a specific bit of information from the lastSub array, and so need to state the position of the information. The array contains this at the moment:

[29/01/2017 15:43:01], [Ian Student], [brgablogse@gmail.com], [123456789], [Mon/Wed 17:00-18:00], [06/02/2017], [31/03/2017], [To prepare for the CAE exam.]

Then, for example, the name “Ian Student” is at position [1] in the array (remembering that the first item is [0] in an array). So, we write lastSub[1]; to get the name.

We then repeat the same format for each piece of information we want, just changing the position in the array each time.

Note, for the startDate and finishDate variables we’re using let not const as we’re going to change the format of them.

In lines 18 and 19, we’re getting the dates from the form submission and we could use them directly from the sheet. The problem is that they won’t be in the format they are on the sheet. They will look like this:

Not the easiest date to read and normally we don’t want the full date, so let’s change the format of the date to the way we want it. For this email, I want the format to be DD-MM-YYYY, e.g. 06-02-17.

22.  //Shorten start date
23.  if (startDate) {
24.    startDate = Utilities.formatDate(startDate, timeZone, 'dd-MM-yyyy');
25.  }
26.  else {
27.    startDate = "---";
28.  }

Line 23: First, I check the user has in fact inputted a date. Sometimes, they don’t know the start or finish date yet. So, I write an if statement to check if the variable startDate exists. The if statement syntax is:

if (logic) {do something if it’s true}

Line 24: If a value does exist, then it will run the code between the curly brackets. This will format the date. It gets the startDate, the timeZone, and converts the date into the format dd-MM-yyyy.

Line 25: Close the if statement.

Lines 26-28: What happens if the if statement returns false, well I want it to put a few dashes instead of a date. Here we use the else statement, which has the same syntax as the if statement and will run if the above if statement is false. So, here I just set the variable startDate as three dashes “—“.

30.  //Shorten finish date
31.  if (finishDate) {
32.    finishDate = Utilities.formatDate(finishDate, timeZone, 'dd-MM-yyyy');
33.  }
34.  else {
35.    finishDate = "---";
36.  }

The finish date follows the same pattern as above.

Now let’s set up our email.

38.  //Email subject line and recipients
39.  const emailTo = "baz@bazroberts.com, brgablog@gmail.com";
40.  const subject = "Private Class Request - " + name;

Line 39: First, set who the email will be sent to. For more than one person, just separate them by a comma and include them all in the same quote marks.

Line 40: Set up the subject line. Here, I’ve passed the name variable so we now who it’s referring to without having to open it.

42.  //Set message in email
43.  let message = "Hello. There's a new request for a private class. Below are the details." +
44.    "\n " +
45.    "\n Name: " + name +
46.    "\n Email: " + email +
47.    "\n Phone Number: " + phone +
48.    "\n Timetable: " + timetable +
49.    "\n Start date: " + startDate +
50.    "\n Finish date: " + finishDate +
51.    "\n Details: " + details +
52.    "\n " +
53.    "\n Link to spreadsheet:" +
54.    "\n " + urlOfSS;

Line 43: We set up the variable message, to store the information and in the first line type a short introduction. Note, at the end of every line from line 43 to 53, we have to add a “+” to show every line is connected together, and everything is within the message variable. Also, in the email, I want to list the details, one line after another, so we use “\n ” to add a line break.

Line 45: To add the name, next to the line break we type the text “Name: ” and close it with the inverted commas. Then we need to add the variable name, putting pluses (+) either side.

Lines 46 to 53 are written in a similar way. The final line of the email, line 54, ends with a semi-colon and doesn’t have a plus.

Then we send the email using the MailApp class and sendEmail() method. Then add the 3 variables, emailTo (the email addresses), subject (the subject line), and message (our email message).

56.  //Send it 
57.  MailApp.sendEmail(emailTo, subject, message);
58. }

Remember to activate the onFormSubmit trigger, as explained in my last post.

---

As this has touched on JavaScript areas such as, arrays, if, and else statements, if you’re not confident using these, I would suggest you read the following on the excellent W3schools website:

arrays / if else

Here’s the link that lets you make a copy of the spreadsheet I’ve used, which will copy the spreadsheet, the linked form, and which contains the above code.

---