Updating Google Calendars from a Google Spreadsheet

49 thoughts on “Updating Google Calendars from a Google Spreadsheet”

1. Interesting script!

   Wondering if the other way around is possible? Or should I make the calendar public and import in Google Docs? It would be great for logging hours for projects or declaring working hours ;-)

   1. Yes, the other way round does appear to be possible – e.g. https://ouseful.wordpress.com/2010/03/05/grabbing-google-calendar-event-details-into-a-spreadsheet/

   2. Thanks! :-)
2. Pingback: Grabbing Google Calendar Event Details into a Spreadsheet « OUseful.Info, the blog…
3. Pingback: Maintaining a Google Calendar from a Goole Spreadsheet, Reprise « OUseful.Info, the blog…

4. If data from spreadsheet to calendar is possible, I guess it must be also posssible to push data to the gmail contactslist.
   This would be very easy when people can fill in there own adress,phone, birtday etc using google form. All that data is collected in to the spreadsheet and at the end of the day we run a script and all that goes to your contactlist. Whishfull thinking? you never know :-)

5. I really like this script. There is an app thats way too complicated to configure

   http://code.google.com/apis/gdata/articles/spreadsheets_calendar_base_mashup.html

   Can I ask if there is a way to stop duplicated entry? Everey time you run the function it just creates another calendar entry. Can it be configured to update existing entries using title as a unique field?

   1. “Can I ask if there is a way to stop duplicated entry? Every time you run the function it just creates another calendar entry. Can it be configured to update existing entries using title as a unique field?”

      Could help me with this process? We’d like to use this at my institution.

6. spoke to soon, noticed you already found a way :)

   1. I was just trying to figure out how to do this thanks! I can’t seem to make it work without doing creating duplicates. How did you get it to update the existing events.

      david

7. This is awesome

8. how do i update an event that is already created? I don’t want to duplicate with new dates and leave the old behind. Also single day events show up about 50% of the time.

   I have copied the script above, and edited the cal.createEvent with the new commands but still isn’t working.

   I would greatly appreciate some help.

   Thank you

   1. @craig It’s been some time since I looked at the API, but when I cobbled together the script I seem to remember that updating pre-existing events wasn’t an option… I will try to make time to see if that functionality is now supported – but I can’t guarantee when…

9. Did anyone got the solution for updating exisiting events and avoiding duplicate items in calendar. Thanks

10. I was wondering if we could work together to make it so that it does not duplicate events when the script runs.

    I am a Professor and Librarian looking to create a bookings module, which this would allows me to do.

    Ultimately, what I want to to is make a Google Form, that is also a spreadsheet, and then have a script run that exports all new events from the spreadsheet into a Google Calendar.

    I look forward to your reply.

    Matt

    1. I am wondering the exact same thing, using this and the reprise of this post I have been able to make it publish to a specific calendar, but every time I run the function it recreates the event.

       Any help would be great!

11. I found this in the Calendar API. Maybe this would help with the updating events problem, and stop the creation of duplicates. I’m really interested in solving this problem and so are my co-workers and other academic institutions. I look forward to working with you.

    http://code.google.com/apis/calendar/data/2.0/developers_guide_java.html#UpdatingEvents

    1. Here is a page of JavaScript features, including a calendar update

12. http://code.google.com/apis/calendar/data/1.0/developers_guide_js.html#Interactive_Samples

13. Has a solution been found? I would really be interested in this functionality too, regarding the updating of times without creating a duplicate.

14. For many years I have wanted to be able to use, administer and update my relational database information in an active spreadsheet, linked to an events list or even a call list. Programmers right and left have pishawed me and said it ain’t going to happen.

    I hoped Google Apps would have this feature, but I sure can’t find it. For instance, I would like to see all my Google calendar events on a spreadsheet and be able to update that calendar on either the event view or in that synchronized active spreadsheet view.

    This would not involve any exporting or importing of course.

    Any suggestion?
15. Pingback: The ProAct Project (Spring 2012)

16. Hi,

    Great tutorial! This is exactly what I’m trying to do at work, managing hundreds of meetings for individual clients.

    Anyone know how I can add the info to a particular “sub” calendar that’s not my default? I’ve tried various functions but just can’t get it to work.

