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.

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.

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

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

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:

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):

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!

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