Tutorial: Using Google Spreadsheets to Update a Google Calendar – Our New Vacation Management System

As promised in my last post about our vacation policies, here’s a step-by-step tutorial of how we now manage our vacation policies with a Google Calendar and a Google Spreadsheet.

By the end of this tutorial you’ll be able to:

  • Organize all of your employee vacation days in one central system using a Google Calendar and a Google Spreadsheet.
  • Have vacation days automatically added to a Google Calendar that is viewable to anyone in your organization.
  • Manage the vacation days in a Google Spreadsheet that only managers can see. The spreadsheet adds days to the calendar as well as automatically tallies vacation days taken for each person.

I used a combination of this helpful tutorial on OUseful.Info from 2010 along with Google’s Apps Scripts documentation to make it happen.

Ready? Let’s get started.

Step 1 – Set up the calendar & grab the Calendar ID

Google Spreadsheet to Calendar - create a calendar

  • In Google Calendar, go to My calendars -> Create new calendar.
  • Name the calendar whatever you want (we named ours “Pure Adapt Vacation Time”).
  • Share the calendar with everyone in your organization. Managers should be able to Make changes AND manage sharing whereas employees should just be able to See all event details.
  • Click Create Calendar.ย  Your calendar should now appear under My Calendars.
  • Click the drop-down next to the calendar and select Calendar settings.
  • Under the Calendar address section find the Calendar ID.
  • Leave this open, we’ll be using it in a minute.

Step 2 – Save a copy of the spreadsheet & update the script with your Calendar ID

Google Spreadsheet to Calendar - save the spreadsheet

  • Open up my Sample Vacation Time Calendar Spreadsheet
  • Save your own copy: File->Make a copy… (you’ll have to be logged in to your Google account).
  • Open up the script editor: Tools->Script editor…
  • Copy and paste your Calendar ID from above where it says INSERT_CALENDAR_ID_HERE.
  • Save the script: File -> Save.
  • You should see a Vacation Calendar button in your spreadsheet (you may need to refresh the page).

Step 3 – Test it out!

Google Spreadsheet to Calendar - test it out

  • In your spreadsheet, click Vacation Calendar -> Update Calendar.
  • The first time you’ll see a message from Google Calendar that says something like Authorization Required. This app needs authorization to run. Click OK and then Accept. This is a one-time authorization that you won’t need to do in the future.
  • Within a few seconds a y should appear under Column D (Calendar) to signify that the calendar has been updated.
  • Take a look at your calendar – Employee 1 should now have a vacation day on Christmas.ย  Success!

Google Spreadsheet to Calendar - example!


  • Totals on the spreadsheet are limited to six employees and 93 rows, but it should be easy to extrapolate those for a larger organization.
  • If you edit or delete a vacation day from the spreadsheet, you also have to edit or delete the calendar event.ย  See my notes in the code below if you’re curious why.
  • Only Full or Half days are supported as Types. If you enter Full it’s tallied as 1 day, anything else is tallied as .5 (we add more info to our half days, for instance “Half – morning”).

Apps Script

In case you’d like to see the apps script code without having to save a copy of the spreadsheet, here it is:

