Sending Confirmation Emails with Calendar Attachments after Google Form Submission

In short, today I succeeded in writing a script that will send a confirmation email with an iCal attachment after someone submits the conference registration form (a Google Form).

Here’s what I needed:

  • Email confirmation upon form submission.
  • Email would include their answers to some key fields and other pertinent conference information.
  • Ideally the email would come from my utah.edu address rather than my Gmail.
  • If recipients replied, the message should be sent to a listserv of conference organizers.
  • Include a calendar file so that recipients can easily add the event to their own calendars.

I started with exploring the add-ons in Google Forms and tested “Form Notifications” which worked pretty well but it was too basic and didn’t allow for including answers or attachments. It does include a feature that will send me an email every 10 responses (can change this number).

Then I found this video/article that goes through setting up a script to include attachments (see also part 1). It didn’t work for me but it did get me started writing and tweaking javascript (and jumping around all over the forums and brushing up on syntax). But definitely start with the video to get the gist of how it works.

Here’s the final code (generalized):

Assume the form entry fields are “Name”, “Email”, “RSVP”, and “Dietary Restrictions”. Insert your information where you see <these brackets>.

function myFunction(e) {
  var userName = e.values[1];
  var userEmail = e.values[2];
  var rsvp = e.values[3];
  var diet = e.values[4];

  var subject = "Confirmation: <Name of Conference> - <Date(s)>";
  var message =  "Thank you, " + userName + ", for registering for the conference. " + 
    "For your convenience, a calendar event has been attached. " +
    "\n\nConference RSVP: " + rsvp + 
    "\nDietary Restrictions: " + diet + 
    "\n\nIf you have any questions, please email us at <listserv email> or see the FAQs at <website>. ";
 
  var cal = DriveApp.getFileById("<id>");
  var calFile = cal.getAs("text/calendar");

  GmailApp.sendEmail (userEmail, subject, message, {
    from:"<Gmail alias>",
    name:"<name of sender or conference name>",
    replyTo:"<listserv email>",
    attachments:[calFile]
  });
}

And now the little things I learned along the way…

Where to create the script

This one took me a while to figure out. IMPORTANT! You don’t want to create the script in your Google Forms interface; you want it in the spreadsheet of responses! The trigger happens when a new entry is added to the spreadsheet. And make sure when you add the trigger, you allow it access to your Gmail and Drive.

Setting variables

For ease of calling the data, you want to set some variables to refer to the different data entries. Google Spreadsheets number their columns starting with 0 (which is usually the timestamp for Forms data).

Why GmailApp instead of MailApp

Because I wanted to control the replyTo parameter, MailApp could not handle the line breaks in the message. This forum answer solved it for me by suggesting GmailApp instead. Plus with GmailApp, I can now use my utah.edu email! For more parameters, see the documentation.

How to get another email address to be a Gmail alias

Follow these instructions.

If you’re affiliated with The University of Utah, the first screen is your utah.edu email and leave the alias box checked. On the next screen, use your uNID credentials to login. Then follow instructions in the confirmation email to verify changes. Now you can send email under your utah.edu address.

How to make a calendar file and insert it as an attachment

For simplicity (and familiarity) I used Outlook to create an event and then saved it as an .ics file. You can probably do this in Google Calendar, but I didn’t explore this route. Then upload the file to Google Drive. When you click on “Get shareable link” you can see the “id” that you will need for the code above (just the string of letters and numbers after the “id=” piece of the URL).

Testing the script

The only way to test it is to actually fill in the form because the email is triggered when the entry appears in the spreadsheet. For the record, I tested it no less than 36 times. If you don’t get an email within 30 seconds, it didn’t work and the code needs tweaking. Debugging is actually not easy since the interface does not always tell you you’re missing a semicolon or have an extra comma somewhere.

And there you have it! Hope that saved you a couple of hours. However, there’s nothing like the feeling you get when you’ve struggled for a while and then the email with the calendar attachment arrives in your inbox and you just shout with joy in your open office… YEA!

Advertisements

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