Google Apps Script: Custom Confirmation Emails for Forms
When the lock down began, the client that I contract for put my team in charge of helping staff get items from their desks. We were provided a spreadsheet that is populated by google form submissions and were instructed to monitor the spreadsheet for any updates, then send an e-mail to the requester.
This strategy would include the repetitive work of generating a new email, typing a message saying “Hello so and so, we got your form submission” and including what they requested. It may take anywhere from five to fifteen minutes to type up the email depending on our typing speed. It would also have to be formatted and proof read to avoid typos. The message reply would be similar every single time and when ever there are repetitive tasks in the workplace and a G Suite application is in use, automation is usually an option.
So I wrote a script that not only generates a confirmation e-mail, it also generates a calendar event. The calendar event allows the form submitter to reschedule their pick up time and provides a better visual tool than a spreadsheet. I’ll be writing another tutorial in the future on generating calendar events.
Here’s what an automatically generated email and calendar event look like after a google form submission.
This particular form has been submitted close to seven hundred times, and as a result has saved my self and my team from manually typing up and sending out close to seven hundred e-mails. Normally, this calendar event would add the requestor and my team, but I’ve removed them for this article.
Well, now you know what’s possible with Apps Script.
If you are interested in learning more and writing something like this on your own, then let’s walk through how I did it.
Access the Script Editor in the Form Responses spreadsheet
First, you’re going to need a google form to work with. Find the responses spreadsheet that is associated with the form. It usually has the same name as the form with the word responses in parentheses afterward. If you’re not sure if your form is associated with a spreadsheet, you can check out this google support page.
Once you find the spreadsheet select the menu option Tools > Script Editor.
You’ll be greeted with an empty script that has a function called myFunction in it. Go ahead and delete that.
Access values using onFormSubmit(e)
The method onFormSubmit is a method that should be executed every time the form is submitted. It’s a method that apps script will pass an event object through the parameter e. The event object has all the questions and responses from the form submission in it. There’s a couple of ways to access the event object.
function onFormSubmit(e)
{
// Retrieve values depending what their index is in the array var values = e.values
var name = values[1]
var email = values[2] // Retrieve values based on their question text var name = e.namedValues["Name"]
var email = e.namedValues["Email Address"]
}
Using the named values strategy is definitely better. It’s easier, and it looks more straightforward in code. The problem with it is if you’re not in control of the form, and your team mate decides to re word some of the questions in your google form with out notifying you. Then, the namedValues strategy is not going to work. When you attempt to retrieve the value based on the incorrect name an error will be thrown. So that’s something to keep in mind.
When I wrote this script, I used the first strategy, getting each value by its index in the array, specifically because of that problem. Questions in the form were being changed, and I wasn’t notified about it until I got some of the error notifications.
Use the values from the form submission and write a message
One of the easiest things to do in code, that’s so powerful, is putting together statements based on what a user has sent to you. Here’s an example of how we can do that.
var items = e.namedValues["Items"]
var time = e.namedValues["Time"]
var date = e.namedValues["Date"]var message = "Hello " + name ", \n" +
"This is a confirmation message to let you know we received your form submission and are working on the request.\n" +
"Date: " + date + "\n" +
"Time: " + time + "\n" +
"items: " + items
Your particular use for this script is going to dictate what your script will say. You may have more or less values to display and can get as intricate as you’d like with your message.
Now that we have a custom email message to send and all of our values together, we can go ahead and send the email to both the requester and our team.
Use GmailApp.sendEmail to send your email
Most of the scripts I have written send my email from an alternate email address. It’s an address that represents my team. You may want to send your confirmation email not from your own address, but from an alternate google group address. Here’s a link to a google support page if you decide to go down that path.
Otherwise, if you’re okay sending the email from your own address, then that may make the code writing portion simpler. I’ll go ahead and show you the two ways you can pull this off.
var email = e.namedValues["Email Address"]
var recipient = email + ", your.email@gmail.com,your.team@gmail.com"
var subject = "Confirmation Message"
// Option 1 - Send from your gmail account
GmailApp.sendEmail(recipient, subject, message)// Option 2 - Send email from an alternative address
GmailApp.sendEmail(recipient, subject, message,
{bcc: "", htmlBody: "", from: "team@gmail.com"})
When the code is all put together and ran your message will be sent.
If you notice, the last parameter of Option 2 has an option called htmlBody. Meaning we can pass in html to the email. So you can not only send a plain text email, you can send an email with some good looking design in it if you are dealing with clients.
Google Apps Script is awesome, and this medium post is just a scratch of the surface of what it can do to create more efficiency in the workplace. I highly recommend checking out the documentation for more.
Testing
Testing is important when ever you want to write code that works most of the time. Testing form submissions is inconvenient if you have to manually fill out a new form every time you’d like to submit one for testing. Below, is a function I found a year ago that will submit a test for you based on the values in the fifth row of your responses spreadsheet.
function test_onFormSubmit() {
var NUMBER_OF_TESTS = 5;
var dataRange = SpreadsheetApp.getActiveSheet().getDataRange();
var data = dataRange.getValues();
var headers = data[0];
// Start at row 1, skipping headers in row 0
for (var row=5; row <= NUMBER_OF_TESTS; row++) {
var e = {};
e.values = data[row].filter(Boolean);
e.range = dataRange.offset(row,0,1,data[0].length);
e.namedValues = {};
// Loop through headers to create namedValues object
// NOTE: all namedValues are arrays.
for (var col=0; col<headers.length; col++) {
e.namedValues[headers[col]] = [data[row][col]];
}
// Pass the simulated event to onFormSubmit
onFormSubmit(e);
}
}Ah, and one more important thing to remember is for some reason when you use this test_onFormSubmit() function, your date and time values will show up formatted differently. They are probably going to look like this: Thu Jan 09 2020 00:00:00 GMT-0800 (Pacific Standard Time). If they do, don’t worry about that — that will only happen when testing. If you use a pre-filled link for your forms or just manually submit a test form then you’ll notice the date formatting will be back to normal.
Triggers
You may also notice when you submit your google form, nothing happens! The script does not submit. Yet, when you run the test function — things seems to work normally. You are also seeing the values from your mock form submissions in your spreadsheet so you know they are working. You might ask yourself what could possibly be going wrong here?
Well, you may have to manually set up the trigger for onFormSubmit when the google form is submitted. I’ve had experiences where it was set up with out my having to add the trigger. I’ve also had experiences where it wasn’t set up and I have had to add the trigger. Any way, here’s how:
First click on the trigger button. Then click on the add trigger button in the bottom right hand part of the screen. You will be met with a window that looks like the image below. You should fill out your trigger identical to mine. How often you would like to be notified of errors is your choice.
If you’ve made it this far thanks for reading and good luck on your project.
Feel free to leave a comment with any questions, concerns, or feedback!