17. Why do I get this error?

    Event start time must be before event end time. (line 16) Dismiss

    Current Script

    function caltest1() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // First row of data to process
    var numRows = 2; // Number of rows to process
    var dataRange = sheet.getRange(startRow, 1, numRows, 5);
    var data = dataRange.getValues();
    var cal = CalendarApp.getDefaultCalendar();
    for (i in data) {
    var row = data[i];
    var title = row[0]; // First column
    var desc = row[1]; // Second column
    var tstart = row[2];
    var tstop = row[3];
    var loc = row[4];
    //cal.createEvent(title, new Date(“March 3, 2010 08:00:00”), new Date(“March 3, 2010 09:00:00”), {description:desc,location:loc});
    cal.createEvent(title, new Date(tstart), new Date(tstop), {description:desc});
    }
    }

18. hola a tod@s solicitando su valiosa ayuda de porque obtengo este error:

    Cannot find method createEvent(string,string,string,object). (Línea 15)Ignorar

    agradecere su valioso apoyo saludos.

19. Wow, thank you for sharing. !!

    I have a calendar account, I have a form that spopulates a spreadsheet, and I hope to puplate the calendar with the entries the form captures into the spreadsheet.

    I just have to figure out what to do with that function code you provided near the top of this page!

    Does it go into the spreadsheet…nop, the calendar, right?

    Thanks, in any event!

    Tom
20. Pingback: Maintaining a Google Calendar from a Google Spreadsheet, Reprise « OUseful.Info, the blog…

21. Is it possible to make it create an “all day” event in the calendar rather than one with a start/stop time/date? And thanks btw for these great tutorials. So helpful!

    1. I figured it out. New to scripts – didn’t think the reference would be that easy to use.

22. Hi this is very nice thanks…. but how can i do for change another calendar for example a calendar that i have create?? thaks for your help…

23. Regarding the duplicate, I added a function that checks first if an event with the same title exists that day. You have to iterate through the returned CalendarEvent object and set a validator to false. Then, if the validator is still true after that recursive function, you can go ahead and add the event :)

    check = cal.getEventsForDay(new Date(tstop))
    for(event in check){
    var obj = check[event];
    if(title = obj.getTitle())
    validator = false
    }

    1. @Srefan – brilliant – thanks for contributing that… I guess I need to do a refresh of the post to make these insights easier to find:-)

    2. Oh and you better trim the titles before you compare them:

       for(event in check){
       var obj = check[event];
       found_title = obj.getTitle();
       if(trim(title) == trim(found_title)){
       validator = false
       }

       You can use a trim function like this:
       function trim(s) {
       return s.replace(/^\s\s*/, ”).replace(/\s\s*$/, ”);
       }

       Next, i’ll see if it is possible to update an event. Or delete / create it again.

    3. I am an amateur at programming, so could you please tell me how to solve this error:
       “Cannot call method “replace” of undefined” . I get this error for the “replace” in the trim function.

    4. hi I am new to script.. where do I put the check script for duplicate?

       function caltest1() {
       var sheet = SpreadsheetApp.getActiveSheet();
       var startRow = 2; // First row of data to process
       var numRows = 100; // Number of rows to process
       var dataRange = sheet.getRange(startRow, 1, numRows, 5);
       var data = dataRange.getValues();
       var cal = CalendarApp.getDefaultCalendar();
       for (i in data) {
       var row = data[i];
       var title = row[0]; // First column
       var startDate= row[2];
       var desc = row[1]; // Second column
       var endDate= row[3];
       var name= row[4];
       //cal.createEvent(title, new Date(“March 3, 2010 08:00:00”), new Date(“March 3, 2010 09:00:00”), {description:desc,location:loc});
       cal.createEvent(title,new Date(startDate), new Date(endDate), {description: desc, location: name});
       sheet.getRange(startRow + i, 6)
       }
       }

24. Very interested in a delete/update option.

25. Hi, great thread here :) i’ve been trying this and took a look at the duplicated event thread, however if we want to run the script to create event A and then want to update event A again how do we do it? How do we get the eventid from the event A we just created so that we can maybe update it?

    Thanks!

