Adding a Google Calendar event using Google Forms
Apr201402
UPDATE
This article is fairly old and definitelly out of date. Google has changed its API and i have not had time to update this post. I would recommend reading this article written by Fredrik which details how to use the new api. Happy Coding.

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

create-formI 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:
view-responses

Once you are on your spreadsheet, you will select “Script editor” under “Tools

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:
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:
resources
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)
triggers

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).

77 comments

  1. CN2_guy says:

    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.

    • Matthew says:

      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.

  2. 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/

  3. Thanks Mr. Matthew since i got your coding, i was frustation but now the good way appear beside me 🙂

  4. Brian Pugh says:

    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?

  5. 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!

    • Matthew says:

      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.

  6. Leonard says:

    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!

    • Matthew says:

      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)

  7. Moonie says:

    Having a bit of difficulty with this Matthew, doesn’t want to output to the calendar no matter what I try 🙁

    • Matthew says:

      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.

      • Moonie says:

        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.

        • Matthew says:

          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.

          • Moonie says:

            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 🙁

          • Matthew says:

            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”.

          • Moonie says:

            http://i61.tinypic.com/21me060.jpg

            is what mine looks like, the blacked out part it the URL at the front.

  8. Sean says:

    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!

  9. Basit says:

    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.

    • Matthew says:

      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.

  10. hypealbis says:

    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!

    • Matthew says:

      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.

    • Daniel says:

      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.

      • Matthew says:

        does it tell you what error is in regards to? If so can you copy and paste the error here for us to view.

        • Daniel says:

          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

        • Daniel says:

          DId I type my Calendar ID wrongly? Any special format for that? THanks

        • Daniel says:

          It is the Calendar ID problem. Ive fixed it. THank you so much.

  11. Daniel Zayas says:

    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.

    • Matthew says:

      Not sure, I haven’t had time to mess with customization since I did this post. Maybe another reader might have an answer.

    • Fredrik says:

      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! 🙂

      • Sam says:

        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

  12. jwequus says:

    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!

    • Matthew says:

      Make sure that it’s executing all the codes. Some times Google deactivates the actions, you just need to reactivate them in the list.

  13. James Tranter says:

    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

    • Matthew says:

      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.

  14. Oliver says:

    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?

  15. Oliver says:

    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()”

    • greg says:

      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…

  16. Randy says:

    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 :).

    • Matthew says:

      Check your variable names (spelling) it needs date to run the function but its not getting any “Cannot call method “createEvent” of null.

  17. Hello

    Thanks for your script.
    Can you help me to modify the script, that it will create a fullday calender entry?

    Thanks

    • Matthew says:

      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.

  18. dpd says:

    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”)” ,

  19. Rui Barros says:

    Great work. Thanks
    How can we add the setting for hour and minute and the location to dynamic?
    Thanks again!

  20. Cassi says:

    this is fabulous! any idea how i could get the meetings to set as recurring?

  21. Kyle says:

    This seems good. Trying to get it setup – can’t locate or add the trigger “getLatestAndSubmitToCalendar” Anyone have any advice? Thank you

  22. Todd says:

    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?

  23. Shaun says:

    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

  24. worked great thanks!

    • Todd says:

      Chris Macioch. Could you help me figure out what I am doing wrong with my code?

    • senorajf says:

      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!

  25. Mohammad A says:

    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.

  26. greg says:

    What about multiple selections on the form per question? Please advise…

  27. Jason says:

    what is my Calendar ID

  28. 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?

    • Todd says:

      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

  29. Ryan says:

    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

  30. Jason says:

    Can I Add multiple event (or periodic event, eg, weekly ) in google calendar using google forms.,…

  31. Fredrik says:

    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.

  32. Ron Stead says:

    getting this error in “Code” Unterminated regular expression literal. (line 52, file “Code”)Dismiss

    That line of code reads… [/php]

    help!111

  33. Ron Stead says:

    Line 36 of email code…message += key + ‘ :: ‘+ e.namedValues[key] + “nn”;

    generates the following error

    Illegal character. (line 36, file “Email for Form submission”)

  34. Allan says:

    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”

Leave a Comment