Turn a Google Form Response into a Google Calendar Event

Objective

Readers will be able to use Google Apps Script to take a response from a Google Form and turn it into a Google Calendar event.

WHY THIS MATTERS

Google Forms are incredibly useful for collecting information from users. By creating a form with date, start time, and end time fields we can use Google Apps Script to convert each new form response into a Google Calendar event. This is useful for creating low cost booking and reservation systems. In schools, teachers or administrators can set this up to book shared spaces like gymnasiums, auditoriums, etc. or shared resources like computer carts.

Prerequisite Knowledge

Advanced beginner JavaScript, Google Apps Script.

INTRODUCTION

I recently completed a freelance job for a music studio in England. The company rents studio space and wanted to use a Google Form to collect studio reservations. To make it easy to see which rooms were booked at particular times, the company wanted the form to create calendar events for each booking on an internal Google Calendar. 

Initial Setup

To get started we need a Google Form that collects a date, start time, and end time. The form can collect additional information, such as location, guests, descriptions, etc. that can be used in any of the various fields in a Google Calendar event. Below is the form I have created for this tutorial. Here is the spreadsheet containing form responses. Below you can demo the system by completing the form. Once the form is submitted a new event will be created on the calendar. The webpage will need to be refreshed before the newly created event is visible.

 

GEtting Started with the Script Editor

From the Spreadsheet Responses, click Tools and then Script Editor to open the Script Editor. If you have not used the Script Editor before, I recommend checking out Google's Overview of Google Apps Script.

Once in the Script Editor, name your project by clicking on "Untitled project" at the top left of the screen - just like you would name a Google Doc or Spreadsheet. Clicking on "Untitled project" will launch the "Edit Project Name" dialogue.

Moment.js

For some reason I have a hard time wrapping my head around the date object in JavaScript. Whenever I build scripts involving times and dates, I always leverage a JavaScript Library called Moment.js, which makes time make sense in JavaScript. That said, it is totally possible to create a form to calendar script without using the Moment.js library. Google Engineer, Eric Koleda posted on Google Plus how to use Moment.js with Google Apps Script back in 2013. The first step is to include Eric's Moment.js App Script library into your project. The library key is:

MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48

To include the library, click on "Resources" and then "Libraries"

Copy and paste the Moment.js library key into the "Find a Library" field. Click "Select" and then choose the latest version of the code and click "Save".


To load the library, include the following code at the top of your project.

// Load the library once.
var moment = Moment.load();

Global Variables

I have read that using global variables in JavaScript is not considered best practice, but it's something that I rely on frequently. I use the following global variable object to store data that I will need in multiple functions or that I want to be able edit quickly without hunting through my entire code.

var GLOBAL = {
  //the id of the form we will use to create calendar events 
  formId : "1YYngHERafN_DRqwww_Grw3fhGa9c2oBXBXxmyVm4nw0",  
  
  //the id of the calendar we will create events on
  calendarId : "p0prere6vsq4510kojq9v74vk8@group.calendar.google.com",
  
  //a mapping of form item titles to sections of the 
  //calendar event
  formMap : {
    eventTitle: "Event Title",
    startTime : "Event Date and Start Time",
    endTime: "Event End Time",
    description: "Event Description",
    location: "Event Location",
    email: "Add Guests",
    
  },
}

Form Id is the unique identifier associated with every Google Form. It is a string of letters, numbers, and underscores. To find your form's id, look at the form URL of the live form and copy the string of text in between the backslashes as shown below.

Functional Javascript

The key steps of this script are to first get an incoming form response and then second, turn the form response into a calendar event. We will write two functions to accomplish these two steps and wrap them in an onFormSubmit function, which will be triggered to run on form submit.

//function called by the form submission event
function onFormSubmit() {
  //store incoming form responses in an event object variable
  var eventObject = getFormResponse();
  //use the event object to create a new calendar event
  //store the event in a variable called event
  var event = createCalendarEvent(eventObject);
}

get form Response

Google Apps Script includes a Forms Service. Whenever I am writing scripts for Google Forms, I make sure that Google's documentation is never more than a browser tab away.

In the Script Editor, create a function called "getFormResponse":

function getFormResponse() {
  
  
}

