Doing More with Google Apps: Google Form Submit -> Sends Email and Creates Google Calendar

Streamlining actions and actives can help increase productivity, but also can be tons of fun to build out a system that does many things. We’ll create a Google Form that when it is submitted, it sends out email(s) and creates a google Calendar event and invites who you emailed. The example we use for the demo is a google form for event submissions. The form collects the data needed for the event, then creates a calendar event, invites members and then sends an email with the information.

First you start out by making a Google Form. Below is an example of blank starter form with questions about the Name of the event, Description, the Start Date and Time and the End Date and Time, as well as the Location.

exampleform

To include the time in a Date option, click on the three circles on the bottom left hand corner of the question and it’ll should you the options of including time and description. Time information is needed for the calendar event creation, but if your not making a calendar event, this can be omitted.

includetime

Then go to the Response Tab and click on the Create Spreadsheet and create a New spreadsheet:

createspreadsheetcreate-cal2

Now in the spreadsheet, you’ll need to go into building the script. Go to the Tools tab and click on the script editor…

startscriptreal

This will take you to the script file and here is where you write your javascript telling the form what to do:

//This creates the calendar event with its options
function createEvent_ (namedValues) {
 //options are the key/value pairs you can set when created a
 //calendar event, below accesses the data given for description
 //and location - guest is hard coded, but can be dynamic
 var options = {
    description: namedValues.Description[0],
    location: namedValues.Location[0],
    guests:"me@gmail.com"};
 //cEvent makes the calendar event, You have to choose the calendar
 //name that you would like to use, then ask for the Name of the 
 //event, start date and end date, then passes the options you have 
 //selected above
 var cEvent = CalendarApp.getCalendarsByName("Example")[0].createEvent(
                 namedValues.Name[0],
                 new Date(namedValues.Starts),
                 new Date(namedValues.Ends),
                 options)
}
//this connects the submission of a google form (which creates a record 
//on a spreadsheet) to call the EmailGoogleFormData function
function Initialize() {
 try {
 var triggers = ScriptApp.getProjectTriggers();
 for (var i in triggers)
 ScriptApp.deleteTrigger(triggers[i]);
 ScriptApp.newTrigger("EmailGoogleFormData")
 .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
 .onFormSubmit().create();
 } catch (error) {
 throw new Error("Please add this code in the Google Spreadsheet");
 }
}
//calls the createEvent_ then creates and send the email
function EmailGoogleFormData(e) {
 createEvent_(e.namedValues);
 if (!e) {
 throw new Error("Please go the Run menu and choose Initialize");
 }
 try {
 if (MailApp.getRemainingDailyQuota() > 0) {
 //who you want to email
 var email = "me@gmail.com";
 // the subject title of the email
 var subject = "New Event";
 //grabs the keys and data for iternations on below
 var key, entry,
 message = "",
 ss = SpreadsheetApp.getActiveSheet(),
 cols = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
 // Iterate through the Form Fields to build the entry
 for (var keys in cols) {
 key = cols[keys];
 entry = e.namedValues[key] ? e.namedValues[key].toString() : "";
 // Only include form fields that are not blank
 if ((entry !== "") && (entry.replace(/,/g, "") !== ""))
 //builds the message
 message += key + ' : ' + entry + "\n\n";
 }
 //sends the email with the who your emailing, the subject title and
//the message(body)
 MailApp.sendEmail(email, subject, message);
 }
 } catch (error) {
 Logger.log(error.toString());
 }
}

When you are happy with your code, save the code and you’ll need to initialize it:

initalize

If you want to the form submission to add an event to a new calendar, create it via your google calendar and replace the Calendar name in the script (make sure to update your script with the new Calendar name):start-create-cal

create-cal

There will be several permission requests throughout this process to connect the different applications, click yes. Now that everything is ready to go, test it out and you should get a calendar event and an email!

excalexemail

Congratulations! This system is helpful for when you have a group of friends or network that are super busy and want to be able to invite the group to different events. This can also be modified so instead of automatically creating, you can verify the information prior. Also, you can pull out the calendar portion completely and have the form set up so it does some data validation and emails the content to different people.

For the full code, go here.

For more things you can do with triggers on google apps go here.

Some common errors:

  • the script’s namedValues attributes are not matching the form questions titles
  • forgetting to save and initialize the script
  • calendar names not matching

5 comments

  1. Hi Jessica,
    I’d love to use your Form->Calendar script which you blogged in your Doing More With Google Apps post. But it keeps throwing an error in line 36 of the code. Type Error: cannot read property “namedValues” from undefined. I’ve triple checked the match of the namedValues with the column names. Is the “e” variable declared somewhere else that I cannot find? Thanks for much for you time. Jim S swensonian@gmail.com

    Like

    1. Hi Jim,
      I’m happy that you are enjoying the code. In regards to line 36, the ‘e’ variable comes from the ScriptApp.newTirgger online 27. When that is call, it call triggers the function EmailGoogleFormData and passes in the event. This is an example of callback functions, if you want to learn more about how this happens you can check out this post Understand JavaScript Callback Functions and Use Them or if you want to learn how they are used with jQuery interactively you can check out Codeacademy’s Javascript. I hope this helps, enjoy 🙂

      Jessica

      Like

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