26. I’ve been looking for/working on a two way sync and here’s a proof of concept I got working. It still needs to be fined tuned, but I thought I would post it in case anyone has insight in optimizing the code, and for anyone that would find it useful. Basically the code creates an array from the calendar, and the spreadsheet, combines them. Sorts it on last modified (date last updated for event, and the standard scripted last modified column for spreadsheet), removes duplicates, and submits to both the calendar (currently after deleting all events) and the spreadsheet. I was planning on adding a key, and having a list where you would type the key to remove the items from both locations, the upside being you can add spreadsheet rows from calendar and vice versa. Thanks in advance for any helpful input.
    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: “Sync Spreadsheet to Calendar”, functionName: “calsync”}];
    //{name: “Sync”, functionName: “myimport”}];
    ss.addMenu(“Calendar Sync”, menuEntries);
    }

    function calsync()
    {
    // This function should be executed from the
    // spreadsheet you want to export to the calendar
    var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Test123”);

    var myCalendar = CalendarApp.openByName(“Test”);

    //calendar event array
    var events = myCalendar.getEvents(new Date(“January 1, 2011 EST”),
    new Date(“January 1, 2014 EST”));
    if (events[0]) {
    var eventarray = new Array();
    var line = new Array();
    line.push(‘Title’);
    line.push(‘Start Date’);
    line.push(‘End Date’);
    line.push(‘Description’);
    line.push(‘Last Modified’);
    eventarray.push(line);

    var i = 0;
    for (i = 0; i < events.length; i++) {
    line = new Array();
    line.push(events[i].getTitle());
    line.push(events[i].getStartTime());
    line.push(events[i].getEndTime());
    line.push(events[i].getDescription());
    line.push(events[i].getLastUpdated());
    //line.push(events[i].getLocation());
    eventarray.push(line);
    }
    } else {
    Browser.msgBox('nothing between ' + startDate + ' till ' + endDate);
    }

    var dataRange = mySpreadsheet.getRange("A2:E53");
    var data = dataRange.getValues();

    if (data[0]) {
    var dataarray = new Array();
    var line2 = new Array();

    var j = 0;
    for (j = 0; j < data.length; j++) {
    var row = data[j];
    line2 = new Array();
    line2.push(row[0]);
    line2.push(row[1]);
    line2.push(row[2]);
    line2.push(row[3]);
    line2.push(row[4]);
    //line.push(events[i].getLocation());
    //line.push((events[i].getEndTime() – events[i].getStartTime()) / 3600000);
    dataarray.push(line2);
    }
    } else {
    Browser.msgBox('nothing between ' + startDate + ' till ' + endDate);
    }

    var newarray = eventarray.concat(dataarray);
    uniquedata(newarray);

    }

    //found at https://developers.google.com/apps-script/articles/removing_duplicates
    function uniquedata(data) {
    var newData = new Array();
    var data2 = sort(data, 4, false);
    for(i in data2){
    var row = data2[i];
    var duplicate = false;
    for(j in newData){
    if(row[0] == newData[j][0]){
    duplicate = true;
    }
    }
    if(!duplicate){
    newData.push(row);
    }
    }
    var filtered = sort(newData, 4 , false);
    UpdateSpreadsheet(filtered);
    UpdateCalendar(filtered);
    // var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test123");
    // sheet.clearContents();
    // sheet.getRange(1, 1, filtered.length, filtered[0].length).setValues(filtered);
    }

    function UpdateSpreadsheet(data) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test123");
    sheet.clearContents();
    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    }

    function UpdateCalendar(data)
    {
    var myCalendar = CalendarApp.openByName("Test");

    // optional – delete existing events
    var events = myCalendar.getEvents(new Date("January 1, 2011 EST"),
    new Date("January 1, 2013 EST"));
    for (var i = 0; i 0) {
    if (typeof columnIndex != “number” || columnIndex > data[0].length) {
    throw ‘Choose a valide column index’;
    }
    var r = new Array();
    var areDates = true;
    for (var i = 0; i < data.length; i++) {
    var date = new Date(data[i][columnIndex]);
    if (isNaN(date.getYear()) && data[i][columnIndex] != '') areDates = false;
    else if (data[i][columnIndex] != '') data[i][columnIndex] = date;
    r.push(data[i]);
    }
    return r.sort(function (a, b) {
    if (ascOrDesc) return ((a[columnIndex] b[columnIndex]) ? 1 : 0));
    return ((a[columnIndex] > b[columnIndex]) ? -1 : ((a[columnIndex] < b[columnIndex]) ? 1 : 0));
    });
    }
    else {
    return data;
    }
    }

27. I found a solution for updating events, with the help of Waqar Ahmad. I’ve spent the last half hour watching events disappear and then reappear on my calendar. The code is available here:

    http://stackoverflow.com/questions/13005699/how-do-i-modify-a-google-calendar-event-using-google-apps-script/13019195#13019195

