Form Submission to Calendar Event

Since last we spoke I’ve…

1. Set a new personal marathon record (3:14:30)

2. Played some old school Zelda with my kids (SNES)

3. Written over a dozen newsletters on my second project.

Now to the sheets…here’s a video of the project we’ll be working on below

Form Setup

If you’ve been following me at all, you know I love a good spreadsheet and putting Apps Script to work behind the scenes.

We don’t have to limit ourselves to Google Sheets, though.

Today’s project has us combining Forms with Calendar. I’ve created a very basic Google Form here that when submitted will add an event to our Google Calendar.

The link above will prompt you to create your own copy that you can edit and receive submissions from.

You’ll also need to grab a calendar ID from your Google Calendars’ settings. It’s found about halfway down. If you’re using your main calendar, this is likely your Gmail address. If you’ve created a unique calendar, it will be a long string as shown in this picture:

Now we need to go do a little magic in Apps Script. We can open the script editor from the top right hamburger menu in Google Forms:

Now we simply need to:

1. Get all of the information that is submitted. And we only want the most recent submission.

2. Hold it in variables

3. Use the variables to create a new calendar event

Get the Info

We’ll use some built in functions to grab the active form, get the responses from it, grab the most recent set of responses, and then parse out each variable we’ll need.

FormApp.getActiveForm() does exactly what it sounds like.

getResponses() returns an array of all the responses. This is great, except that we don’t want to create multiple events for the same submission. We just want the most recent submission.

We can use pop() method on the responses to “pop” off the last item of the responses array.

Then, to get each of the item responses, we can use getItemResponses() to actually return the variables we need.

We’ll do a little bit of work on the start and end time to get them into the right format since we will have strings instead of Date objects from the responses.

And combining everything together, it will look like this:

let form = FormApp.getActiveForm()
let responses = form.getResponses()
  .pop()
  .getItemResponses()
let title = responses[0].getResponse()
let startTime = new Date(responses[1].getResponse() + " " + responses[2].getResponse())
let endTime = new Date(responses[3].getResponse() + " " + responses[4].getResponse())
let guests = responses[5].getResponse()

Now we have the info we need to add it to our calendar.

Add to Calendar

This part is fairly straightforward. We’ll use the calendar ID from our calendar and the getCalendarById() and createEvent() methods to create an event using the above variables.

Here’s what it will look like:

CalendarApp.getCalendarById("put your calendar's ID in between quotes here")
  .createEvent(
    title,startTime,endTime,
    {guests: guests, sendInvites: true}
)

We wrap everything up in a function called toCalendar() and then we need to create a trigger so it runs every time we submit something to the form.

Trigger on Submit

The last step is to set up a trigger that runs the toCalendar() function every time the form submits.

In the trigger settings, we set the function, event source and event type, and we’re good to go.

The video at the top walks through the whole process if you would like to see me go through all the steps of the project. Please take a second to subscribe to my YouTube channel while you’re checking that out!

Thank you so much!

It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!

Would love to say hi. Here are the best places to find me:

• 📺️ Videos on YouTube

• 💾 Products on Gumroad

• ✉️ Connections on LinkedIn

Need More Help?

• Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems.

• Hire an expert to help complete your next Google Sheets, Apps Script or Google Workplace project.