In this blog post, we’re going to look at how to use Google App Script to create a Google Calendar with a Google Form. This tutorial will build on some of the same code and concepts we’ve been working with in some earlier blog posts, so feel free to check those out:
The first step in creating a Google Calendar event using a Google Form is to actually generate the form that will accept user input. You should create a form using the time and date input fields. These fields will format time and date inputs in a way that makes it easier to create a new JavaScript Date object later.
As in previous tutorials, we are going to open up the script editor in the spreadsheet that holds the form responses. We are going to write a function that takes the spreadsheet event as a parameter and creates the variables that we need to populate our Google Calendar event. For this particular example, we are going to need to create variables to hold three required parameters of a calendar event: a name or description, a start time date object, and an end time date object. To extend this script one step further, we are also going to ask for a longer description of the event, but this is optional, as are location and some other pieces of information.
function myFunction(e){
var eventName = e.values[1];
var eventDate = e.values[2];
var startTime = e.values[3];
var endTime = e.values[4];
var eventDescription = e.values[5];
}
When we create the Google Calendar event, we will need to pass its constructor function two DateTime objects. To create these two DateTime objects we will need to parse the variable we just created. To do the parsing we’ll use the JavaScript built-in date constructor:
var startDate = new Date( );
The JavaScript date object is pretty forgiving, but we will need to concatenate together the eventDate and startTime/endTime string we got from the form submission. Below our initial function definitions, we’ll include the next two lines of code to create date objects from our form variables:
startDate = new Date( eventDate + " " + startTime); endDate = new Date( eventDate + " " + endTime);
Now that we have all of our objects and variables created, we can make the method call to create the Google Calendar event. The first step in that process is using the CalendarApp to access the default calendar for the Google account you’re using.
Usually, I like to create a variable that stores Google Calendar that I’ll be working with. This just keeps the code clean so that we’re not using a lot of different stringed method calls. All of the methods we’re working with belong to the CalendarApp class in Google Apps Script. Using the CalendarApp, we can perform CRUD operations on any calendar you have access to, but it very easy to just get a default calendar for the associated Google account.
var myCal = CalendarApp.getDefaultCalendar( );
You can also get calendars using some of the CalendarApp methods available
The last part of this function will be a call to the createEvent method on our myCal object with all of our variables to create the Google Calendar event. We can also pass along our eventDescription variable in a JSON object as an additional and optional field in the calendar event:
myCal.createEvent( eventName, startDate, endDate ); //Optional method call with description in a JSON object
There are other options that you can pass besides the description. For example, you can specify a location or whether or not to send invites to the people who sign-up.
myCal.createEvent( eventName, startDate, endDate, {
description: eventDescription,
sendInvites: true,
location: "New Business Building"
}
);
You can find out more about the advanced options using the official Google documentation.
Hi
Thanks. I want to create a form that will update x number of calendars depending on the x selected when the form is submitted.
You should then just be able to add an additional input to store the number of calendars and then loop through the number of calendars you want to update. Do you have a script you are working on that I could see?
Thanks. Appreciated. I am using formMule. There are a new version which only gives you email merge when form is submitted. Then there’s an old version which update and event on Google calendar when the form is submitted as well. I will get back with the link to the script. Thanks again.
Here is the link to the new and old formMule script. I cannot figure out how to or if I can use it to update more than one calendar from a form input. http://cloudlab.newvisions.org/scripts/formmule
That looks like a pretty intense plugin. I think you’d have better luck writing what you want from scratch than trying to modify that.
Thanks for the great information!
Hopefully you can help me with a couple of questions. I almost have what I need but I can’t seem to figure out two details: (1) I would like to make it so that there is a default end date/time (always two hours later than the start date/time) built into the script and therefore NOT entered manually at all because (2) I would also like to add recurring events with two specific options–a one-time event and another option that allows me to repeat the event 12 times. Does that make sense?
Is this something you would be able to provide some suggestions with? I haven’t had any luck in my efforts up to now and with only limited programming experience.
Thanks,
Eric
Great question Eric! Thanks for asking. To answer your first question, you just need to do some calculations with JavaScript to create that end date. First, you’d take the date the user submits and turn that into a JS date object like this:
This line will turn the user submission into a date, then we need to parse it to turn that into milliseconds:
This line will turn it into a large number representing the number of milliseconds since Jan 1, 1970, which is really irrelevant. We can then take that number and add to it the number of milliseconds in two hours, so 120000. Then we’ll create a new date from those two numbers:
That should give you an end time that is two hours after the date/time the user submits. I’m going to reply to your 2nd question in a different response.
For the second part of your question, you would just want to use some conditional logic to decide when to create a single event or when to create a recurring event. If this is something the user will select, you will need to add some radio buttons where the user will select between something like: “one-time” and “recurring”
Then just write an if/else statement like this:
var frequency = e.values[usersubmission]; if ( frequency === "recurring"){ //create the recurring events here } else { //do what you have already written to //create one event }Check out this link on creating recurring events: https://developers.google.com/apps-script/reference/calendar/calendar-app#createeventseriestitle-starttime-endtime-recurrence
Thank you for your quick response! I’ll give it a try.
Sure thing! Thanks for reading.
Hello, I have my script to create a document, add the people to contacts and then add to calendar. However, for some reason when I add the calendar part everything else breaks and the calendar does not get updated… let me know if you need a link to my form and spreadsheet…this is a test but would like to use it in the real world.
Hmmm…that sounds like an interesting script. What kind of error do you get? Also, sharing them with me might help debug.
Here is the folder where I am doing this…you’ll see test 1 and test 2 you can use either one they both have the same script.
You’ll see a lot of things commented out…
So the way that I have working until now is that when the user submits the google form it will get some info from there and to a google doc inside of the testing folder. Then it will get the info and add to google contacts in a group and then it should send info to calendar event.
So it works for creating the document, and adding contact but if I add the calendar part it stops working and also doesn’t create an event.
I don’t have things in functions just an onSubmit function that then does everything else.
Here is the link…
https://drive.google.com/folderview?id=0B12bPVQ6z2-xMWRkM2YzNmMtNzU1Yi00ZDdmLTgzMDYtMzg4MTdjMTU5MmI2&usp=sharing
Hello-
I think I have finally gotten the code together and it works for me – but not for anyone else completing the form. Did I miss understand the intent, or do I have something awry in the code (pasted below)? Any help is appreciated!
function myFunction(e) {
var eventName = e.values[2];
var eventDate = e.values[3];
var startTime = e.values[4];
var endTime = e.values[5];
var eventDescription = e.values[6];
var startDate = new Date( );
startDate = new Date( eventDate + ” ” + startTime);
endDate = new Date( eventDate + ” ” + endTime);
var myCal = CalendarApp.getDefaultCalendar( );
myCal.createEvent( eventName, startDate, endDate, { description: eventDescription});
}
What is happening when people fill out the form? By getting the default calendar, you are grabbing a reference to the default calendar for the account that authorized the script (presumably your Gmail). So, I’d imagine, based on this code, that when other people add events, they are showing up on your calendar. Is that correct?
If you want to write things to a shared calendar, you will need to get that calendar by id (assuming you have read/write privileges): https://developers.google.com/apps-script/reference/calendar/calendar-app#getcalendarbyidid
I hope that helps, but if not, just comment back with more details and I’ll see what I can do. Thanks for reading!JE
Thanks so much for the response – and I see why the code does not do what I was hoping. What I actually wanted to do was to add the event to the respondents calendar, but I would not have read write access to it. Is it possible to add them to an event I have rights to, but as a guest, and send an invite they could add to their own calendar? (I know I want a lot!!)
These are invites to a training event they are signing up for….
Thanks-
Bethany
Hmmmm….that is an interesting use case. So, it looks like you could use this to get the event: https://developers.google.com/apps-script/reference/calendar/calendar#getEventsForDay(Date,Object)
And then use this to add people as a guest by email, but I’m not sure what that would do in terms of adding to a calendar or sending an email: https://developers.google.com/apps-script/reference/calendar/calendar-event#addGuest(String)
I used to run all of my training signups through Google Forms when I worked in Higher Ed, but I never tried what you’re looking to do. Good luck. Let me know how it goes and if I can help along the way. Thanks for reading!
What exactly should be the code I need to paste into the script editor if I only need the title, start and end dates?
I keep on trying to enter the code, removing the description variables but it says that there’s an error on line 8:
startDate = new Date( eventDate + ” ” + startTime);
I have no background in any computer language. But I really need this for my work.
What does the error say specifically? That would help.
Is there something I can add into the script that will send the ‘submitter’ the event to add to their Google calendar?
Yes, it looks like you can pass in an advanced parameter to make it send invites: https://developers.google.com/apps-script/reference/calendar/calendar-app#createAllDayEventSeries(String,Date,EventRecurrence,Object)
I’ll update the code on the example to include this as an option. Thanks for asking.
Thank you! Would I just need to add
description: eventDescription,
sendInvites: true
to the end of the script? I am trying to set up a form for my school that will allow students to sign up for an extra study period. I would like to have an event on my calendar that shows the students name and date on my calendar while also sending the event with name(study hall) to their calendar.
Thank you for all the help!
That should do it, but I would test before you release into the wild
When I run it I’m getting the Reference Error:
“myCal” is not defined. (line 2, file “Code”)
This does not add event to calendar of person who is filling the form. How to do that ?
TBH, I’m not sure you can. If your org has a GSuite account, you can lock down the form to only users of your domain, and maybe use something like this: https://stackoverflow.com/questions/26425627/retrieve-the-e-mail-address-of-a-google-form-respondent-without-asking-for-their
Sorry I don’t have a better answer, but you’re the first person to want to do this.
Thanks I will give the link a shot, but just for the record, I too would like to be able to do this.
Bethany
Thanks for posting. Since there seems to be some demand for this, and it seems like a reasonable thing for people in a Google org to want to do, I’ll give it a shot in the coming weeks.
When running the code, I am having trouble getting the values from the spreadsheet. How can I map my different columns to the different values? Also, what prevents this code from only reading the most recent row added rather than all of the rows?
It might help if you post some code to the comments here so I can see exactly what you’re doing. Arrays in JavaScript are zero indexed, so the first item is actually array[0]. If you use Logger.log to do some basic logging that can help you figure out how your columns are structured and what indexes you need to pass into the array to access them. As for the rows question, when we use e.values to access values from a form submission, we only have access to the values passed into the form, not anything else in the spreadsheet. We could read the last row, but this case, at least what my example shows, is more like looking at the values submitted to the form before they are added to the spreadsheet. Post back a code example if you still want some help.
I managed to get all aspects of my code, mostly a mash up of your code, running and have encountered an issue with the date parsing. When selecting a date, say 2020/09/10 or 2020/09/11, on the Google Form, a calendar entry is created for 2020/10/09 or 2020/11/09. I understand what is happening but cannot figure out how to correct it. Writing the startDate value to a Google Doc in the same script gives the date as 10/09/2020 in one instance and Fri Oct 09 2020 00:00:00 GMT+0200 (South Africa Standard Time) in another (the latter occurred in the last test). I guess this has to do with the calendar time zone rather than a JS date parsing issue? Or could it be browser dependent (unlikely as I use Chrome for all the tests).
Sorry for the late reply, but I’ve been pondering this. Yeah, I could see that getting tricky. It might involve converting whatever your local date format is into what the Apps Script might expect in a cleaner way, although I can’t say that reading the comment I’m 100% sure on how to proceed of even how best to test and isolate this.
What you might consider looking into is converting the timestamp to a JS date object, then formatting it based on the locale you want written in the document and the format you want the actual date part to be using the toLocaleString method. Feel free to post back with your progress if you want to talk this through some more.
Thanks for reading,
Jeff