28. Here’s an updated version of the code I post. I’ve cleaned up the code, and added some comments, the biggest change is now I pull multiple columns into the description, so those can be updated in both places but the spreadsheet will keep the columns organized. It was the best solution I could think of without being able to add actual fields to the calendar event. I also put in some code to replace variations of initials and first names, my solution was hardcoded after I gave up finding a better way to do it, I would love to hear some suggestions on how to handle that batter. Hopefully this helps someone looking for something similiar to what I needed, I still have to work this in at a more global level to our task tracking workbooks, so I may or may not have another major update. I’m getting a funky extra title row, but won’t be able to revisit it until next week, I think I need to exclude a header row in one of the for loops.

    Hopefully someone finds this useful, suggestions welcomed:

    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: “Sync Spreadsheet to Calendar”, functionName: “calsync”},
    {name: “Clear Calendar”, functionName: “clearcalendar”}];
    //{name: “Sync”, functionName: “myimport”}];
    ss.addMenu(“Calendar Sync”, menuEntries);
    }

    function calsync()
    {
    // // Here are the steps the script follows
    // 1.Builds events array from calendar, if null ignores, i add a feature, or facility on a sheet level with a template sheet, so
    // 2.Get calendar from sheet, combine columns, and add headers so that many columns fit in description
    // 3. Concatenate arrays
    // 4.Filter for last modified, take most recent data
    // 5. replace initials or first name with full name
    // 6. array is ready to be sent back to calendar
    // 7. split out columns based on header (currently it depends on the order, I would like to be able to find based on header and header rank, and then send them in the standard order,
    // but since no one is adding new calendar events, just edditing its not a big deal.
    // Just a side note, I use the flagged for discussion column to query all sheets on all workbooks where a team member would like to raise issue, to achieve maximum visibility on questions/concerns.

    var myCalendar = CalendarApp.openByName(“Test2”);
    var events = myCalendar.getEvents(new Date(“January 1, 2011 EST”),
    new Date(“January 1, 2014 EST”));
    if (events[0]) {
    var eventarray = new Array();
    var line = new Array();
    line.push(‘Title’);
    line.push(‘Start Date’);
    line.push(‘End Date’);
    line.push(‘Description’);
    line.push(‘Last Modified’);
    eventarray.push(line);

    var i = 0;
    for (i = 0; i < events.length; i++) {
    line = new Array();
    line.push(events[i].getTitle());
    line.push(events[i].getStartTime());
    line.push(events[i].getEndTime());
    line.push(events[i].getDescription());
    line.push(events[i].getLastUpdated());

    eventarray.push(line);
    }
    }

    var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test2");
    var last_row = mySpreadsheet.getLastRow();
    var last_column = mySpreadsheet.getLastColumn();
    var dataRange = mySpreadsheet.getRange("A2:"+last_column+last_row);
    var data = dataRange.getValues();

    if (data[0]) {
    var dataarray = new Array();
    var line2 = new Array();

    var j = 0;
    for (j = 0; j 0) {
    if (typeof columnIndex != “number” || columnIndex > data[0].length) {
    throw ‘Choose a valide column index’;
    }
    var r = new Array();
    var areDates = true;
    for (var i = 0; i < data.length; i++) {
    var date = new Date(data[i][columnIndex]);
    if (isNaN(date.getYear()) && data[i][columnIndex] != '') areDates = false;
    else if (data[i][columnIndex] != '') data[i][columnIndex] = date;
    r.push(data[i]);
    }
    return r.sort(function (a, b) {
    if (ascOrDesc) return ((a[columnIndex] b[columnIndex]) ? 1 : 0));
    return ((a[columnIndex] > b[columnIndex]) ? -1 : ((a[columnIndex] 0){
    var i = 0;
    var dataArray = new Array();
    var line = new Array();
    line.push(‘Title’);
    line.push(‘Start Date’);
    line.push(‘End Date’);
    line.push(‘Description’);
    line.push(‘Last Modified’);
    dataArray.push(line);
    for (i = 0; i < data.length; i++) {
    line = new Array();
    var row = data[i];
    var temp = row[3];// for the initial replacement my solution is unacceptable longterm, I have to add people individually I know I could just do a simple table,
    // but I wanted to cover variation so from a spreadsheet I build out what I paste in, it's replace.(Resource: Initials, Full Name) or replace.(firstname) for everyone and all likely variations….
    // it ends up being a very long line of code, suggestions welcomed!
    line.push(row[0]);
    line.push(row[1]);
    line.push(row[2]);
    line.push(temp);
    line.push(row[4]);
    dataArray.push(line);
    }
    }
    UpdateCalendar(dataArray);
    Logger.log(dataArray);

    var dataarray2 = new Array();
    var line2 = new Array();
    line2.push('Flagged for Discussion')
    line2.push('Title');
    line2.push('Title');
    line2.push('Resource');
    line2.push('Notes');
    line2.push('Status');
    line2.push('Category');
    line2.push('Start Date');
    line2.push('Duration');
    line2.push('End Date');
    line2.push('Worksheet Link');
    line2.push('Last Modified');
    dataarray2.push(line2);

    var j = 0;
    for (j = 1; j < data.length; j++) {
    line2 = new Array();
    var row = dataArray[j];

    var length = row[3].length;
    var locnotes = row[3].indexOf('Notes:');
    var locresource = row[3].indexOf('Resource:');
    var locstatus = row[3].indexOf('Status:')
    var loccategory= row[3].indexOf('Category: ')
    var locflagged = row[3].indexOf('Flagged for Discussion:');
    var locwslink = row[3].indexOf('Worksheet Link:');

    var splittitle = row[0].split("- ");
    var shorttitle = splittitle[2];
    Logger.log(shorttitle);

    var notes = row[3].slice("notes: ".length+locnotes,locresource);
    var resource = row[3].slice( "resource: ".length+locresource,locstatus);
    var status = row[3].slice("status: ".length+locstatus,loccategory);//will be ,loccategory); if we add it
    var category = row[3].slice("category: ".length+loccategory,locflagged);
    var flagged = row[3].slice("Flagged for Discussion: ".length+locflagged,locwslink);
    var worksheetlink = row[3].slice("Worksheet Link: ".length+locwslink,length);

    line2.push(flagged);
    line2.push(row[0]);
    line2.push(splittitle[2]);
    line2.push(resource);
    line2.push(notes);
    line2.push(status);
    line2.push(category);
    line2.push(row[1]);
    line2.push(row[2]-row[1]); //this needs to be 7 if we delete duration
    line2.push(row[2]);
    line2.push(worksheetlink);
    line2.push(row[4]);
    dataarray2.push(line2);
    }
    UpdateSpreadsheet(dataarray2);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test3");
    sheet.clear();
    sheet.getRange(1, 1, dataarray2.length, dataarray2[0].length).setValues(dataarray2);
    }
    function UpdateCalendar(data)
    {
    var myCalendar = CalendarApp.openByName("Test2");

    // optional – delete existing events
    var events = myCalendar.getEvents(new Date("January 1, 2011 EST"),
    new Date("January 1, 2013 EST"));
    Logger.log(events);
    for (var i = 0; i < events.length; i++)
    {
    events[i].deleteEvent();
    }

    for (i in data)
    {
    var row = data[i];
    // assume that each row contains a date entry and a text entry
    var theTitle = row[0]; // First column of row
    var theStartDate = row[1]; // Second column of row
    var theEndDate = row[2]; // Third column of row
    var theDescription = row[3]; // Fourth column of row
    myCalendar.createEvent(theTitle, new Date(theStartDate),new Date(theEndDate),{description: theDescription});
    }

    }

    function UpdateSpreadsheet(data) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test2");
    sheet.clear();
    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    }

    function ClearCalendar()
    {
    var myCalendar = CalendarApp.openByName("Test2");

    // optional – delete existing events
    var events = myCalendar.getEvents(new Date("January 1, 2011 EST"),
    new Date("January 1, 2013 EST"));
    for (var i = 0; i < events.length; i++)
    {
    events[i].deleteEvent();
    }
    }

    1. These our my column headers that pull into the workbook in order:

       [Flagged for Discussion,Title,Title,Resource,Notes,Status,Category,Start Date,Duration,End Date,Worksheet Link,Last Modified]
       Handled in discussion for calendar event, in this order:
       Notes:
       Resource:
       Status:
       Category:
       Flagged for Discussion:

29. Hi cheney,

    Thanks for posting this script. This is exactly what I was looking for. One question… I continue to get the following error when pasting your script into the spreadsheet script editor.

    “Missing ) in parenthetical. (line 72)” Can you offer me any help with this? I’ve done my best to solve the syntax error but I’m beginner with google apps script.

    Thanks,

    1. Sure, feel free to share the workbook with me at: cjoseph@cmredis.com, and i’ll take a look at it. It may be helpful if you go ahead and share the calendar as well.

       1. Hi, thanks for the reply. I ended finding the problem in the missing parenthetical and I simplified the script so it just updates the calendar like i needed.

          thanks again for the help
30. Pingback: Google Calendar / Spreadsheet reciprocity script with Gscript | appsgoogleplus.com

31. Thanks a lot for this! I made an SMS alert system using Spreadsheet and Calendar.

32. Guys, sorry, i’m a complete newbie to Google Script. Can someone please help with getting this script work with the following three fields: “Name”, “Surname”, “Date”, which would result in an annual event entry in the calendar like this: “Name Surname Birthday” [at a corresponding date every year]?

33. Quality articles is the main to invite the viewers to go to see the web
    page, that’s what this site is providing.
34. Pingback: Tutorial: Using Google Spreadsheets to Update a Google Calendar – Our New Vacation Management System | Adam McFarland

Comments are closed.