The first thing we will do is declare some variables to store the form and all the responses so we can iterate through the responses.

  // Get a form object by opening the form using the
  // form id stored in the GLOBAL variable object
  var form = FormApp.openById(GLOBAL.formId),
      //Get all responses from the form. 
      //This method returns an array of form responses
      responses = form.getResponses(),
      //find the length of the responses array
      length = responses.length,
      //find the index of the most recent form response
      //since arrays are zero indexed, the last response 
      //is the total number of responses minus one
      lastResponse = responses[length-1],
      //get an array of responses to every question item 
      //within the form
      itemResponses = lastResponse.getItemResponses(),
      //create an empty object to store data from the 
      //last form response 
      eventObject = {};

Using the debugger, we can see where this code takes us:

Notice we have a "responses" array that contains two objects. Each object represents a form response, or one form submission. The length of the responses array is 2.0 because my test form has two response submissions. The lastResponse variable contains the object representing the most recent of the two form submissions. The itemResponses is an array of objects representing the lastResponse. The array has six objects representing the six questions in the sample form.

The next step is to iterate through each question in the last response and map it to the eventObject, from which we will create our calendar event. If you look back at the GLOBAL.formMap object we defined earlier, you will notice that each question title is mapped to a property key. For example, the formMap property eventTitle is paired to the value "Event Title", which is the title of a question in the sample form.

for (var i = 0, x = itemResponses.length; i<x; i++) {
    //Get the title of the form item being iterated on
    var thisItem = itemResponses[i].getItem().getTitle(),
        //get the submitted response to the form item being
        //iterated on
        thisResponse = itemResponses[i].getResponse();
    

Adding a debugger after these few lines of code and then running the code in debug mode will yield the following results:

Screen Shot 2016-02-09 at 3.46.47 PM.png

Remember, we are iterating through each question in the most recent form response (six items total). On the first run, where i = 0, the variable thisItem holds the value "Event Date and Start Time", which is the title of the first question. The variable thisResponse holds the value "2016-02-09 10:00", which is the response to the first question in the most recent form submission.

We will next use a switch statement to look at the item title (thisItem) and assign the response value (thisResponse) to a property in our eventObject. 

    //based on the form question title, map the response of the 
    //item being iterated on into our eventObject variable
    //use the GLOBAL variable formMap sub object to match form
    //question titles to property keys in the event object
    switch (thisItem) {
      case GLOBAL.formMap.eventTitle:
        eventObject.title = thisResponse;
        break;
      case GLOBAL.formMap.startTime:
        eventObject.startTime = thisResponse;
        break;
      case GLOBAL.formMap.endTime:
        eventObject.endTime = thisResponse;
        break; 
      case GLOBAL.formMap.description:
        eventObject.description = thisResponse;
        break;
      case GLOBAL.formMap.location:
        eventObject.location = thisResponse;
        break;
      case GLOBAL.formMap.email:
        eventObject.email = thisResponse;
        break;
    } 
  }

Throwing a debugger at the end of the for loop and running in debug mode will yield:

Notice that we have iterated over all six items, i = 6. Our eventObject is complete and we can now return this object and use it as the input for our next function, createCalendarEvent. The full function for getFormResponse() is:

function getFormResponse() {
  // Get a form object by opening the form using the
  // form id stored in the GLOBAL variable object
  var form = FormApp.openById(GLOBAL.formId),
      //Get all responses from the form. 
      //This method returns an array of form responses
      responses = form.getResponses(),
      //find the length of the responses array
      length = responses.length,
      //find the index of the most recent form response
      //since arrays are zero indexed, the last response 
      //is the total number of responses minus one
      lastResponse = responses[length-1],
      //get an array of responses to every question item 
      //within the form for which the respondent provided 
      //an answer
      itemResponses = lastResponse.getItemResponses(),
      //create an empty object to store data from the last form
      //response that will be used to create a calendar event
      eventObject = {};
  //Loop through each item response in the item response array
  for (var i = 0, x = itemResponses.length; i<x; i++) {
    //Get the title of the form item being iterated on
    var thisItem = itemResponses[i].getItem().getTitle(),
        //get the submitted response to the form item being 
        //iterated on
        thisResponse = itemResponses[i].getResponse();
    //based on the form question title, map the response of the 
    //item being iterated on into our eventObject variable
    //use the GLOBAL variable formMap sub object to match 
    //form question titles to property keys in the event object
    switch (thisItem) {
      case GLOBAL.formMap.eventTitle:
        eventObject.title = thisResponse;
        break;
      case GLOBAL.formMap.startTime:
        eventObject.startTime = thisResponse;
        break;
      case GLOBAL.formMap.endTime:
        eventObject.endTime = thisResponse;
        break; 
      case GLOBAL.formMap.description:
        eventObject.description = thisResponse;
        break;
      case GLOBAL.formMap.phone:
        eventObject.phone = thisResponse;
        break;
      case GLOBAL.formMap.email:
        eventObject.email = thisResponse;
        break;
    } 
  }
  return eventObject;
}

