// you’re reading...

Google

Google Apps Script: Automatically create Calendar Events from Google Form

Recently I wrote a Google Apps Script for a client which creates Google Calendar events from the responses submitted to a form created using Google Forms.  Based on the start and end date in the form, the script automatically creates an event in a Google calendar, when the form is submitted. Also if a response is edited in the responses spreadsheet, the calendar event is modified automatically through this script.

What’s more using the AppSheet addon, the form can be submitted from a mobile too.

Instead of publishing the entire script, which also does a few more things based on the client’s requirements, following is a simpler script using a simple Google Form.

For a simpler example, I created a Google Form as shown in the screenshot.

A simple Google Form prompting for a start and end date which will be synced with the Google Calendar

A simple Google Form prompting for a start and end date which will be synced with the Google Calendar

Once you have created this form, click on View Responses. This will show a Google Spreadsheet with the columns Timestamp, Title, Info, Start Date and End Date. To this spreadsheet, add a new column named “Google Calendar Event ID” (without the double quotes), next to the End Date column. That is, Google Calendar Event ID will be the last column of the sheet.

While still on the Responses Spreadsheet, click on Tools>Script Editor. Click on the Close button if you see a popup titled Google Apps Script. This will reveal a script named Code.js with the following content:

function myFunction() {
  
}

Replace this content with the following script:

//insert your google calendar ID
var calendarId = "insert-calendar-id";

//index (starting from 1) of each column in the sheet
var titleIndex = 2;
var descriptionIndex = 3;
var startDateIndex = 4;
var endDateIndex = 5;
var googleCalendarIndex = 6;

/*
find the row where the Google Calendar Event ID is blank or null
The data of this row will be used to create a new calendar event
*/
function findRow(sheet) {
 var sheet = SpreadsheetApp.getActiveSheet();
 var dataRange = sheet.getDataRange();
 var values = dataRange.getValues();

 for (var i = 0; i < values.length; i++) {
 if(values[i][googleCalendarIndex-1]=="" || values[i][googleCalendarIndex-1]==null)
 newEvent(i+1);
 } 
 };


/* 
get the data of the new row by calling getSheetData() and 
create a new Calendar event by calling submitToGoogleCalendar()
*/

function newEvent(row){ 
 var sheet = SpreadsheetApp.getActiveSheet();
 var eventId = submitToGoogleCalendar(getSheetData(sheet,row),null)
 if(eventId!=null) 
 sheet.getRange(row,googleCalendarIndex,1,1).setValue(eventId);
};


/* 
Store the data of a row in an Array
*/

function getSheetData(sheet,row)
{
 var data = new Array();
 
 data.title=sheet.getRange(row,titleIndex,1,1).getValue();
 data.description=sheet.getRange(row,descriptionIndex,1,1).getValue();
 
 data.startDate = sheet.getRange(row,startDateIndex,1,1).getValue();
 data.endDate = sheet.getRange(row,endDateIndex,1,1).getValue();
 
 return data; 
};

/* 
if a cell is edited in the sheet, get all the data of the corresponding row and 
create a new calendar event (after deleting the old event) by calling submitToGoogleCalendar() 
*/

function dataChanged(event){
 
 var sheet = SpreadsheetApp.getActiveSheet();
 var row = event.range.getRow();
 
 var eventId = sheet.getRange(row,googleCalendarIndex,1,1).getValue();
 
 var eventId = submitToGoogleCalendar(getSheetData(sheet,row),eventId)
 
 if(eventId!=null)
 sheet.getRange(row,googleCalendarIndex,1,1).setValue(eventId);
 
};


/* 
This function creates an event in the Google Calendar and returns the calendar event ID 
which is stored in the last column of the sheet 
*/
function submitToGoogleCalendar(sheetData,eventId) {
 // some simple validations ;-)
 if(sheetData.title == "" || sheetData.startDate == "" || sheetData.startDate == null)
 return null;
 
 var cal = CalendarApp.getCalendarById(calendarId);
 var start = new Date(sheetData.startDate);
 var end = new Date(sheetData.endDate);
 end.setHours(23);
 end.setMinutes(59);
 end.setSeconds(59);
 
 // some simple date validations
 if(start > end)
 return null;
 
 var event = null;
 
 //if eventId is null (when called by newEvent()) create a new calendar event
 if(eventId==null)
 {
 event = cal.createEvent(sheetData.title, start, end, {
 description : sheetData.description, 
 });
 return event.getId(); 
 }
 /*
 else if the eventid is not null (when called by dataChanged()), delete the calendar event 
 and create a new event with the modified data by calling this function again
 */
 else
 {
 event = cal.getEventSeriesById(eventId);
 event.deleteEventSeries(); 
 return submitToGoogleCalendar(sheetData,null);
 }
 
 return event.getId();
 
};

