For a long time now I have been trying to find an easy way for the volunteers under my command to submit vacation requests. I searched high and low for a PHP scheduler but nothing suited my needs.
I the realized that google had a calendar API which would allow external forms to add events to my calendar, but sadly this didn’t pan out either till I found out that Google also had its own form generator. But sadly again, doing searched on how to add an event to Google Calendar from the form turned up either useless or empty.
I did manage to find a script that came close to what I wanted. Bruce Burge create a great article on how to add events, however his code was lacking for what I wanted, but not by much. His code is the base for my code found in this article. The major modification of course was to remove the code which sets the start time to 0:00 and end time to 23:59, since I wanted the absence tracker to show their shift and not the full day. Some tweaks to what gets added to the comments and description as well as the title were also changed.
Another feature I wanted was to have an email sent to me when someone submitted the form. Amit Agarwal has a great tutorial on this, however again, I wanted to make a few modifications based on my needs. The second script is a slightly modified version of his code, namely making the subject dynamically created based on the values in the spreadsheet/form.
Now that I have given credit to the original creators, and given an explanation as to what I did to their code to make it better, lets get on with how its done:
The First Step – Create Your Form
I made the mistake of making the spreadsheet first, although you can do this, it is much easier to create the form, as once you are done with the form, it will automatically create the spreadsheet for you.
For my form there were a total of 6 fields:
- Name (Text, Required)
- Absence Type (Check Boxes, Required)
- Shift Start (Date, Required)
- Shift End (Date, Required)
- Reason (Chose from a list, Required)
- Comments (Paragraph Text, Not Required)
Now for the Shift Start and End fields I also selected “include time” as I wanted the calendar to show only time of the event I was creating.
You can have more columns if you like, however for the Calendar you need to have a Start and Stop time, as well as fields to create the Title and what you want to put into the description.
Accessing the Spreadsheet and Adding Your Code
[notification type=”info”] It doesn’t matter what you name your Script. When it asks you for Authorization, please authorize the script to run or it will not work. [/notification]Once you have completed your form, click on “View Responses“, this will bring up your form generated spreadsheet:
Once you are on your spreadsheet, you will select “Script editor” under “Tools”
Copy and paste the code below overwriting anything that is currently in the Code.gs file, this created the calendar event:
NOTE: Your Calendar ID needs to have the “@group.calendar.google.com” at the end of it
[php] //this is the ID of the calendar to add the event to, this is found on the calendar settings page of the calendar in question//Look for “Calendar Address:” and the ID shows up beside it.
var calendarId = “CALENDAR_ID_GOES_HERE”;
//below are the column ids of that represents the values used in the spreadsheet (these are non zero indexed)
//Column containg the Start Date/Time for the event
var startDtId = 4;
//Column containg the End Date/Time for the event
var endDtId = 5;
//Column containg the First Part of the Title for the event (In this case, Name)
var titleId = 2;
//Column containg the Second part of the Title for the event (In this case, Absence type)
var titleId2 = 3;
//Column containg the Comments for the event
var descId = 7;
//Column containg the Time Stamp for the event (This will always be 1)
var formTimeStampId = 1;
function getLatestAndSubmitToCalendar() {
//Allow access to the Spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
//Removed setting of Hour and Minute for the Start and End times as these are set i our form
var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
//Create an addition to the Description to included who added it and when
var subOn = “Added :”+sheet.getRange(lr,formTimeStampId,1,1).getValue()+” by: “+sheet.getRange(lr,titleId,1,1).getValue();
//Setting the Comments as the description, and addining in the Time stamp and Submision info
var desc = “Comments :”+sheet.getRange(lr,descId,1,1).getValue()+”n”+subOn;
//Create the Title using the Name and tType of Absence
var title = sheet.getRange(lr,titleId,1,1).getValue()+” – “+sheet.getRange(lr,titleId2,1,1).getValue();
//Run the Crete event Function
createEvent(calendarId,title,startDt,endDt,desc);
};
function createEvent(calendarId,title,startDt,endDt,desc) {
var cal = CalendarApp.getCalendarById(calendarId);
var start = new Date(startDt);
var end = new Date(endDt);
//Manually set the Location, this can be modified to be dynamic by modifying the code if need be
var loc = ‘Computer Centre’;
//Set the Options, in this case we are only using Description and Location, as we do not need Guests or sendInvites
var event = cal.createEvent(title, start, end, {
description : desc,
location : loc
});
};
[/php]
In order to make it so that the form also sends you an email, create a new code file:
Name this files something like “Email” so you remember what it does. Then copy and past the below text over writing anything that Google automatically adds:
[php] //Left Original Coder’s info as I just modified the file/* Send Google Form by Email v2.0 */
/* For customization, contact the developer at amit@labnol.org */
/* Tutorial: http://goo.gl/7Ujaqw */
//Added some variables that we will use to pull info for the subject
var startDtId = 4;
var titleId = 2;
var titleId2 = 3;
function SendGoogleForm(e)
{
try
{
// Added code to allow it to read the spreadsheet
// this was taken from the previous script as we know it works
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
// You may replace this with another email address
// i.e. var email = “email@domain.com”;
var email = Session.getActiveUser().getEmail();
// Customized the Subject to pull the Name, Absence REason, and Start date from the database
var subject = sheet.getRange(lr,titleId,1,1).getValue()+” – “+sheet.getRange(lr,titleId2,1,1).getValue()+” – “+sheet.getRange(lr,startDtId,1,1).getValue();
var s = SpreadsheetApp.getActiveSheet();
var columns = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = “”;
// Only include form fields that are not blank
for ( var keys in columns ) {
var key = columns[keys];
if ( e.namedValues[key] && (e.namedValues[key] != “”) ) {
message += key + ‘ :: ‘+ e.namedValues[key] + “nn”;
}
}
// This is the MailApp service of Google Apps Script
// that sends the email. You can also use GmailApp for HTML Mail.
MailApp.sendEmail(email, subject, message);
} catch (e) {
Logger.log(e.toString());
}
}
[/php]
Now we need to add the triggers so that when we submit our form, it adds it to Google Calendar and sends out an email. To do this, while still in the Coding section, Select Resources in the top menu:
Then make sure that you have two triggers set like the image below (Click the link to Add them as they will not automatically be there)
Notes to Keep in mind when Testing Your Form
It can take up to five minutes for the entry to be added to your spreadsheet, once it shows there you will see an entry in your Google Calendar (the one you provided the ID for, don’t forget to change this), and you should receive an email as well with all the form fields.
If for what ever reason something doesn’t work. in your triggers, set the notification to Immediate notification, not once a day at midnight.
If you have any questions, I will do my best to answer them.
Plans for Future Posts
Now that I have had a bit of experience with coding for Google Forms, I will try and create a better version, one that will allow you to create a booking app, one that would check if the time is already taken in the calendar (prevents double booking) as well as allows Invites and Guests (the full options abilities).
That is so cool… I will have to look into that and start using my google calendar more as I am now finding time an the issue with the 500 project is taking up a lot of time including the 2 quild websites I run probono.
I did a lot of poking around to get this done for work, trying to think how I can implement it into other projects. Their API is very extensive and some folks have created a PHP library for it. It basically allows you to used anything from Google Drive to Google Calendar in your PHP Projects… I have a lot more research to do, but wow is it extensive.
Matthew,
I wanted to thank you for sharing your code! I was able to implement it, with minimal modifications, to help save me a bunch of time and frustration. Thank you! See how I used it here: http://tjregister.wordpress.com/2014/07/23/using-google-apps-to-automate-scheduling-of-student-reassessment/
Thanks Mr. Matthew since i got your coding, i was frustation but now the good way appear beside me 🙂
Thanks so very much!
Just wondering about your note: “… one that would check if the time is already taken in the calendar (prevents double booking).”
Any update?
Sadly no update on this, been busy with other projects. Once I do have an up tho I will post it.
Any update?? This would be an awesome feature!!
Thank you! Is there a way to only add event to calendar by pressing a button (other than Submit)? In my case, people will submit a form for a transportation request (school setting). Transportation supervisor will review form and approve if there are no conflicts. Would love to be able to have supervisor click a button that would then add to calendar. Thanks!
Sadly my knowledge on the subject is limited to what I had researched. From what I gather you are looking for a way to add the initial requests into a queue for approval, then have the supervisor approve hthem and have then add it to the calendar. I am sure there is a way that you could process it afterwards as there are several options within the coding. FOr isntance I have it process the code on submition, where you could run it manualy afterwards via the press of a button. Sorry I could not be much more help.
Thanks for your code! I follow the instructions. But my google calendar shows the starting date is 1/1/1970! The end date is correct. Any suggestion? Many thanks!
Check to make sure your variables are named correctly… 1/1/1970 is 0 Unix Time or epoch time, this is usually entered when somehting is not correct it defaults to 0 (or 1/1/1970)
Having a bit of difficulty with this Matthew, doesn’t want to output to the calendar no matter what I try 🙁
I know it probably a dumb question, but did you change the CALENDAR_ID_GOES_HERE part to be the correct calendar ID? If you have modifiec the code, make sure that you have the proper syntax. My scripts are still woking for my internal site, so Google hans’t change anything that would make it deffective.
Yeah, the calander_id_part is fine, I took the calendar ID and put it in.
The only thing I changed is due to this error:
Syntax error. (line 38, file “Code”)
Which, I assume is due to the ? after the } rather than a ;
Other than that, its the same.
I fixed the code. Not sure what to say, others have reported it works. Wish I could be more help, but I am not as fluent in the coding as I would like to be.
My calendar url is a bit different though, rather than just being something like: gvj9ncovistn231u8lg3q90fd@@group.calendar.google.com, it has a URL in front of it and looks like: myurl.com_gvj9ncovistn231u8lg3q90fd@@group.calendar.google.com. I’ve tried using it like this and by removing the URL from it, but still no joy 🙁
There should only be one @ symbol, and mine does not have the myurl.com_ part in it, simply a random_generated_key@group.calendar.google.com. The Calendar ID can be found by clicking on “Calendar settings” next to the calendar you wish to display. The Calendar ID is then shown beside “Calendar Address”.
http://i61.tinypic.com/21me060.jpg
is what mine looks like, the blacked out part it the URL at the front.
Simply fantastic work, thank you! I plan on having our medical school use this so students can submit lunch events to a shared calendar so all the students can see the events going on around campus. This is the perfect tool for the job!
Have you developed booking system yet? Any pointer to that post?
I am also looking for booking appointment via Google form, and thought of using calendar, but not working for me till now.
Sadly know, I have been busy with other projects I haven;t had time to learn any more coding. Good luck in your search. If you find something, let us know.
Hi!
I´ve been trying to make it work but the event is not created in the calendar, the answers get in the spreadsheet and the mail is send, but the calendar don´t creates event,i dont know what i´m doing wrong,
Must the calendar be public?
Must i add the id with the @group.calendar.google.com ?
Anyone with the same problem?
Thnks a lot!
I know for sure you have to have the @group.calendar.google.com in the ID. As fro the calendar being Public, I can;t remember… I believe Yes, if you want others to view it.
I have exactly the same question with you. After form submitted, spreadsheet filled and email sent, but cannot create event due to Line 48 in the script. Thanks for answer.
does it tell you what error is in regards to? If so can you copy and paste the error here for us to view.
From : apps-scripts-notifications@google.com
Content:
Your script, absreq, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here.
A table shown below:
Start Function Error Message Trigger End
10/24/14 1:36 AM getLatestAndSubmitToCalendar TypeError: Cannot Call null ?createEvent?? (line 48, file “code”) formSubmit 10/24/14 1:36 AM
DId I type my Calendar ID wrongly? Any special format for that? THanks
It is the Calendar ID problem. Ive fixed it. THank you so much.
That would definitely cause that error, as without it the function createEvent would return without a value.
Thanks, Matthew. Looking forward to your booking system. GS is really user friendly .
I am having problems dynamically populating a location.
I added the var = locId = 5 (for the fifth column)
I added var loc = sheet.getRange(lr,locId,1,1).getValue();
I added var loc = new Location(loc);
My assumption is that either the location needs to be some format of info or that I messed up that last bit of code. Some correction love would be much appreciated.
Not sure, I haven’t had time to mess with customization since I did this post. Maybe another reader might have an answer.
Here is how I solved it!
//Column containg the Start Date/Time for the event
var startDtId = 4;
//Column containg the End Date/Time for the event
var endDtId = 5;
//Column containg the First Part of the Title for the event (In this case, Name)
var titleId = 2;
//Column containg the Second part of the Title for the event (In this case, Absence type)
var titleId2 = 3;
//Column for place
var locId = 6;
//Column containg the Comments for the event
var descId = 7;
//Column containg the Time Stamp for the event (This will always be 1)
var formTimeStampId = 1;
function getLatestAndSubmitToCalendar() {
//Allow access to the Spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
//Removed setting of Hour and Minute for the Start and End times as these are set i our form
var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
//Create an addition to the Description to included who added it and when
var subOn = “Added :”+sheet.getRange(lr,formTimeStampId,1,1).getValue()+” by: “+sheet.getRange(lr,titleId,1,1).getValue();
//Setting the Comments as the description, and addining in the Time stamp and Submision info
var desc = “Comments :”+sheet.getRange(lr,descId,1,1).getValue()+”n”+subOn;
//Create the Title using the Name and tType of Absence
var title = sheet.getRange(lr,titleId,1,1).getValue()+” – “+sheet.getRange(lr,titleId2,1,1).getValue();
//Create and get place
var loc = sheet.getRange(lr,locId,1,1).getValue();
//Run the Crete event Function
createEvent(calendarId,title,startDt,endDt,desc,loc);
};
function createEvent(calendarId,title,startDt,endDt,desc,locId) {
var cal = CalendarApp.getCalendarById(calendarId);
var start = new Date(startDt);
var end = new Date(endDt);
//Manually set the Location, this can be modified to be dynamic by modifying the code if need be
var loc = locId;
//Set the Options, in this case we are only using Description and Location, as we do not need Guests or sendInvites
var event = cal.createEvent(title, start, end, {
description : desc,
location : loc
});
};
Now I’m gonna try to solve how to set colors! 🙂
Fredrik,
Could you walk me through what you have done? I have used Matthew’s code as a basic structure for my own and have applied changes to it based on your code above.
I guess my first question would be: Is the location included on the Google Calendar invite based on information filled out in the Google form, or where the person filling out the form is located?
Basically, I’m attempting to have someone fill out a form with date/time/production location and, when it appears in the Google Calendar notification, I would like the notification to also include the location info .
Any help would be GREATLY appreciated
Hi Sam.
Sorry not having replied until now. Yes the location is included in the calendar event if its “findable” for google i suppose. Most of the time it works for me just using “Bla bla elementary school”.
Since I wrote this I have rewritten my script using the new Google Calendar API. I started writing a blog post about it … but time is not my friend 🙂
I will try to finish it this week and post it here.
All best
Fredrik
Hi Again Sam! I wrote a blog post were I use the new Calendar API. I hope the instructions are clear enought!
http://hedstrom.ninja/wordpress/2015/10/23/having-fun-with-google-calendar-api/
Hi Matthew,
Thanks for the tutorial. I’ve tried several times setting this up, but I never get anything on the calendar. The form submits and shows up on the spreadsheet, but nothing on the calendar and no error email. Any ideas?
Much appreciated!
Make sure that it’s executing all the codes. Some times Google deactivates the actions, you just need to reactivate them in the list.
Hi Matthew, when searching my google calender ID it has come up with just the email address that the calender is linked to, ie it doesn’t have the “@group.calendar.google.com” at the end of it.
Does it need to be a calender i have created? This would be a problem as we have been using a different calender for a while now and changing everything over would be a pain.
Thanks James
Never tried this on a default calendar, theoretically it should work. I have it working on two different calendars that were created specifically for the purpose.
Hi this will be really useful if the event can be added into the users default calendar, instead of a calendar that I have created. Is there a way to do this?
Actually I have figured this one out. You have to replace line 4 with the ‘get default Calendar’ code “var calendarId = CalendarApp.getDefaultCalendar()”.
You also have to change line 42 to the following “var cal = CalendarApp.getDefaultCalendar()”
Oliver,
So does this fix the issue of the events being populated on the other end users calendar instead of mine? As well, what did you replace in the code for line 3 “Calendar ID”… Please advise…
Hello Matthew,
I would like to say this has saved me quite of bit headache!! This script is strait to the point.
So I have created a Detention Sign Up form for our school. When I click on run for “CreateEVENT” I am presented with the following error:
“TypeError: Cannot call method “createEvent” of null. (line 48, file “Code”)”
//Set the Options, in this case we are only using Description and Location, as we do not need Guests or sendInvites
var event = cal.createEvent(title, start, end, {
description : desc,
location : loc
});
Line 48 is : var event = cal.createEvent(title, start, end, {
Some events are pushed to the Calendar the rest are failing.
Please let me know you thoughts if possible :).
Check your variable names (spelling) it needs date to run the function but its not getting any “Cannot call method “createEvent” of null.“
Hello
Thanks for your script.
Can you help me to modify the script, that it will create a fullday calender entry?
Thanks
Sadly I no longer work for the company I did the project for and have not dabbled in it since. I wouldn’t know where to begin, sorry.
hi, thank you alot for sharing this usiful code , i’ve adjusted it a little to match my needs , but when applying i got this error message “TypeError: Cannot call method “createEvent” of null. (line 46, file “Code”)” ,
Make sure when you are teting you are running the parent script, as it passes on the event to be created.
I am getting the same error code. What do you mean by running the parent script?
Great work. Thanks
How can we add the setting for hour and minute and the location to dynamic?
Thanks again!
I’ve managed to fix the time. Still no on the location!!!
this is fabulous! any idea how i could get the meetings to set as recurring?
Not off hand, there should be some resources somewhere, I will see if I can find some.
This seems good. Trying to get it setup – can’t locate or add the trigger “getLatestAndSubmitToCalendar” Anyone have any advice? Thank you
I seem to be having the same issue as others here in regards to the calendarID. I am using a school district domain (google of course) but can’t seem to get the form submission to populate to calendar. The email notification DOES work.
ReferenceError: “calendarId” is not defined. (line 48, file “Code”)
Could it be that I created the calendar in “My calendars” ?? Is there another method to creating calendars that I’m not familiar with?
This is a great script. I’ve been able to modify it for my needs however I keep getting this error whenever I test it
TypeError: Cannot call method “createAllDayEventSeries” of null. (line 46, file “Code”)Dismiss
worked great thanks!
Chris Macioch. Could you help me figure out what I am doing wrong with my code?
Chris, I am getting this error: TypeError: Cannot call method “createEvent” of null. I have no idea how to fix it. I used the entire code as he has suggested but with different titles for each in my google form. I have the same number of columns and type of information that he has in his. Yours worked, and mine doesn’t. So, I am wondering what I have done incorrectly. Please help!
Hi– did you ever get a reply for this issue? I have the same error.
Thanks!
Hello,
I’ve tried everything you’ve recommended and have even turned to the comments, but I can’t seem to figure out what’s wrong. Like a few others before me here, when I submit the form, the email sends, but no event is created. I know that the calendar ID is correct, and I adjusted the script to read the correct column with the date in it, but I’ve been having no luck.
any luck figuring out this script?
No, still no luck.
It seems to work for some people but not for others.
What about multiple selections on the form per question? Please advise…
what is my Calendar ID
I am able to get this script to populate my calendar. But for some reason when the event is created, the script starts the event at the time designated for the end of the event.
Anybody have any Ideas?
Hi Mitch. I’m wondering what your scenario is in regards to calendar/gmail account. Are you using a personal gmail account or are you in a google domain? The reason I ask is that I have been trying to get this script to work for awhile now and have had no luck with populating to calendar. I thought it may have something to do with the google domain I am in (its a school district domain but I have admin rights.)
Could you post your script here?
Thanks
Todd
I get everything except the start date. It defaults to 1969. I’m setting it in the form just like the end date. Anyone having the same issue? Or am I just not seeing my error? There are no js errors in the console, just an event in my calendar that starts Dec 1969.
Thanks,
R
Can I Add multiple event (or periodic event, eg, weekly ) in google calendar using google forms.,…
Hi. Really great script and howto. I wonder if you are planning to update the script for dynamic handling of the “Place” instead of computer club. Also if there is a way to set colors on events created in the calender.
getting this error in “Code” Unterminated regular expression literal. (line 52, file “Code”)Dismiss
That line of code reads… [/php]
help!111
Did you ever get this figure out? I am dealing with the same issue right now.
Line 36 of email code…message += key + ‘ :: ‘+ e.namedValues[key] + “nn”;
generates the following error
Illegal character. (line 36, file “Email for Form submission”)
Hi,
I have tried the script that you have published but i can not make it work. When i try to key in the calendar id. it always says “illegal character”
I believe they have updated their code. I made an amendment at the top of the post stating such.