Create Calendar Event

Google Apps Script includes a Calendar Service, which we will use to create our calendar event.

In the Script Editor, create a function called "createCalendarEvent()" that takes the output from getFormResponse() as its input:

function createCalendarEvent(eventObject) {

}

Looking at the documentation for the createEvent() method we see that the method accepts four arguments, title, startTime, endTime, and an options object.

Let's create a variable for each of the createEvent() arguments and set them equal to the appropriate values in our eventObject input that we created in the getFormResponse() function. We will use Moment.js to make sure that our start time and end times are properly formatted as dates.

  //Get a calendar object by opening the calendar using the
  //calendar id stored in the GLOBAL variable object
  var calendar = CalendarApp.getCalendarById(GLOBAL.calendarId),
      //The title for the event that will be created
      title = eventObject.title,
      //The start time and date of the event that will be created
      startTime = moment(eventObject.startTime).toDate(),
      //The end time and date of the event that will be created
      endTime = moment(eventObject.endTime).toDate();
  //an options object containing the description and guest list
  //for the event that will be created
  var options = {
    description : eventObject.description,
    guests : eventObject.email,        
    location : eventObject.location
  };

To test this section of code we can run the entire script from the onFormSubmit function, which will first run the getFormResponse and pass the eventObject return value into our createCalendarEvent() function.

function onFormSubmit() {
  var eventObject = getFormResponse();
  var event = createCalendarEvent(eventObject);
}

Using debug mode we get:

The variables for the createEvent method are all set. Our title is set to "My amazing event", which is the latest response to the event title item on our form. The start and end times are properly formatted as dates, which correspond to the responses for the start and end time questions in our form. Our options object contains a description and a guest email address, also corresponding to the latest form response.

It is good practice to wrap the createEvent() method in a try catch, that way if for some reason there is an invalid email address, the script will fail more gracefully. Notice how in the catch, we delete the guest property from our options object.

try {
    //create a calendar event with given title, start time, 
    //end time, and description and guests stored in an 
    //options argument
    var event = calendar.createEvent(title, startTime, 
                                     endTime, options)
    } catch (e) {
      //delete the guest property from the options 
      //variable, as an invalid email address with cause
      //this method to throw an error.
      delete options.guests
      //create the event without including the guest
      var event = calendar.createEvent(title, startTime, 
                                       endTime, options)
      }
  return event;   

All together the createCalendarEvent() looks like:

function createCalendarEvent(eventObject) {
  //Get a calendar object by opening the calendar using the
  //calendar id stored in the GLOBAL variable object
  var calendar = CalendarApp.getCalendarById(GLOBAL.calendarId),
      //The title for the event that will be created
      title = eventObject.title,
      //The start time and date of the event that will be created
      startTime = moment(eventObject.startTime).toDate(),
      //The end time and date of the event that will be created
      endTime = moment(eventObject.endTime).toDate();
  //an options object containing the description and guest list
  //for the event that will be created
  var options = {
    description : eventObject.description,
    guests : eventObject.email, 
    lcoation: eventObject.location,
  };
  try {
    //create a calendar event with given title, start time, end time,
    //and description and guests stored in an options argument
    var event = calendar.createEvent(title, startTime, 
                                     endTime, options)
    } catch (e) {
      //delete the guest property from the options variable, 
      //as an invalid email address with cause this method 
      //to throw an error.
      delete options.guests
      //create the event without including the guest
      var event = calendar.createEvent(title, startTime, 
                                       endTime, options)
      }
  return event;   
}

Set a Trigger

We want the onFormSubmit trigger to run every time a new form response comes in. To do so we will create a trigger by clicking on the Current project's triggers icon. 

Next click the link "No triggers set up. Click here to add one now."

Choose the onFormSubmit function in the first drop down. Next choose "From spreadsheet" from the next drop down. Choose "On form submit" from the third drop down and then click "Save." Now whenever a new form submission comes in, the onFormSubmit function we've written will run. 