In the above script, replace insert-calendar-id with the ID of your Google Calendar. The way to find the calendar ID is to browse to calendar.google.com. Login and move the mouse over on the calendar on the left. If you have more than one calendar, select the calendar in which you want the form data to sync. Click on the down arrow image which appears and select Calendar settings. On the subsequent page, copy the calendar ID (shown against Calendar Address:). In the above script, replace insert-calendar-id with the calendar ID that you copied. Next, on the menu, click on the save icon to save the script.

When prompted to enter new project name, type in any name (for example, Sync to Calendar). Next, click on Resources>Current project’s triggers (from the menu at the top). On the popup, click on “No triggers set up. Click here to add one now”. From the dropdown for Run, select findRow, under Events, select Time-driven. In the next dropdown, select Minutes timer and then select Every minute. Click on Add a new trigger. Select dataChanged for the Run dropdown, select From Spreadsheet for Event. In the next dropdown, select On edit. The Current project’s trigger should look like the screenshot below:

Google Spreadsheet - Project Triggers

Google Spreadsheet – Project Triggers

Click on Save. This will prompt you to authorize the script to access your Google Calendar. Click on Continue and then on Accept in the next window.

How it works:
Now whenever someone fills up the Google Form (as setup above), the response goes into the spreadsheet. The first trigger that we had setup, fires every minute and calls the findRow( ) function. This function reads the cell for the column no. 6 (Google Calendar Event ID), which is the column we inserted in the spreadsheet. If there is no data/value in this cell, this function calls the function newEvent( ) to insert a new calendar event with the data of the row where the cell is blank. Note: the event will be created in the calendar after a delay of a minute

Also, if you edit any data in a cell, the second trigger calls the dataChanged( ) function which deletes the calendar event and creates a new event with the changed/modified data. I have explained parts of the above script using inline comments. For any doubts/suggestions, please feel free to reach out to me using the comment box on this blog post 🙂

Update (November 3, 2015): First, all thanks to Dmitry who wrote a comment to point out the following typo in the script. There was a typo in the script because of which the editing of data was not working i.e. the edited data was not getting updated in the calendar. The typo was in the line 115:

 return SubmitToGoogleCalendar(sheetData,null) 

in the function named submitToGoogleCalendar. In the above line, the name of the function was misspelled as SubmitToGoogleCalendar whereas the correct code should be as follows:

 return submitToGoogleCalendar(sheetData,null);

Note the small “s” and also the added semicolon. I have fixed the code in the blog post. So this update note is only for those who would have copied or used this code before 3rd November 2015.

GD Star Rating
loading...
GD Star Rating
loading...
Google Apps Script: Automatically create Calendar Events from Google Form, 9.7 out of 10 based on 7 ratings
Share

Email This Post Email This Post Print This Post Print This Post Print This Post Post A Comment Tweet your comments/question to me @shekharg

Discussion

