Send Google Forms to Google Calendar

Code a Google Calendar Event from Google Forms

For my Coding Google Forms workshop I thought it would be super easy to make a tutorial on how to have people sign up for something on a Google Form with a Date and Time and then create a Google Calendar event. It is easy if I decide the time and hard code that myself, but not so easy if they submit the time to the Google Form. Many many Google Searches turned up with fragments of information that did not solve the problem. I finally figured it out and here is the solution.


Try it Out

First, try filling out this Google Form I coded. Choose a date and a time and then go check your calendar that it made the appointment.




The Code

Thank you to all the coders who put their code out on the internet for me to steal. This is my primary way of getting started with a coding project, stand on the shoulders of others! So here is my code to get you started.

function createEvent() {
var form = FormApp.getActiveForm();
var cal = CalendarApp.getDefaultCalendar();
var responses = form.getResponses();
var len = responses.length;
var last = len – 1;
var items = responses[last].getItemResponses();
var email = responses[last].getRespondentEmail();
var name = items[0].getResponse();
var bring = items[1].getResponse();
var date = items[2].getResponse();
Logger.log(date);
var replace = date.replace(/-/g,”/”);
Logger.log(replace);
var start = new Date(replace);
Logger.log(‘start ‘+start);

//Logger.log(newStart.getHours());
var endHours = 2+0+start.getHours();

//Logger.log(start.getDay());
var day = start.getDate();
var minutes = start.getMinutes();
var year = start.getFullYear();
var month = start.getMonth();
var hours = start.getHours();

var d = new Date(year, month, day, endHours, minutes);
Logger.log(d);

var event = cal.createEvent(‘Class Party ‘+name+’ brings ‘+bring, start, d)
.addGuest(email)
.setDescription(name+’ you will be bringing ‘+bring+’ to the party.’);

GmailApp.sendEmail(email, name+’ a Google Calendar invite has been created for you’, name+’ You filled out the Google Form for the date of ‘+start+’. Check your Google Calendar to confirm that you received the invite.\n’);

}

The Problem

When you create a question in Google Forms that asks for date and time it is NOT in the format that JavaScript needs to create a date. So when you use new Date(date) it sets it for December 31st 1969!! Google Forms puts the date with dashes instead of slashes, so that is the first problem to solve.

I defined a variable called date for the Google Form question that collected the date.

Replace

I then used the replace method to swap out the dash for the slash. I wish I could remember which of the many websites I searched where I found that nugget (THANK YOU!).

var replace = date.replace(/-/g,”/”);

Make a Date

Once the formatting of the Google Form date is better, now use JavaScript to make it a date from the text string. This gives me my start time for my event.

var start = new Date(replace);

If I am making an all day event, then I am good to go! However, if you want to make an event at a specific time you also need the end time. This was a challenge. I kept trying to add 2 to the hours and it would just give me, if the time was 10am, 102 where it concatenated the 2 instead of adding the 2 to make 12. When I use a prompt in a script that asks for a number and returns it as a text string I am unable to add or subtract. Usually parceInt() will switch the text string to an integer value and then I can do the math I need to on it. No dice with date.getHours();

Every time I used + to add it was instead using concatenate no matter what trickery I did to convert to integer values or perform math on the hours. Out of desperation I double checked that Logger.log(6+2); would indeed give me 8, so the plus does do addition if it wants to! I think did
Logger.log( 6+2+start.getHours()); and it returned a number!!! So I switched it to a math problem of adding the number of hours the meeting would be to zero and then adding the start.getHours(). I am sure there is a more elegant way to do this, but it works so I don’t care.

var endHours = 2+0+start.getHours();

Since my start date is working as a legit date I extracted out each part of the date.

var day = start.getDate();
var minutes = start.getMinutes();
var year = start.getFullYear();
var month = start.getMonth();
var hours = start.getHours();



Construct the End Date

To create a new date you can do
new Date(year, month, day, hours, minutes)

So I created a new date to be my end date by setting all the pieces together. Notice for hours it is the modified start hour plus 2 hours.

var d = new Date(year, month, day, endHours, minutes);

Create Calendar Event

The method createEvent uses the title, start time, and end time. You can define the title separately or construct it within the method. But my variable start is a JavaScript date for the start time and my variable d is a JavaScript date for the end time. Save and run your code!!

var event = cal.createEvent(‘Class Party ‘+name+’ brings ‘+bring, start, d);

Trigger

Don’t forget to set a trigger for your Google Form to automatically create the calendar event on form submit.



Add-Ons and Templates

Digiprove sealCopyright secured by Digiprove © 2018

Online Workshop

Online Go Slow Workshop
*Jan 7: Google Classroom Tips and Tricks
*Jan 14: Intro to Google Forms

Register at
alicekeeler.com/goslow