// 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. Hi Mike Gingerich

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

    This is a known issue. When you edit the dates in the google form, the dataChanged event is not triggered. The dataChanged event is triggered only when you edit the Responses sheet directly

    Posted by Shekhar | April 22, 2016, 1:38 am
  2. I keep getting the following error: TypeError: Cannot read property “title” from undefined. (line 83, file “Code”)

    Here is the code:
    //insert your google calendar ID
    var calendarId = “HIDDEN FOR THIS POST”;

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

    /*
    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 Courtney Whitaker | April 26, 2016, 11:11 pm
  3. I’m trying to get this work but I couldn’t sadly.

    I’m trying to use Appsheet to enter the form and the infomation gets updated into google calendar. Is that possible to do.

    Please could you help me out.

    Here is what I have.

    I have a Google Form with four entries.

    1. short text
    2. Date and Time
    3. short text
    4. multi select

    Thank you for your help!!

    Posted by Spencer | April 30, 2016, 1:22 pm
  4. The above message can be ignored, I have fixed it. But now my final problem is that when I update or delete a row in AppSheet, it only updates to the response sheet and not to google calendar. Please give me some help I’m very close to it. Many thanks!!

    Posted by Spencer | May 2, 2016, 4:36 pm
  5. I read all the comments from top to bottom, but could not find a solution for my issue. I’m getting errors of “TypeError: Cannot call method “createEvent” of null. (line 104, file “Code”)”. I do no know what I have done wrong. Can you please help me to get it working? Thanks in advance.

    https://docs.google.com/spreadsheets/d/19X3-onclS89Ui3tP-aHfSTWe4sEMYtHh1Mp9oP601B0/edit

    Posted by Anuradha | May 3, 2016, 10:44 am
  6. This is amazing. Thank you so much. It is working for me but it is putting the event for 24 hours solid in the calendar. Is there a way to have the option of a time period or the event is made “All Day” in google calendar so it appears at the top of the day instead of taking up the entire block. Thanks!

    Posted by Maggie | May 21, 2016, 12:44 am
  7. Shekhar please can you share de modified code that can include the start and end time of each events in calendar?? I try modify de code with get.sheetdate wwith no sucess. Thanks

    Posted by Daniel A. | May 24, 2016, 4:20 am
  8. If an event is deleted on the Google spreadsheet, it is not deleted from the Google calendar. Is there a way to make sure that events deleted from the spreadsheet are also removed from the calendar?

    Thanks!

    Posted by Ingram | June 15, 2016, 10:17 pm
  9. I have form that will be created that will have 21
    questions on it. Start and End Date are the last two columns, 20 and 21. I added the “Google Calendar Event ID” to column 22. when I run the form, all the columns populate with their respective answers, but when it comes to the EVENT id, nothing shows. I figured I should change the column number as it was 6 in your example, to 22. Still when I run the form, nothing populates into the Calender event ID. Am I missing something? I am very much a novice at scripting, and was amazed that I got it to work the first time. any help would be greatly appreciated!

    Thanks

    Posted by Robin Thomas | June 30, 2016, 1:55 am
  10. Hi Shekhar,
    Thanks for the post. Really helpful.
    Is there a simple modification to the script such that each sheet is mapped to a specific calendar? For example, changes to Sheet1 will add events to Calendar1 and changes to Sheet2 will add events to Calendar2.
    Thanks!

    Posted by Michael Guigli | July 7, 2016, 9:08 pm
  11. Thank you for your very nice tutorial, works like a charm!

    But I would like to go a bit further. In my form I have field called ID. I want the code to check wether this ID already exist in the spreadsheet, and if it does I want data from the form to update that corresponding row rather than creating a new one. And then delete and create a calendar event just as if I was updating manually in the spreadsheet.

    One solution could maybe be to delete the calendar event corresponding to the old row, and then create a new row and a new calendar event?

    Is that even possible?

    Posted by Ingeborg | August 8, 2016, 2:23 pm
  12. @Anuradha I looked at your link and it seems like you might have gotten things working. Can you tell me what you did? I am getting the same error.

    Posted by Greg | October 12, 2016, 11:38 pm
  13. Thank you so much for sharing this – exactly what I needed. Especially thank you for answering questions in the comments – several of the modifications I wanted to make were included there.

    Now to go troubleshoot my additional modifications!

    Posted by Melinda | October 13, 2016, 8:35 pm
  14. Found it. I had a space ahead of my google calendar address. Noob mistake.

    I have added a location field to my form.
    Can anyone tell me how to add to the script code so that the resultant spreadsheet info (column 6) auto-propagates in the Google calendar event Location field when the new event is created?

    I see pieces of it mentioned above but I’m new to coding and need a little more detail about what goes where. My attempts keep breaking my script.

    My code:

    var calendarId = “XYZ.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 locationIndex = 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 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 Greg | October 13, 2016, 9:17 pm
  15. @Greg –

    Here’s how I modified mine to get the location to populate the event (note it is the addition of a line within the squiggly brackets with the description info):

    //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,
    location : sheetData.location,
    });
    return event.getId();
    }

    Posted by Melinda | October 13, 2016, 11:35 pm
  16. @Greg
    I pulled into an Array so I also had it in this section:
    /*
    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.location=sheet.getRange(row,locationIndex,1,1).getValue();
    data.startDate = sheet.getRange(row,startDateIndex,1,1).getValue();
    data.endDate = sheet.getRange(row,endDateIndex,1,1).getValue();
    data.kit = sheet.getRange(row,kitIndex,1,1).getValue();
    data.items = sheet.getRange(row,itemsIndex,1,1).getValue();
    data.number = sheet.getRange(row,numberIndex,1,1).getValue();
    data.ipad = sheet.getRange(row,ipadIndex,1,1).getValue();

    return data;
    };

    Posted by Melinda | October 13, 2016, 11:39 pm
  17. I would like the script to create calendar entries on different calendars depending on the responses. Is there any way to do this?

    Posted by Dan Oliphant | November 29, 2016, 9:46 am
  18. I’ve been playing around with this and have the basic thing working, which is great. However, I’m struggling figuring out two things:

    1) How can I invite guests from a column in the form? It seems I need to use .addGuest(email) somewhere, but I can’t figure out where. I’ve tried this including adding all the normal bits up top, but no luck:

    //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,
    location : sheetData.location,
    }).getEventSeriesById(eventId).addGuest(sheetData.mainGuest);

    return event.getId();
    }

    2) I added location as Melinda described below and it works well to include a string. However, I’d like to have it actually include the location of a real room that’s in Google Calendar. A.k.a. where you’d click rooms and book an existing room already in google calendar. The rooms seem to function as calendars, each with their own ID.

    Any ideas? Thanks so much for your help!

    Posted by Nicole | January 26, 2017, 1:12 am
  19. HI and thank you Shekhar, so close from what i need,,
    is there a way to add options to the event from form and sheet ?
    i need to add files from drive attached to the event, from a list of files in the sheet or form.
    i also need to add location, guest from a list in a sheet
    and the color of the event.
    thanks for any help
    L

    Posted by lud | February 16, 2017, 1:01 am
  20. Thank you, Shekhar, this is helping on scheduling our vacations. But I am running into a problem, sometimes when it creates the calendar event it shows up on the day before the date selected. So if I put in 3/19/2017 it may show up on the calendar on 3/18/2017 even if the date in the spreadsheet shows 3/19. And it does not happen all the time, so we have to double check every entry. Can you help?

    Posted by Keith Hamilton | March 10, 2017, 9:55 pm
  21. Hi Dan Oliphant,

    would like the script to create calendar entries on different calendars

    Just change the calendar id based on your requirement. You can have multiple calendar IDs like:

    var calendarId = “insert-calendar-id”;
    var calendarId2 = “insert-calendar-id2”;

    Posted by Shekhar | March 10, 2017, 10:03 pm
  22. I am having issues with the script. I get the following errors

    Event Start time must be before event end time (line 102,file”code”)

    Cannot find method get/range((class),number,number,number). (line 48,file”code”)

    Thank you for any assistance

    Posted by Okhela | March 29, 2017, 12:31 am
  23. Apologies for not adding earlier, when I View – Execution Script I get the below error:

    17-03-28 12:24:18:046 PDT] Execution failed: ReferenceError: “descriptionIndex” is not defined. (line 48, file “Code”) [0.587 seconds total runtime]

    Thank you!

    Posted by Okhela | March 29, 2017, 12:56 am
  24. how can I add in color of the event and attached documents from the form? This would be helpful to coordinate our editorial calendar I am creating with your framework

    Posted by Justin | May 3, 2017, 2:09 am
  25. Hello, I’m not sure if your even still getting responses on this post, but I have to try…this is driving me crazy, lol. I recently got out of the Army and the little I have learned about scripts, editing…not creating, has been on down time during deployments and for specific purposes…with that being said I have made the google form to google sheet to work, but I cannot get it to create the event that it should, this script you have here looks promising but I cannot get it to work, https://docs.google.com/forms/d/e/1FAIpQLSfhBABzOGan2ZS3qGXGpGfBk-GnardihH7T5UpYG7M7zGFzYQ/viewform?c=0&w=1, that is the link to my form, if you can help me at all that would be GREATTTTTT!!!!!

    Posted by Erica | May 4, 2017, 8:28 am
  26. Hi,
    first of all thanks to Shekhar for sharing this nice piece of code
    I’d like to share the code modified for a restaurant reservation form with two different locations

    the following changes were made:
    – It uses two calendars, one for each restaurant, depending on which location, the event is created in one or another, in lines 128 and 139 you need to add a keyword that differentiate the two locations one from another, pe one is called “Bistro Bar Downtown” and the other “Bistro Bar Station”, the keyword for location1 is “Downtown” and for the second is “Station”
    – the start time of the event is a combination of two cells, one with the date and another with the time
    – the end is the same day as the start date of the event plus one hour, so it creates events of 1 hour, I kept the end date in case you want to use the other method
    – adds the phone and email to the description, but it could be used to add any other information in the description of the event
    – adds the number of guests to the title and the description
    – it has the functionality of add one guest and send a notification, but it’s deactivated. Just delete the two // to uncomment and the functionality will be activated (lines 133, 134, 144, 145)

    what doesn’t work and I don’t know how to fix is:
    – if changes are made to the spreadsheet it creates a new event but it doesn’t delete the old one, so keep it mind it basically duplicates the event it doesn’t update the old one.

    for the rest just follow the amazingly well written instructions from Shekhar in the original code

    hope it helps

    thanks,
    Alvaro

    //insert your google calendar ID
    var calendarId1 = “INSERT CALENDAR1”;
    var calendarId2 = “INSERT CALENDAR2”;

    //index (starting from 1) of each column in the sheet
    var titleIndex = 7;
    var descriptionIndex = 6;
    var startDateIndex = 4;
    var endDateIndex = 4;
    var googleCalendarIndex = 10;
    var locationIndex = 3;
    var startTimeIndex = 5;
    var dateTimeIndex = 11;
    var phoneNumberIndex = 8;
    var emailIndex= 9;

    /*
    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][dateTimeIndex-1]==="" || values[i][dateTimeIndex-1]===null)
    timeAdd(i+1);
    }

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

    //if eventId is null (when called by newEvent()) create a new calendar event
    // insert a keyword that is unique in the name of the first location
    if(!eventId && (place.indexOf(‘insert a keyword here for location1’) !== -1))
    {
    event = cal1.createEvent(sheetData.title, start, end, {
    description : “Number of guests: ” + sheetData.description + “” + “Contact Number: “+ sheetData.phoneNumber,
    location : sheetData.location,
    //guests : sheetData.email,
    //sendInvites : true,
    });
    return event.getId();
    }
    // insert a keyword that is unique in the name of the first location
    else if(!eventId && (place.indexOf(‘insert a keyword for location1’) !== -1))
    {
    event = cal2.createEvent(sheetData.title, start, end, {
    description : “Number of guests: ” + sheetData.description + “” + “Contact Number: “+ sheetData.phoneNumber,
    location : sheetData.location,
    //guests : sheetData.email,
    //sendInvites : true,
    });
    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 = cal1.getEventSeriesById(eventId);
    event.deleteEventSeries();
    return submitToGoogleCalendar(sheetData,null);
    event = cal2.getEventSeriesById(eventId);
    event.deleteEventSeries();
    return submitToGoogleCalendar(sheetData,null);
    }

    return event.getId();

    };

    Posted by Alvaro | January 24, 2018, 9:15 pm

Post a comment

Recent Tweets

Follow Me on Twitter