126 comments for “Google Apps Script: Automatically create Calendar Events from Google Form”

  1. More:

    Run/findRow gives no error.

    Run/newEvent gives error:
    Cannot find method getRange((class),number,number,number). (line 49, file “”)

    Run/getSheetData gives error:
    TypeError: Cannot call method “getRange” of undefined. (line 49, file “”)

    Run/dataChanged gives error:
    TypeError: Cannot read property “range” from undefined. (line 66, file “”)

    Run/SubmitToGoogleCalendar gives error:
    TypeError: Cannot read property “title” from undefined. (line 84, file “”)

    Posted by Charlotte Pierce | November 12, 2015, 4:10 am
  2. Here’s the code in each of the rows in error messages above:

    Row 49:
    data.title=sheet.getRange(row,titleIndex,1,1).getValue();

    Row 66: var row = event.range.getRow();

    Row 84: if(sheetData.title == “” || sheetData.startDate == “” || sheetData.startDate == null)

    Posted by Charlotte Pierce | November 12, 2015, 4:12 am
  3. I have worked for hours to try to get your code to work but have been unsuccessful. My headings were different from yours so I changed the var IDs in the code to match.
    var teacherIndex = 2;
    var startDateIndex = 6;
    var endDateIndex = 7;
    var googleCalendarIndex = 8;

    Should I have changed something further down in the code to correspond to those changes.
    Thanks,
    Melinda

    Posted by Melinda Buterbaugh | November 12, 2015, 5:13 am
  4. Hi Melinda Buterbaugh,

    Should I have changed something further down in the code to correspond to those changes.

    Yes. You have renamed the variable titleIndex to teacherIndex. So you will have to replace the name of this variable in the function function getSheetData(sheet,row):

    data.title=sheet.getRange(row,titleIndex,1,1).getValue();
    

    to:

    data.title=sheet.getRange(row,teacherIndex,1,1).getValue();
    

    Posted by Shekhar | November 12, 2015, 9:36 pm
  5. Hi Christina,

    Event start time must be before event end time. (line 102, file “Code”) time-based

    The error is exactly what is said by the above 🙂 I too faced this issue. If you are capturing the time, the start date and time must be less than the end date and time. This becomes more important if the start and end date are the same. This is the reason I’m appending 23:59:59 to the end date in my script:

    var end = new Date(sheetData.endDate);
     end.setHours(23);
     end.setMinutes(59);
     end.setSeconds(59);
    

    Hope this helps and gives you some hint to fix the error. Lemme know

    Posted by Shekhar | November 16, 2015, 7:52 pm
  6. Charlotte Pierce,

    TypeError: Cannot call method “getRange” of undefined. (line 49, file “”)

    Question: did you by mistake setup the script on the Google Form? The form also has a Script Editor (under tools) which should not be used.

    You should click on Responses>View Responses. This will show you the responses spreadsheet. On this page, click on Tools>Script Editor and copy/write the script.

    Posted by Shekhar | November 16, 2015, 7:58 pm
  7. I think I am close! This is my first time working through script – so issue me for a silly question.

    This is what I have
    78 This function creates an event in the Google Calendar and returns the calendar event ID
    79which is stored in the last column of the sheet
    80*/
    81function submitToGoogleCalendar(sheetData,eventId) {
    82 // some simple validations 😉
    83 if(sheetData.title == “” || sheetData.startDate == “” || sheetData.startDate == null)
    84 return null;
    85
    86 var cal = CalendarApp.getCalendarById(calendarId);
    87 var start = new Date(sheetData.startDate);
    88 var end = new Date(sheetData.endDate);
    89 end.setHours(23);
    90 end.setMinutes(59);
    91 end.setSeconds(59);

    I am getting this error
    Type Error: Cannot read property “title” from undefined. (line 83, file “”)

    Here is the link to my very simple form

    https://docs.google.com/forms/d/1Ghwf7vxhVPnuir4vl4wuk_GQJQif4-CGvg7fpmc97-E/edit?usp=sharing

    Also my end time does not work — I can get an appt to show on the calendar – the start time is correct – but it always ends at 11:59pm. I am so excited to get this to work …with you help 🙂

    Posted by Kathi Smith | November 23, 2015, 9:06 pm
  8. Hi Kathi Smith,

    Here is the link to my very simple form

    https://docs.google.com/forms/d/1Ghwf7vxhVPnuir4vl4wuk_GQJQif4-CGvg7fpmc97-E/edit?usp=sharing

    Sorry for the huge delay in responding. I was able to access your form and the responses spreadsheet but the script editor is disabled. Guess I will be able to access it if you enable “can edit” while sharing.

    Posted by Shekhar | November 30, 2015, 1:47 am
  9. https://docs.google.com/a/stpaul.k12.mn.us/forms/d/1eq3oMoTmFk6m3H2qRaZZpnVKn83dqVtPRZY_Cc42-Rs/viewform?usp=send_form

    Can you help me figure my form out? I tried to copy and paste your script up there, but then noticed that it didn’t work because of the different indexes… Thanks in advance!

    Posted by Natalia Vang | December 1, 2015, 12:01 am
  10. Posted by Natalia Vang | December 1, 2015, 12:03 am
  11. Kathi Smith

    89 end.setHours(23);
    90 end.setMinutes(59);
    91 end.setSeconds(59);

    Try removing these lines because these set the end time to 23:59:59. In my form, the time is not included but I can see that your form allows to select the time.

    Posted by Shekhar | December 2, 2015, 12:11 pm
  12. Hi Kathi Smith, thanks for that information. Would I also have to change the “index” information, line 5-9, according to my form? Thanks again!

    Posted by Natalia Vang | December 2, 2015, 7:37 pm
  13. This is what I have in the script:
    //insert your google calendar ID
    var calendarId = “stpaul.k12.mn.us_ffaa62a51a9aq6c7pp7cacnif8@group.calendar.google.com”;

    //index (starting from 1) of each column in the sheet
    var fullNameIndex = 2;
    var schoolIndex = 3;
    var fossKitIndex = 4;
    var startDateIndex = 5;
    var endDateIndex = 6;
    var googleCalendarIndex = 7;

    /*
    find the row where the Google Calendar Event ID is blank or null
    The data of this row will be used to create a new calendar event
    */
    function findRow(sheet) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();

    for (var i = 0; i < values.length; i++) {
    if(values[i][googleCalendarIndex-1]=="" || values[i][googleCalendarIndex-1]==null)
    newEvent(i+1);
    }
    };

    /*
    get the data of the new row by calling getSheetData() and
    create a new Calendar event by calling submitToGoogleCalendar()
    */

    function newEvent(row){
    var sheet = SpreadsheetApp.getActiveSheet();
    var eventId = submitToGoogleCalendar(getSheetData(sheet,row),null)
    if(eventId!=null)
    sheet.getRange(row,googleCalendarIndex,1,1).setValue(eventId);
    };

    /*
    Store the data of a row in an Array
    */

    function getSheetData(sheet,row)
    {
    var data = new Array();

    data.title=sheet.getRange(row,titleIndex,1,1).getValue();
    data.description=sheet.getRange(row,descriptionIndex,1,1).getValue();

    data.startDate = sheet.getRange(row,startDateIndex,1,1).getValue();
    data.endDate = sheet.getRange(row,endDateIndex,1,1).getValue();

    return data;
    };

    /*
    if a cell is edited in the sheet, get all the data of the corresponding row and
    create a new calendar event (after deleting the old event) by calling submitToGoogleCalendar()
    */

    function dataChanged(event){

    var sheet = SpreadsheetApp.getActiveSheet();
    var row = event.range.getRow();

    var eventId = sheet.getRange(row,googleCalendarIndex,1,1).getValue();

    var eventId = submitToGoogleCalendar(getSheetData(sheet,row),eventId)

    if(eventId!=null)
    sheet.getRange(row,googleCalendarIndex,1,1).setValue(eventId);

    };

    Posted by Natalia Vang | December 2, 2015, 7:54 pm
  14. Jeffrey,

    I want the calendar entry to read Verlander Vacation, but it’s only reading Verlander which is the info in column 2.

    Assuming that you are using the code in the blog post, refer to the function getSheetData. In this function, below the following line:

    data.title=sheet.getRange(row,titleIndex,1,1).getValue();
    add another line as follows:

    data.title = data.title + ” ” + sheet.getRange(row,3,1,1).getValue();
    Let me know if this is what you have asked for and if this worked for you.

    Posted by Shekhar | October 26, 2015, 3:18 pm

    This did work! Thanks!

    Is there a way to have the calendar entry default to an “All Day” event? Rather than have the (00:00) Start time?

    Thanks!

    Posted by Jeffrey | December 8, 2015, 3:04 am
  15. Natalia Vang,

    This is what I have in the script:

    Besides adding the new columns (index information) in the beginning of the script, you will have to add these new columns in the function getSheetData(sheet,row).

    For this replace the following lines in this function:

    data.title=sheet.getRange(row,titleIndex,1,1).getValue();
    data.description=sheet.getRange(row,descriptionIndex,1,1).getValue();
    

    with:

    data.title=sheet.getRange(row,fullNameIndex,1,1).getValue();
    data.description=sheet.getRange(row, schoolIndex,1,1).getValue();
    data.description+=sheet.getRange(row, fossKitIndex ,1,1).getValue();
    

    Note that the above will set the title of the calendar to full name. The school and foss kit will be added to the description of the calendar. Hope it helps.

    Posted by Shekhar | December 8, 2015, 10:38 pm
  16. Hi Jeffrey,

    Is there a way to have the calendar entry default to an “All Day” event? Rather than have the (00:00) Start time?

    An all day event will not have an end date (or you will have to create multiple all day events). You can use the function createAllDayEvent of the Calendar.

    Following is the modified code for my submitToGoogleCalendar() function which creates an all day event based on the start date.

    function submitToGoogleCalendar(sheetData,eventId) {
      // some simple validations ;-)
      if(sheetData.title == "" || sheetData.startDate == "" || sheetData.startDate == null)
        return null;
      
      var cal = CalendarApp.getCalendarById(calendarId);
      
      var start = new Date(sheetData.startDate);
      
    
      var event = null;
      
      //if eventId is null (when called by newEvent()) create a new calendar event
      if(eventId==null)
      {
      event = cal.createAllDayEvent(sheetData.title, start, {
          description : sheetData.description,      
      });
      return event.getId();  
      }
      /*
      else if the eventid is not null (when called by dataChanged()), delete the calendar event 
      and create a new event with the modified data by calling this function again
      */
      else
      {
       event = cal.getEventSeriesById(eventId);
       event.deleteEventSeries();   
       return SubmitToGoogleCalendar(sheetData,null)        
      }
      
      return event.getId();
      
    };
    

    Hope this helps

    Posted by Shekhar | December 17, 2015, 2:43 pm
  17. Is there any way to create a multiday all day event by inserting code to include the end date?
    With you script suggestion on Dec 17th, it works really well, but events entered that span over more than one day is currently only created as one day events on the calendar. I need to be able to create all day events that are more than one day (eg, leave requests).
    Many thanks

    Posted by Nanette | January 14, 2016, 2:53 am
  18. Hi Nanette,

    Is there any way to create a multiday all day event by inserting code to include the end date?

    Refer to the function createAllDayEventSeries https://developers.google.com/apps-script/reference/calendar/calendar?csw=1#createAllDayEventSeries

    Posted by Shekhar | January 16, 2016, 7:55 pm
  19. Hi Shekhar,

    The code works perfectly the way it is for all day/multiple day events. For events that just happen over a few hours, how to add a start time and end time (and omit the end day).
    Thank you

    Posted by Vithya Premkumar | February 9, 2016, 8:45 am
  20. need your help, can i get your direct email ID.
    I need phone number sync, and auto sms to be sent to the person takgn the appointment.

    Posted by sudhir ajja | February 9, 2016, 6:20 pm
  21. Hi Vithya Premkumar,

    The code works perfectly the way it is for all day/multiple day events. For events that just happen over a few hours, how to add a start time and end time (and omit the end day).

    Are you using the Date Question Type in Google Forms with Include time (checked) for the end date?

    Posted by Shekhar | February 9, 2016, 10:43 pm
  22. Hi Shekhar,
    I’ve ticked ‘Include time’ for ‘Start Date’ as well as ‘End Date’ field. It registers the starting time correctly in the calendar, but the end time is defaulted to 23:59 hrs, irrespective of whatever time was entered in the field. In fact, in my situation, I don’t need an ‘End Date’, or ‘End Time’, as the ‘End time’ is always 1hr after ‘Start Time’. Can this be automated as well.
    Thank you
    Vithya

    Posted by Vithya Premkumar | February 10, 2016, 3:49 am
  23. Hi sudhir ajja,

    need your help, can i get your direct email ID.

    I have your gmail ID and dropping you an email with the subject “Google Apps Script: Automatically create Calendar Events from Google Form”. Let’s discuss!

    Posted by Shekhar | February 11, 2016, 1:13 am
  24. Hi Vithya Premkumar,

    I don’t need an ‘End Date’, or ‘End Time’, as the ‘End time’ is always 1hr after ‘Start Time’. Can this be automated as well.

    try the following code:

    var end =  new Date(sheetData.startDate);
    end.setHours ( end.getHours() + 1 );
    

    Posted by Shekhar | February 11, 2016, 1:26 am
  25. Hi,

    I’m trying to get this to work so that rugby matches entered on a spreadsheet can be synced to the club’s calendar and hence to everyone’s phones and the intranet.

    In my sheet i have the following fields:
    eventID
    title
    description
    startDate
    endDate
    Google Calendar Event ID

    Then i have copied the script at the top of the post and replaced with my calendar ID.

    I’ve set up the events bit nothing happens.

    When i run each script manually i get the following errors:

    findRow – no error/no success

    newEvent
    Cannot find method getRange((class),number,number,number). (line 48, file “Code”)Dismiss

    getSheetData
    TypeError: Cannot call method “getRange” of undefined. (line 48, file “Code”)Dismiss

    dataChanged
    TypeError: Cannot read property “range” from undefined. (line 65, file “Code”)Dismiss

    submitToGoogleCalendar
    TypeError: Cannot read property “title” from undefined. (line 83, file “Code”)Dismiss

    Can you see where i’m going wrong?

    Here’s the full code pasted below:

    //insert your google calendar ID
    var calendarId = “blahblahblah@group.calendar.google.com”;

    //index (starting from 1) of each column in the sheet
    var titleIndex = 2;
    var descriptionIndex = 3;
    var startDateIndex = 4;
    var endDateIndex = 5;
    var googleCalendarIndex = 6;

    /*
    find the row where the Google Calendar Event ID is blank or null
    The data of this row will be used to create a new calendar event
    */
    function findRow(sheet) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();

    for (var i = 0; i end)
    return null;

    var event = null;

    //if eventId is null (when called by newEvent()) create a new calendar event
    if(eventId==null)
    {
    event = cal.createEvent(sheetData.title, start, end, {
    description : sheetData.description,
    });
    return event.getId();
    }
    /*
    else if the eventid is not null (when called by dataChanged()), delete the calendar event
    and create a new event with the modified data by calling this function again
    */
    else
    {
    event = cal.getEventSeriesById(eventId);
    event.deleteEventSeries();
    return submitToGoogleCalendar(sheetData,null);
    }

    return event.getId();

    };

    Posted by Matt | February 12, 2016, 7:25 pm
  26. BTW – i’m not getting the sheet data from a form and just entering it directly

    Posted by Matt | February 12, 2016, 7:26 pm
  27. Hi Matt,

    Then i have copied the script at the top of the post and replaced with my calendar ID. I’ve set up the events bit nothing happens.

    The code you have posted in your comment is very different from the code in my blog post. For example, the findrow function in my code looks as follows:

    function findRow(sheet) {
     var sheet = SpreadsheetApp.getActiveSheet();
     var dataRange = sheet.getDataRange();
     var values = dataRange.getValues();
    
     for (var i = 0; i < values.length; i++) {
     if(values[i][googleCalendarIndex-1]=="" || values[i][googleCalendarIndex-1]==null)
     newEvent(i+1);
     } 
     };
    

    Is this a copy paste issue? I guess, changing the indexes and the google calendar ID should have worked for you. Lemme know

    Posted by Shekhar | February 17, 2016, 2:34 pm
  28. Thanks for your reply, Shekhar. The form is now updating the Start and End times correctly.

    Posted by Vithya Premkumar | February 23, 2016, 8:39 am
  29. Hi Shekhar,

    If someone edits an entry, the calendar is not getting updated (the response form gets updated). I’ve set up the triggers as you’d mentioned. Could you please help.

    Posted by Vithya Premkumar | February 23, 2016, 8:43 am
  30. Hi Vithya Premkumar,

    Thanks for your reply, Shekhar. The form is now updating the Start and End times correctly.

    Great! I’m glad it worked. Thanks for the update 🙂

    Posted by Shekhar | February 23, 2016, 4:59 pm
  31. Vithya Premkumar,

    If someone edits an entry, the calendar is not getting updated (the response form gets updated). I’ve set up the triggers as you’d mentioned. Could you please help.

    How is the entry being edited? In the spreadsheet?

    Posted by Shekhar | February 23, 2016, 4:59 pm
  32. Thanks again for your reply, Shekhar. I edited the form with different dates. The spreadsheet got updated but the calendar did not.

    Posted by Vithya Premkumar | February 24, 2016, 2:55 am
  33. I cannot get the code to work. I am not sure if I have input the code wrong or am not sure how to align the response spreadsheet to what is being asked in the sample code you provided.

    Posted by Leron Rahynes | February 25, 2016, 5:24 am
  34. Hi Vithya Premkumar,

    Thanks again for your reply, Shekhar. I edited the form with different dates. The spreadsheet got updated but the calendar did not.

    Unfortunately this is a limitation with app script. The trigger is invoked only when editing is done directly in the spreadsheet.

    Posted by Shekhar | February 25, 2016, 1:40 pm
  35. Hi Leron Rahynes,

    I cannot get the code to work.

    Please provide me some details e.g. any error messages

    I am not sure if I have input the code wrong or am not sure how to align the response spreadsheet to what is being asked in the sample code you provided

    Are you using the same form and code that I have explained in the blog post? if not, I suggest first get the example in this blog post working. Then try out modifications to it.

    Posted by Shekhar | February 25, 2016, 1:46 pm
  36. I received an email with the following message on two different days:

    2/23/16 6:03 AM dataChanged TypeError: Cannot call method “deleteEventSeries” of null. (line 114, file “Code”) edit 2/23/16 6:03 AM

    Error Message Count
    ReferenceError: “on” is not defined. (line 56, file “Code”) 176

    Posted by Leron Rahynes | February 25, 2016, 3:51 pm
  37. Leron Rahynes,

    I received an email with the following message on two different days:

    Did you replace the insert-calendar-id in your script as explained in the blog post?

    In the above script, replace insert-calendar-id with the ID of your Google Calendar. The way to find the calendar ID is to browse to calendar.google.com. Login and move the mouse over on the calendar on the left. If you have more than one calendar, select the calendar in which you want the form data to sync. Click on the down arrow image which appears and select Calendar settings. On the subsequent page, copy the calendar ID (shown against Calendar Address:). In the above script, replace insert-calendar-id with the calendar ID that you copied. Next, on the menu, click on the save icon to save the script.
    

    Note: the double quotes will remain. For example:

    var calendarId = “your calendar ID here within the double quotes”;

    Posted by Shekhar | February 29, 2016, 11:34 pm
  38. Your script was exactly what I was looking for, I did minor tweaks to get the description as I required BUT the dataChanged() function will just not work.
    I have not made any changes but all I get is the ‘TypeError: Cannot read property “range” from undefined’ on the row: var row = event.range.getRow();

    Full function shown below:
    function dataChanged(event){

    var sheet = SpreadsheetApp.getActiveSheet();
    var row = event.range.getRow();

    var eventId = sheet.getRange(row,googleCalendarIndex,1,1).getValue();

    var eventId = submitToGoogleCalendar(getSheetData(sheet,row),eventId)

    if(eventId!=null)
    sheet.getRange(row,googleCalendarIndex,1,1).setValue(eventId);

    };

    Any help would be very much appreciated

    Posted by Ken Paine | March 1, 2016, 10:05 pm
  39. Hi Ken Paine,

    Any help would be very much appreciated

    did you by mistake setup the script on the Google Form? The form also has a Script Editor (under tools) which should not be used.

    You should click on Responses>View Responses. This will show you the responses spreadsheet. On this page, click on Tools>Script Editor and copy/write the script.

    Let me know.

    Posted by Shekhar | March 5, 2016, 10:06 pm
  40. Hi Shekhar,
    For some reason, the ‘Description’ gets updated on the ‘Google Calendar Event ID’ column (column 6), though the script says:
    var titleIndex = 2;
    var descriptionIndex = 3;
    var startDateIndex = 4;
    var locationIndex = 5;
    var googleCalendarIndex = 6;

    But once I copy and paste it in the Descrition column, the Google Calendar ID gets generated and my calendar gets updated as well. Why does this happen?
    Kind regards
    Vithya

    Posted by Vithya Premkumar | March 7, 2016, 3:29 am
  41. This looks great! Is it possible to adjust the script so that it can create multiple calendar events (all different times on same day) from start/end times on the same row of the spreadsheet?

    Thank you!

    Posted by Brian Pugh | March 9, 2016, 6:56 pm
  42. Hi Vithya Premkumar,

    But once I copy and paste it in the Descrition column, the Google Calendar ID gets generated and my calendar gets updated as well. Why does this happen?

    Can’t guesses 🙁 The following line in the function newEvent() updates the calendar id when the form is submitted:

    sheet.getRange(row,googleCalendarIndex,1,1).setValue(eventId);
    

    Posted by Shekhar | March 14, 2016, 5:12 pm
  43. For some reason, the ‘Description’ gets updated on the ‘Google Calendar Event ID’ column (column 6), though the script says:
    var titleIndex = 2;
    var descriptionIndex = 3;
    var startDateIndex = 4;
    var locationIndex = 5;
    var googleCalendarIndex = 6;

    Posted by Vithya Premkumar | March 15, 2016, 5:58 am
  44. When I delete an event from the spreadsheet, it doesn’t get deleted from the Google calendar. Is there a way to set that up?

    Thank you for this tutorial. I work for a small library, and this setup is going to save me and my staff so much time!

    Posted by ML Ingram | March 29, 2016, 9:36 pm
  45. Is there a way to have the calendar entry default to an “All Day” event? Rather than have the (00:00) Start time?

    Posted by Jeffrey | March 31, 2016, 7:19 am
  46. What script can I write to check the All Day event box? If this box is checked, then (00:00) start time doesn’t appear. Thanks!

    Posted by Jeffrey | March 31, 2016, 8:48 am
  47. Thanks so much for your work on this script. I have a form for folks to fill out when they plan tol be on vacation. I have been working all day with this scrip trying to get these dates to go directly into my Google calendar. I have successfully got the start date (which is in the 5th column of my spreadsheet) but I can’t seem to get the end date (7th column) to register.

    It seems too that I can’t get the calendar to change when the dates in the form are edited.

    The script returns the following errors when I try to run the different functions:

    findRow – no errors
    newEvent – Cannot find method getRange((class),number,number,number). (line 49, file “Code”)
    getSheetData – TypeError: Cannot call method “getRange” of undefined. (line 49, file “Code”)
    datChanged – TypeError: Cannot read property “range” from undefined. (line 66, file “Code”)
    submitToGoogleCalendar – TypeError: Cannot read property “title” from undefined. (line 84, file “Code”)

    I did change the script to create all day events rather than including a time.

    It would be great if the script could make the respondent’s vacation all one event using the start and end dates they submit but I’d be thrilled if it just recorded both these dates on the calendar as two separate events.

    I was also wondering how I would get the script to post an additional set of start and end dates from possible second vacation dates which are also in my original form. Any help is very much appreciated.

    I will paste my script below:

    //insert your google calendar ID
    var calendarId = “actionintl.org_tseh1i7odr6cgtibcadt1tkc2s@group.calendar.google.com”;

    //index (starting from 1) of each column in the sheet
    var titleIndex = 3;
    var descriptionIndex = 2;
    var startDateIndex = 5;
    var endDateIndex = 7;
    var googleCalendarIndex = 14;

    /*
    find the row where the Google Calendar Event ID is blank or null
    The data of this row will be used to create a new calendar event
    */
    function findRow(sheet) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();

    for (var i = 0; i < values.length; i++) {
    if(values[i][googleCalendarIndex-1]=="" || values[i][googleCalendarIndex-1]==null)
    newEvent(i+1);
    }
    };

    /*
    get the data of the new row by calling getSheetData() and
    create a new Calendar event by calling submitToGoogleCalendar()
    */

    function newEvent(row){
    var spread = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spread.getSheets()[0];
    var eventId = submitToGoogleCalendar(getSheetData(sheet,row),null)
    if(eventId!=null)
    sheet.getRange(row,googleCalendarIndex,1,1).setValue(eventId);
    };

    /*
    Store the data of a row in an Array
    */

    function getSheetData(sheet,row)
    {
    var data = new Array();

    data.title=sheet.getRange(row,titleIndex,1,1).getValue();
    data.description=sheet.getRange(row,descriptionIndex,1,1).getValue();

    data.startDate = sheet.getRange(row,startDateIndex,1,1).getValue();
    data.endDate = sheet.getRange(row,endDateIndex,1,1).getValue();

    return data;
    };

    /*
    if a cell is edited in the sheet, get all the data of the corresponding row and
    create a new calendar event (after deleting the old event) by calling submitToGoogleCalendar()
    */

    function dataChanged(event){

    var sheet = SpreadsheetApp.getActiveSheet();
    var row = event.range.getRow();

    var eventId = sheet.getRange(row,googleCalendarIndex,1,1).getValue();

    var eventId = submitToGoogleCalendar(getSheetData(sheet,row),eventId)

    if(eventId!=null)
    sheet.getRange(row,googleCalendarIndex,1,1).setValue(eventId);

    };

    /*
    This function creates an event in the Google Calendar and returns the calendar event ID
    which is stored in the last column of the sheet
    */
    function submitToGoogleCalendar(sheetData,eventId) {
    // some simple validations 😉
    if(sheetData.title == "" || sheetData.startDate == "" || sheetData.startDate == null)
    return null;

    var cal = CalendarApp.getCalendarById(calendarId);

    var start = new Date(sheetData.startDate);

    var event = null;

    //if eventId is null (when called by newEvent()) create a new calendar event
    if(eventId==null)
    {
    event = cal.createAllDayEvent(sheetData.title, start, {
    description : sheetData.description,
    });
    return event.getId();
    }
    /*
    else if the eventid is not null (when called by dataChanged()), delete the calendar event
    and create a new event with the modified data by calling this function again
    */
    else
    {
    event = cal.getEventSeriesById(eventId);
    event.deleteEventSeries();
    return SubmitToGoogleCalendar(sheetData,null)
    }

    return event.getId();

    };

    Posted by Mike Gingerich | April 2, 2016, 7:39 pm
  48. Hi Jeffrey,

    Is there a way to have the calendar entry default to an “All Day” event? Rather than have the (00:00) Start time?

    Refer to http://www.shekhargovindarajan.com/google/google-apps-script-automatically-create-calendar-events-from-google-form/#comment-246100

    Posted by Shekhar | April 5, 2016, 2:43 pm
  49. Thank you so much for your help! I couldn’t get my form to work or add the dates to my calendar. Thanks for finding what I was doing wrong in the script. The school principal I was working with is now able to implement a system for finding coverage for his teachers.

    Posted by Leron Rahynes | April 5, 2016, 7:03 pm
  50. question, I have put this script on my form and am having a few errors. I can not set up the trigger “dataChanged” with spreadsheet option and I want to make the appointment 2 hours by default. can you help me?

    Posted by Art Edelhoff | April 5, 2016, 8:05 pm

Post a comment

Recent Tweets

Follow Me on Twitter