Please note that whichever email address you are logged into when you set this trigger is the email address that will run the script. That means:

  1. The email address must be a collaborator on the form otherwise the script will throw an error when it tries to open the form by the form id.
  2. The email address must have editing rights to the calendar otherwise the script will throw an error when it tries to open the calendar by the calendar id.

The lines of code that will throw errors if the user account running the script does not have editing rights to the form and or calendar are:

var form = FormApp.openById(GLOBAL.formId),
var calendar = CalendarApp.getCalendarById(GLOBAL.calendarId),

Conclusion

I really enjoyed putting this build together. I hope that this post helps burgeoning scripters.

Full Code:

//Load the Moment.js library once.
var moment = Moment.load();

var GLOBAL = {
  //the id of the form we will use to create calendar events 
  formId : "1YYngHERafN_DRqwww_Grw3fhGa9c2oBXBXxmyVm4nw0",  
  
  //the id of the calendar we will create events on
  calendarId : "p0prere6vsq4510kojq9v74vk8@group.calendar.google.com",
  
  //a mapping of form item titles to sections of the calendar event
  formMap : {
    eventTitle: "Event Title",
    startTime : "Event Date and Start Time",
    endTime: "Event End Time",
    description: "Event Description",
    location: "Event Location",
    email: "Add Guests",
    
  },
}

function onFormSubmit() {
  var eventObject = getFormResponse();
  var event = createCalendarEvent(eventObject);
}

function getFormResponse() {
  // Get a form object by opening the form using the
  // form id stored in the GLOBAL variable object
  var form = FormApp.openById(GLOBAL.formId),
      //Get all responses from the form. 
      //This method returns an array of form responses
      responses = form.getResponses(),
      //find the length of the responses array
      length = responses.length,
      //find the index of the most recent form response
      //since arrays are zero indexed, the last response 
      //is the total number of responses minus one
      lastResponse = responses[length-1],
      //get an array of responses to every question item 
      //within the form for which the respondent provided an answer
      itemResponses = lastResponse.getItemResponses(),
      //create an empty object to store data from the last 
      //form response
      //that will be used to create a calendar event
      eventObject = {};
  //Loop through each item response in the item response array
  for (var i = 0, x = itemResponses.length; i<x; i++) {
    //Get the title of the form item being iterated on
    var thisItem = itemResponses[i].getItem().getTitle(),
        //get the submitted response to the form item being
        //iterated on
        thisResponse = itemResponses[i].getResponse();
    //based on the form question title, map the response of the 
    //item being iterated on into our eventObject variable
    //use the GLOBAL variable formMap sub object to match 
    //form question titles to property keys in the event object
    switch (thisItem) {
      case GLOBAL.formMap.eventTitle:
        eventObject.title = thisResponse;
        break;
      case GLOBAL.formMap.startTime:
        eventObject.startTime = thisResponse;
        break;
      case GLOBAL.formMap.endTime:
        eventObject.endTime = thisResponse;
        break; 
      case GLOBAL.formMap.description:
        eventObject.description = thisResponse;
        break;
      case GLOBAL.formMap.location:
        eventObject.location = thisResponse;
        break;
      case GLOBAL.formMap.email:
        eventObject.email = thisResponse;
        break;
    } 
  }
  return eventObject;
}

function createCalendarEvent(eventObject) {
  //Get a calendar object by opening the calendar using the
  //calendar id stored in the GLOBAL variable object
  var calendar = CalendarApp.getCalendarById(GLOBAL.calendarId),
      //The title for the event that will be created
      title = eventObject.title,
      //The start time and date of the event that will be created
      startTime = moment(eventObject.startTime).toDate(),
      //The end time and date of the event that will be created
      endTime = moment(eventObject.endTime).toDate();
  //an options object containing the description and guest list
  //for the event that will be created
  var options = {
    description : eventObject.description,
    guests : eventObject.email,
    location: eventObject.location,
  };
  try {
    //create a calendar event with given title, start time,
    //end time, and description and guests stored in an 
    //options argument
    var event = calendar.createEvent(title, startTime, 
                                     endTime, options)
    } catch (e) {
      //delete the guest property from the options variable, 
      //as an invalid email address with cause this method to 
      //throw an error.
      delete options.guests
      //create the event without including the guest
      var event = calendar.createEvent(title, startTime, 
                                       endTime, options)
      }
  return event;   
}