//push new events to calendar
function pushToCalendar() {
  //spreadsheet variables
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow(); 
  var range = sheet.getRange(2,1,lastRow,5);
  var values = range.getValues();   
  var updateRange = sheet.getRange('G1'); 
  //calendar variables
  var calendar = CalendarApp.getCalendarById('INSERT_CALENDAR_ID_HERE')
  //show updating message
  var numValues = 0;
  for (var i = 0; i < values.length; i++) {     
    //check to see if name and type are filled out - date is left off because length is "undefined"
    if ((values[i][0].length > 0) && (values[i][2].length > 0)) {
      //check if it's been entered before          
      if (values[i][3] != 'y') {                       
        //create event https://developers.google.com/apps-script/class_calendarapp#createEvent
        var newEventTitle = 'Vacation Day: ' + values[i][0] + ' - ' + values[i][2];
        var newEvent = calendar.createAllDayEvent(newEventTitle, values[i][1]);
        //get ID
        var newEventId = newEvent.getId();
        //mark as entered, enter ID
      } //could edit here with an else statement
  //hide updating message


//add a menu when the spreadsheet is opened
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];  
  menuEntries.push({name: "Update Calendar", functionName: "pushToCalendar"}); 
  sheet.addMenu("Vacation Calendar", menuEntries);  

         To trigger an alert use Browser.msgBox(); 
         onEdit triggers when spreadsheet is edited, however was unreliable
         To support edits, we need to grab calendar event by id (currently is not supported), then compare the date of the event with the date in the spreadsheet and adjust if they do not match

Update 8/13/15 – please do not request access to the spreadsheet. It is not necessary. You can save your own copy using the instructions in Step 2. Thanks!

Update 10/16/15 – I’ve closed comments on the post. If you have a question that isn’t already addressed in the comments below, take a look at Google’s Documentation. They have great tutorials, as well as a support community to answer any of your questions.

93 comments on Tutorial: Using Google Spreadsheets to Update a Google Calendar – Our New Vacation Management System

  1. dfriedla says:

    Thank you so much for sharing this! Really interesting integration possibilities.

    • Adam McFarland says:

      You’re welcome! Glad to hear you find it useful. If you end up doing anything interesting let me know.

  2. Jeni says:

    Could you use the onOpen function to automatically push the info to the calendar. I’m trying to write script that will allow someone to fill out a google form with info for a sign up and via the spreadsheet the info is set as the description for the event. I’ve got most of it figured out but I would like it to update automatically.

    • Adam McFarland says:

      Hi Jeni,

      Thanks for reading. That sounds like it’s doable. I recall having issues with ours taking a long time to update automatically with onOpen so that’s why I created the button. I haven’t used the Google Apps scripts since writing this post so I’m not intimately familiar with what’s possible and what isn’t. I’m sure their documentation can point you in the right direction.

      – Adam

  3. Mat Rees says:

    This looks like its what I need.
    But I cant adapt it to my spreadsheet – im a novice but I really need an auto event to be set up from my google sheet to my google calendar.

    What I am trying to do is add an auto event to the calendar when the content of a new row has been added. The calendar needs to display fields from about 8 columns and insert into the calendar at the set appointment time and date (also fields on the sheet). Can this be done? Can you help? If not – where can I go for help?

    Thank you


    • Adam McFarland says:

      Hi Mat,

      Thanks for checking out my post. I think the best place to go is Google’s documentation https://developers.google.com/apps-script/ If you need help with something specific, there are Google+ communities where you can ask questions. Based on what you described, you may also want to incorporate a Google Form. That could be a good work-around if you’re having trouble getting the spreadsheet to update when a new row is added (in this case you’re adding the row via the form, and maybe when the form is submitted you could add the row to the spreadsheet and the info to the calendar).

      Good luck!


    • Narelle says:

      Hi there, did you find a solution for this?
      In the Add-Ons or with just the script running automatically?


  4. Dudely says:

    You might find the following snippet useful. Personally I detest having to work with meaningless ID’s. The following trades off having to hard code a name instead of an ID, but at least a name is easy to understand what it means.

    var calendars = CalendarApp.getOwnedCalendarsByName(‘My Vacation Calendar’);
    var cal=calendars[0];

  5. JM Lowry says:

    Hi, so far so useful (transferring deadline spreadsheet to calendar) BUT it’s only paying attention to the row from your example: row 2. How do I get the script to pay attention to rows 3-38?

    • Adam McFarland says:

      Hi JM – sorry to hear it’s not working for you. I just tested out my sample one I created on my personal Gmail account and it worked beyond row 2. Maybe it’s worth deleting the spreadsheet and calendar and trying again?

  6. korbin says:

    I love this! I have been trying to do this for over a year now! Only 1 problem…
    If I edit the spreadsheet (change a date) and get rid of the Y for calender, I can republish that event but the old event is still in the calender.

    I am trying to do this for class lessons and every day we have a snow day, I update the spreadsheet and change all the days, the old days are still in the calender. .

    Think you can help? Thanks!

    • Adam McFarland says:

      Hi Korbin – that’s an awesome use of the spreadsheet. Glad it’s been helpful!

      Unfortunately though, that’s one of the limitations of the spreadsheet as currently constructed. When we edit a vacation day, we manually edit it in the calendar also. Alternatively we could do what you do (edit, republish) and then delete the old event from the calendar.

      I realize that may not scale well for your use. In my notes on the script I got into the reasons why I couldn’t add full editing functionality. It’s possible that Google has since added that ability. If you’re willing to spend a few hours with the script, the documentation, and/or ask around on the help forums you may be able to figure it out. Certainly if you learn anything let us all know.

      – Adam

  7. Dudely says:

    Not sure I fully understand what’s being requested, but it sounds like Korbin is trying to delete an event.
    I needed to do the same. Here’s what I did for myself.

    function processRow(row,rowNum,sheet) {

    var contactPerson = row[0];
    var phoneNumber = row[1];
    var email = row[2];
    var street = row[3];
    var city = row[4];
    var state = row[5];
    var zip = row[6];
    var notes = row[13]
    var status = row[14];
    var apptDateTime = row[15]; // Example Input: 02/27/2014 18:00:00
    var endTime=15; // add fifteen minutes to start time to get ending time.
    var endDateTime = new Date(addMinutes(new Date(apptDateTime), endTime));
    var description;

    Logger.log(“Entering processRow()”);

    if (contactPerson == “”) {
    Logger.log(“Exiting processRow()”);
    return -1;

    for (var i=0; i<8; i++)
    description+=row[i] + " ";

    for (var i=12; i<16; i++)
    description+=row[i] + " ";

    var location="";
    var calendars = CalendarApp.getCalendarsByName('Name of my calendar');
    Logger.log('Found %s matching calendars.', calendars.length);
    var cal=calendars[0];
    Logger.log('Calendar is set to the time zone "%s".', cal.getTimeZone());
    // Log the time zone of the script.
    Logger.log('Script Session is set to the time zone "%s".', Session.getScriptTimeZone());

    // Logger.log("ContactPerson=" +contactPerson);
    ////////////// Check for Duplicate Calendar Entries

    var check = cal.getEventsForDay(new Date(endDateTime));
    for(var event in check) {
    var obj = check[event];
    var found_title = obj.getTitle();

    if(trim(contactPerson) == trim(found_title)){

    // Logger.log("Found Duplicate Event");
    // return -1;



    var advancedArgs = {description: description, location: location};
    var event = cal.createEvent(contactPerson, new Date(apptDateTime), new Date(endDateTime), advancedArgs);
    //Logger.log('Event ID: ' + event.getId());
    event.addPopupReminder(24*60); // 24 hours prior
    event.addPopupReminder(60); // 60 minutes prior
    event.addSmsReminder(30); // 30 minutes prior
    event.addPopupReminder(10); // 10 minutes prior

    //Browser.msgBox("Appt Added to Calendar");

    Logger.log("Exiting processRow()");

    function trim(str) {
    return str.replace(/^\s+|\s+$/g,"");

    function addMinutes(date, minutes)
    return new Date(date.setMinutes(date.getMinutes() + minutes));

  8. Yourav says:


    Great explanations, I almost made it work but unfortunately failed after several attempts.

    Open up the script editor: Tools->Script editor
    We are given quite a few choices of scripts, nowhere I could find the one mentioning “says INSERT_CALENDAR_ID_HERE.”
    So I copied and paste what you wrote under “Apps Script” in a “Blank Script”

    After Saving, Giving Access to Calendars and Push To Calendars, I tried running the script but error message popped:
    Cannot find method createAllDayEvent(string,string). (line 27, file “Code”)

    Did I do something wrong?

    • Adam McFarland says:

      Hi Yourev,

      Thanks for the comment. Sorry to hear you’re having trouble getting it to work. I just attempted it again quickly and when I go to Tools -> Script Editor the “vacation-sample.gs” script is already loaded. It might be worth starting over to see if it works the second time through. However, if not, copying the script should work, just remember to still go in and change the calendar ID.

      Hope that helps!


  9. Brian Ferguson says:

    Hi, this is great! Is there anyway to have a form for the employee to fill out, which populates the spreadsheet, which in turn creates the calendar entry?

    • Adam McFarland says:

      Hi Brian,

      Glad you like it. Yea I’m sure you can do that. It’s a really good idea, I hadn’t thought of it!

      You could try creating the form using Google Forms. That will give you the choice of where you want the information sent to. You should be able to send it right into this spreadsheet. You can also get notified via email when someone fills out the form. That would be an amazing workflow: employee fills out the form, you get notified, you go in the spreadsheet to approve it, and then it shows up on the calendar.

      If you’re able to get it to work let me know.

      – Adam

      • Brian Ferguson says:

        I am trying to wrap my head around how the form would have to look to properly populate the already created spreadsheet with the script.

        • Adam McFarland says:

          Hi Brian,

          I’m not all that familiar with forms but they have good documentation here https://support.google.com/drive/topic/1360904?hl=en&ref_topic=2811744

          The other option would be to build the form to your specification and then re-work the resulting spreadsheet to accomplish what this one does. Hopefully that wouldn’t be too hard using my script above as a guideline.

          I’ve only really used forms to populate a spreadsheet once and from what I recall the destination spreadsheet is pretty rigid. I remember I tried to add a column and couldn’t. In that case you could have a second sheet in the spreadsheet that mimicked my original spreadsheet and then pull the data from the first sheet where the form was populated.

          Keep me posted on how it goes!

          – Adam

  10. Grahame says:

    This is just what I’ve been looking for as I am new to google scripting and needed a way to do what you created. Excellent stuff. One question, how would you go about setting the colorid for each person? I figure you could add the # code into a column and have the code reference it but what would the code be and where would you put it?


    • Adam McFarland says:

      Hi Grahame,

      Thanks for the kind words, glad it helped! By “colorid” do you want to color-code the events by person in your calendar or in your spreadsheet? I believe calendar colors are set in your settings of Google calendar and since this script only uses one calendar you’d only have one color for that calendar…without doing some re-work to the script. If you’re looking to color-code within the spreadsheet, you should be able to do that pretty easily with conditional formatting. Hope that helps, best of luck to you!

      – Adam

      • Grahame says:

        Hi Adam,

        Yes it is in the calendar that I want to color code each event. The calendar has its default color, but you can choose from 1 of 11 colors for the actual event. It is that color I want to set. From what I can see the calendar.createalldayevent has parameters for title, date and then options (https://developers.google.com/apps-script/reference/calendar/calendar#createAllDayEvent%28String,Date,Object%29) but the options section appears to not include a place to change the colorid of the event and I haven’t successful in changing the color that way.

        Is that possible with the options parameter and if yes, what would the syntax be, or if not is there another way to change an event’s colorid?


        • Adam McFarland says:

          Ah thanks for the clarification Grahame. I’ve never used that feature in Calendar but I do see it there now. I took a quick look at the page you linked to and there is a setColor function that appears to do what you desire. I would try that right under where I get the event ID. So something like:

          var newEventId = newEvent.getId();

          Give that a shot, hopefully it works.

          – Adam

          • Grahame says:

            Actually the setColor function is on the Calendar as whole not the Calendar event. I’ve looked into it further and it looks like they left the setColor function out of the parameters for events, go figure. As it turns out my project doesn’t actually need individual colors so your code is up and running and works great. Thanks again for doing this.


          • Adam McFarland says:

            That’s odd that it’s not even-specific like the getId() function is. Oh well, glad to hear it’s at least not a requirement for you.

          • Dani says:

            If you change your mind and decide you want to be able to set the colors of individual events, I found a library that offers that functionality. (I’m implementing it tomorrow, so I can’t comment on its effectiveness.)


  11. Eddie says:

    Hi guys,
    I am a working mum operating best within outlook alerts and business calendar on my Samsung Note 3. I struggle to remember when is the school sports day and cake sale yet I really want to be there!!!! Old schools my children went to had a Google event calendar which could be linked to my mobile phone diary. I would like to make something similar for our school Can you help me to do that? Thanks.

    • Adam McFarland says:

      Hi Eddie,

      Thanks for reading. Based upon what you described, you probably wouldn’t need the spreadsheet solution outlined here, rather a way to sync Google Calendar notifications with your phone and/or Outlook. Android’s calendar syncs seamlessly with Google Calendar, although your business calendar may use different software. You should be able to Google search the name of your business calendar software + “Google Calendar sync” to determine the best way to do that.

      – Adam

  12. bLewis says:

    This is really awesome! The only tweak for me would be to post events into a specific time slot versus a “Full” day event. Can this be done?

  13. bLewis says:

    I tried using the createEvent but cant seem to get it to work. Assuming my Start Dates and End Dates are in columns C and D. Can you suggest how it should be written. I’m sure its something minor I’m missing :-/.

    • Adam McFarland says:

      Hello again. I’d start by just trying to get every entry to have a time instead of being a full-day. So for instance, just change:

      var newEvent = calendar.createAllDayEvent(newEventTitle, values[i][1]);


      var newEvent = calendar.getDefaultCalendar().createEvent(newEventTitle, new Date(‘July 21, 2014 20:00:00 UTC’), new Date(‘July 21, 2014 21:00:00 UTC’));

      If that works, I’d then try adjusting your range in getRange to account for your two added columns, and finally your values to match the data values in the new columns. It might take a little while but I think you should be able to get there.

      Keep me posted on how it goes,


    • Mike says:


      Did you ever figure out how to do this?
      I am looking into it as well and can’t seem to get it to work. Let me know!

  14. Crystal B says:

    Any thoughts on if/how this could work with a range of dates as opposed to single days. So maybe using the existing date column for the “start date” then maybe add two new columns for “end date” and a column for the total number of days so the tally can still keep count of the days taken and available. Then can the code be modified to accomplish this so that separate rows do not have to be completed for consecutive days if an employee is taking the whole week off? Example….
    “start date” =8/4/14 and
    “end date”= 8/8/14 and
    “total days”= 5

    • Adam McFarland says:

      Hi Crystal,

      Thanks for reading! I think this can be done. First I’d change the spreadsheet to match what you described. The total days off can be calculated with the NETWORKDAYS function in Google Spreadsheets. Then in the code I think you can use the createEvent function I mentioned to bLewis above to have the start/end times match the corresponding columns in your spreadsheet.

      This is a good iteration…let me know if you’re able to get it working!


  15. scott says:

    Hello. I love this script. What i would like to do is a modified version of this. My needs would be for adding meetings into the spreadsheet that would then update to the calender. I would like the ability to add the date and also add start and end time as well as general comments. any help on this script and spread sheet would be much appreciated.

    • Adam McFarland says:

      Hi Scott,

      Thanks for reading & commenting! I think you should be able to accomplish this by using the calendar.createEvent modification described to bLewis above and then calling setDescription to add your comments. Hope that helps, if you get it working let me know!

      – Adam

  16. Richard says:

    AWESOME SCRIPT! I’m a pilot and my schedule gets sent to me in a spread sheet.
    Some modifications for additional columns, cut and paste, now my girl knows my schedule, and I can read it easliy.

    Working on making them individual events, let you know how it goes.

    I’m a novice, at this type of scripting, and love the challenge.

    • Adam McFarland says:

      Glad to hear it Richard, that’s a great use case! Keep me posted on your modifications, I’d be happy to update the post with them.

      • Joachim says:

        Hi Richard, can you tell me the whole function term modified? I am new in this business? In which columns do you enter the data for start and end time?
        Thank you in advance!

  17. Parth Shah says:

    It Not Working please help.
    * Creates a simple calendar event – requires modify permissions on the
    * calendar in question.
    * For more information on using Calendar events, see
    * https://developers.google.com/apps-script/class_calendarevent.
    function createEvent(calendarId) {

    var cal = CalendarApp.getCalendarById(calendarId);
    var title = ‘Script Demo Event’;
    var start = new Date(“April 1, 2012 08:00:00 PDT”);
    var end = new Date(“April 1, 2012 10:00:00 PDT”);
    var desc = ‘Created using Google Apps Script’;
    var loc = ‘Script Center’;

    var event = cal.createEvent(title, start, end, {
    description : desc,
    location : loc

    * Creates an event, invite guests, book rooms, and sends invitation emails.
    * For more information on using Calendar events, see
    * https://developers.google.com/apps-script/class_calendarevent.
    function createEventInvitePeople() {
    var calId = ”;
    var room1CalId = ‘a_room_cal_id’;
    var room2CalId = ‘another_room_cal_id’;
    var guest1Email = ‘guest1@yourdomain.com’;
    var guest2Email = ‘guest2@yourdomain.com’;
    var invitees = room1CalId + ‘,’ + room2CalId + ‘,’ + guest1Email + ‘,’ +

    var cal = CalendarApp.getCalendarById(calId);
    var title = ‘Script Center Demo Event’;
    var start = new Date(“April 1, 2012 08:00:00 PDT”);
    var end = new Date(“April 1, 2012 10:00:00 PDT”);
    var desc = ‘Created using Apps Script’;
    var loc = ‘Script Center’;
    var send = ‘true’;

    var event = cal.createEvent(title, start, end, {
    description : desc,
    location : loc,
    guests : invitees,
    sendInvites : send

    * Creates an event that recurs weekly for 10 weeks. These settings
    * are very simple; recurring events can become quite complex. Search for
    * ‘google apps script class recurrence’ to get more details.
    * For more information on using Calendar events, see
    * https://developers.google.com/apps-script/class_calendarevent.
    function createEventSeries() {
    var calId = ”;
    var cal = CalendarApp.getCalendarById();
    var title = ‘Script Center Demo Recurring Event’;
    var start = new Date(“April 1, 2012 08:00:00 PDT”);
    var end = new Date(“April 1, 2012 10:00:00 PDT”);
    var desc = ‘Created using Apps Script’;
    var loc = ‘Script Center’;

    var recurrence = CalendarApp.newRecurrence();
    var series = cal.createEventSeries(title, start, end, recurrence, {
    description : desc,
    location : loc

    • Adam McFarland says:

      Hi Parth,

      What specifically about your script isn’t working? What error messages do you see?

  18. Hugo says:

    Hi Adam great script!

    Just a quick one have you tried to add event with time?


    • Adam McFarland says:

      Thanks Hugo! I think you should be able to accomplish this by using the calendar.createEvent modification I described to bLewis above.

      Good luck!

  19. Bharath says:

    Can we incorporate time also in this ?If so please let me know how to do that.

    • Adam McFarland says:

      Hi Bharath, thanks for reading. Yup, you should be able to accomplish this by using the calendar.createEvent modification I described to bLewis above.

  20. Anthony Amadure says:


    Great idea for updating a calendar from a spreadsheet. Would I be able to use this same idea/format you showed at the beginning of this thread to list training events on a Google spreadsheet and have them populate to a Google calendar? Ultimately I’d like to have multiple tabs on a spread sheet update to a calendar. Please let me know if i can use what you’ve shown to in any way accomplish my objective. Thank you very much for any assistance you could provide.

    • Adam McFarland says:

      Hi Anthony,

      Yea absolutely! I think you could use this as a starting point. You’d probably want times entered instead of entire days, so you’d probably want to use the calendar.createEvent modification I described to bLewis above. Otherwise I think everything would be pretty much the same.

      – Adam

  21. Phiasko1 says:

    I am trying to use our script for something a bit different for project management…. I need to add events to calendar from a spreadsheet. I manage to do it once and even managed to change the title of the event instead of “vacation days”. But every time I manage to only add one event and then it doesn’t recognize the next events… I am not sure if it because I am not using the second table of the totals or what do you think it could be…? I tried already a few times and i couldn’t add more events… (sorry don’t know anything about scripts etc so this might be a totally idiotic question..)
    and thank you for sharing your work!

    • Adam McFarland says:

      Hi Phiasko1,

      Thanks for reading. I don’t think it has anything to do with the second table. My best guess would be that it has something to do with not having all of the other columns in the same order. If all else fails, you could always start over, get it working how you want it with your calendar, and then remove/edit the spreadsheet after the fact.

      – Adam

  22. Glen says:


    Great Script ๐Ÿ™‚ and i’m trying to re-use it but need your advice on how you selected which cells to use.

    I have created a Form that users will fill out to request to borrow a laptop. From this I have the responses spreadsheet. Now with the responses spreadsheet I want to be able to automatically push that to a calendar I have created. Which is why this looks perfect for me but not sure how to adjust it to match headers in my spreadsheet.

    Headers are ‘Timestamp’ in A1 (which i dont need in the calendar), ‘Name’ in B1 which i will require, ‘Start Date’ in C1 (which will be needed), ‘Time if Collection’ in D1 (which is not necessary), and ‘Return Date’ in E1 which is necessary.

    Any help would be greatly appreciated but please don’t feel obliged.

    Thanks very much ๐Ÿ™‚

    • Adam McFarland says:

      Thanks Glen! It’s been a while since I wrote this script, but you should just be able to adjust the “Spreadsheet Variables”, namely range and updateRange to match your spreadsheet. Hope that helps!

      – Adam

  23. Drew says:

    Hey Adam, again awesome script. I’m trying to do something much like glen in that I need the calendar to grab a few more cells. I see under the variables that there is a get range input but where exactly would I input what columns I want the sheet to grab?



    • Adam McFarland says:

      Thanks Drew! The line you’d be looking for would probably be:

      var range = sheet.getRange(2,1,lastRow,5);

      In the documentation it looks like the input variables for getRange are: row, column, numRows, numColumns. With that you should be able to select any row/column combination you wish.

      Hope that helps!


  24. Grace says:

    Hi, I used this vacation calendar script to try and move events listed in a spreadsheet (which is populated by a form) to a calendar. And it’s working…except each time I click to update the calendar, all the previously entered events are duplicated in the calendar even though there is a ‘y’ in the correct column and an event ID in the spreadsheet.

    I have been basically self-teaching all this coding/script writing through these forums so I have no idea what I’m actually doing. I don’t think I have changed anything in the script except for the column for marking ‘y’ when updating (I needed more columns than the vacation calendar had so I moved it from column C to column E), but below is the script I’m using – please let me know if there’s something wrong which would explain why I am getting duplicated events each time I update! Thank you so much!

    var numValues = 0;
    for (var i = 0; i 0) && (values[i][2].length > 0)) {

    //check if it’s been entered before
    if (values[i][5] != ‘y’) {

    //create event https://developers.google.com/apps-script/class_calendarapp#createEvent
    var newEventTitle = ‘Visit: ‘ + values[i][0] + ” (” + values[i][2] + “)” + ‘ – ‘ + values[i][3];
    var newEvent = calendar.createAllDayEvent(newEventTitle, values[i][1]);

    //get ID
    var newEventId = newEvent.getId();

    //mark as entered, enter ID

    } //could edit here with an else statement

    • Adam McFarland says:

      Hi Grace,

      Glad to hear you’ve got it working. In briefly looking at your code, I think you might want to try keeping

      if (values[i][3] != ‘y’) {

      instead of changing it to 5. I think it would depend on the range you set up top. Assuming you adjusted:

      var range = sheet.getRange(2,1,lastRow,5);

      I think the “y” still might be located in the 4th place of the array, meaning 3 since 0 counts as a place.

      If that doesn’t work, try changing that number up and down and I bet that will work. Keep me posted on how it goes.

      – Adam

  25. Joey Juarez says:


    The calendar works great, accept from March 8 – April 5 of 2015 the calendar updates to 1 day before.

    In other words I enter the date 3/8/15 and on the calendar it’s 3/7/15… Help not sure what’s wrong.

    • Adam McFarland says:

      Hi Joey,

      Thanks for the kind words. I’m not sure what would be causing that problem, we’re not seeing that issue. My guess is that it has something to do with time zones. I’m not sure where those settings are in Calendar and Sheets, but that’s what I would look into.

      – Adam

  26. saul says:

    Hi, thank you very much for that info. There’s something missing for me between those 2 lines:
    Open up the script editor: Tools->Script editorโ€ฆ
    Copy and paste your Calendar ID from above where it says INSERT_CALENDAR_ID_HERE.

    When I open the script editor I get the option to create a new script: Blank, some Add-ons, custom function and web app.
    No matter what I choose, I don’t see the line where I add the Calendar_ID

    Hope you can explain.

    • Adam McFarland says:

      Hi Saul,

      I just gave it a quick test and it seems to be working. Maybe try saving a new copy of the spreadsheet (the step directly above what you referenced). If your copy of the spreadsheet seems fine and the only thing missing is the script you could always copy and paste the script in this post in to the script editor.

      Good luck!

      – Adam

  27. Kristian says:

    Hey Adam,

    My company has us keep track of all of the meetings we take in a spreadsheet, and I wanted to save myself the added step of putting them on my calendar as well my using this script. The spreadsheet we use has several sheets so I edited the code to reflect that.

    I am getting the following error (class)@9dc67d2 is not a function, it is object.

    I edited the code at the following locations

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Cal static”)

    var range = sheet.getRange(2,1,lastRow,6)

    Name Date Type Calendar Calendar Event ID End

    var newEvent = calendar.createEvent(newEventTitle,new values[i][1], new values[i][5])

    Secondly, is there a way to add the locations into the script as well?

    • Adam McFarland says:

      Hi Kristian,

      I’m not sure about the (class)@9dc67d2 is not a function, it is object error, you might need to do some Googling around about that one.

      As far as locations, the setLocation event should do the trick. On my script I’d try something like:

      //get ID
      var newEventId = newEvent.getId();
      newEvent.setLocation('Albany, NY');

      I haven’t tested that but it – or something similar – should work.

      Hope that helps and good luck!

      – Adam

  28. Alek says:

    Hey Adam,

    Love the script but i’m finding its creating the calendar entry a day earier and I can’t work out why? any ideas?

    • Adam McFarland says:

      Thanks Alek. Unfortunately I’m not sure what would be causing that. Joey Juarez above had the same issue, my guess would be that it has something to do with time zone settings in either Calendar or Sheets. I’d look into those. It’s possible my spreadsheet is set somewhere to Eastern Time (US) because that’s my time zone. If you figure it out let us know, I’m sure future people who run into this issue will appreciate it.

  29. kevinsteward says:

    Hi Adam

    Re: sheets putting calendar entry in the day before or similar. you are correct it is time zones i am in the uk so mine was day before, you need to set the time zones to be the same in Sheets, Calendar and in script editor. then it puts the entry on the correct calendar day.

    • Adam McFarland says:

      Thanks Kevin! I appreciate you leaving a comment. I’m sure that will be helpful for a lot of people.

  30. Marie says:

    Hi there,
    I am very glad I discovered your script. I am looking to share a spreadsheet and allow different people to generate events in their own agendas. at this stage, they can only generate event in a shared calendar. Could you help me with that ?
    Just to let you know, I am not a developer myself. Would be more than happy to have a script exemple that I can modifiy with the 5 different Google Agenda IDs.
    Thank you,

  31. marie says:

    Hi again,
    Also, I would like to run the script many time during the day without creating duplicates. Could you help on that ?

    • Adam McFarland says:

      Hi Marie,

      Thanks for reading. I think you should be able to modify the calendar variables to achieve what you’re looking for. Instead of inserting your calendar ID where it says INSERT_CALENDAR_ID_HERE you could have it pull a calendar ID from the spreadsheet.

      That would require some effort and testing. Since you’re not a developer, you may want to ask a developer to assist you, or possibly devote a few days yourself to reading the documentation and playing around with it,

      Good luck,


  32. […] part-timers, we’re navigating our way through eight people’s vacations this year. Our vacation calendar has been full since June and it doesn’t clear up until the end of […]

  33. Chris says:

    This has been a huge help! Just wanted to see how I can add a description column and have it populate in the calendar? Not sure how to integrate the setdescription() within the script.

    • Adam McFarland says:

      Hi Chris,

      Glad to hear it! To use setDescription I would:

      1) Replace the “Notes” field column in the spreadsheet with “Description”

      2) In the script I would replace:

      var range = sheet.getRange(2,1,lastRow,5);


      var range = sheet.getRange(2,1,lastRow,6);

      3) Above the //get ID comment, I’d add:


      Keep in mind I haven’t tested this code, but it should get you on the right track.

      – Adam

  34. Guilherme says:

    Thanks a lot.

    It helped me to create a spreadsheet to update my calendar with my exams dates.


  35. Christa says:

    Is there any way to insert a email column and add them to the calendar invite?

    • Adam McFarland says:

      Hi Christa,

      Yes, I think you should be able to do that with the addGuest function in a similar fashion to how I described adding a description field to Chris a few comments above. If you get it to work let us know, I’m sure it will be helpful to others.

      – Adam

  36. mbarki chadi says:

    hello Adam,

    Can you share and send me the last version of the script? and thank you so much

    • Adam McFarland says:

      Hi Mbarki,

      Thanks for checking out my site. The version in the post is the latest working version. I just used ours yesterday and it worked so I think it should still work for you. You can save your own copy by following the instructions in Step 2 in the post. Hope that helps.

      – Adam

  37. Mbarki Chadi says:

    hello Adam,

    Thanks a lot for your response i just ask about new version you know day after day API change i have already make a copy tahnks a lot for your sharing but i wanna ask you i just start developping new project with Google sites and GAS “Leave management system” os i wanna ask you if is there a solutions that to validate the request (i mean that the request from the emplyee must first be validated by the Humai ressource) so have you any kind of this script to do it ? and thanks a lot

    • Adam McFarland says:

      Hi Mbarki,

      I was unaware of any API change. I took a look in the documentation and did a quick Google search and couldn’t find any information about it. Do you have a link about the changes?

      To validate the request, for instance from human resources as you suggest, you could either have the employee email the request to human resources and have HR enter it into the spreadsheet (this is what we do), or you could create a Google Form and have the employees enter their requests there. The requests would be entered into the spreadsheet, but to be approved and show up on the calendar HR would go in to the spreadsheet and click the “Update Calendar” button.

      I hope that helps a bit.

      – Adam

  38. Lance says:

    I am pretty new to coding and I tried a simpler version of your code. I was wondering the purpose of the ‘numValues’ and ‘numValues++’. My script actually works without it but I’m not fluent enough to know what the significance is. Thanks!

    • Adam McFarland says:

      Hi Lance,

      Actually, they don’t have a purpose ๐Ÿ™‚ I never noticed that! Good catch. I must have put them in there when attempting to add a feature (maybe one of the things in the notes) and then never removed them. Next time I update the script I’ll take them out of there.

      – Adam

  39. Joseph says:

    anyone who has a code for Leave request, Supervisor and Human Resources approval.
    Thanks in advance

  40. Safa says:

    Hi Adam

    Many thanks for your tutorial and script. You are so kind to share this and help others tweak it as needed.

    This is all new to me but it’s what I was looking for. I’ve been playing around with it trying to get it to work but not sure what I am doing wrong
    I use google sheets to record my work (I work in different areas) – I was trying to get the script to read the content of one or two columns next to the date then push this to the calendar.

    See picture below:

    I was trying to get the script to read columns C & D then push those to corresponding date (as per column A) on calendar.
    There might have been an issue with range as the readable range is 32 (end of month) … I also use different sheets in the same spreadsheet for different months.

    Could you please help me figure this out

    Best regards


    • Adam McFarland says:

      Thanks for the kind words Safa!

      The first line you’d need to change would be:

      var range = sheet.getRange(2,1,lastRow,5);

      In the documentation it looks like the input variables for getRange are: row, column, numRows, numColumns. With that you should be able to select any row/column combination you wish.

      Then within the for loop, you can adjust the text for the event:

      var newEventTitle = ‘Vacation Day: ‘ + values[i][0] + ‘ – ‘ + values[i][2];

      values[i][0] is the first column, values[i][1] would be the second column, and so on.

      Hope that helps!

      – Adam

  41. Safa says:

    Dear Adam

    Can’t thank you enough
    I will change it as you suggested

    Many thanks & best regards


  42. Elliot Johnson says:

    This works absolutely amazingly.

    How easy would it be to change this to add holiday between 2 dates so people could book a whole work week off etc? I have tried to work it out but cannot get my head round it.

    • Adam McFarland says:

      Thanks Elliot! Check out the comments above with bLewis, the suggestions I had for him should work.

      – Adam

Comments are closed for this post.