How do I modify a google calendar event using google apps script?

Question

I figured out how to add an event to a calendar, but then spent a good 8 hours trying to figure out how to edit an existing event (or delete it, either way would get the job done). Here's what I've got:

function UpdateEventTime() {
  var cal = CalendarApp.getCalendarById("[email protected]");
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 5, numRows, 5);
  var data = dataRange.getValues();


//  var oldtstart = SpreadsheetApp.getActiveSheet().getRange('G2');
//  var oldtstop = SpreadsheetApp.getActiveSheet().getRange('H2');
// ??????????????????????????????????????????????????
// ??  How do I call up and delete the old event?  ??  
// ??????????????????????????????????????????????????  



  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, tstart, tstop, {description:desc,location:loc});
    SpreadsheetApp.getActiveSheet().getRange('G2').setValue(tstart);
    SpreadsheetApp.getActiveSheet().getRange('H2').setValue(tstop);
 }
}

From what I can tell in the online documentation, you can't pull up an event, you can only pull up all the events in a date range. So at the end of the code I try to store the start time and stop time in a spreadsheet, and then refer back to it next time the script is executed. The commented out section in the middle is where I'm lost. That's where I'm trying to call up the event that I added last time I ran the script. I need to either edit it, or delete it.

Please help.

Answer 1

It would be better if you manage to save the event Ids in spreadsheet along with those details. If you need to modify or delete those event, just fetch the event by id and do the things.

Modified code For saving

var event = cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
var eventid = event.getId();
SpreadsheetApp.getActiveSheet().getRange('I2').setValue(eventid);

To fetch the event back at later time

var id = SpreadsheetApp.getActiveSheet().getRange('I2');
var cal = CalendarApp.getCalendarById("[email protected]");
var event = cal.getEventSeriesById(id);
//Now modify or delete the event
event.addEmailReminder(minutesBefore);
event.addGuest(email);
event.deleteEvent();
.
.
.

Hope this will help you

Answer 2

Here's a work in progress I put together. 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;
}
}

Answer 3

EDIT : I was writing my answer while Waqar was posting his answer... so it's kind of a repetition but I'll just post it so you get an example...

You can access events using their IDs, here is an example that adds guests to calendar events. It also updates a log sheet that shows the results.

function sendinvites(e) {
  var ss = SpreadsheetApp.openById('0AnZ5_Sh________UJnVlFtNDM2NUE')
  var sh = ss.getSheets()[0]
  var logsheet = ss.getSheets()[1]
  var last = ss.getLastRow();
  var FUS1=new Date().toString().substr(25,8);
  var calendar_name = 'test'
  var group = GroupsManager.getGroup('groupemail');
  var members = group.getAllMembers();
  var startDate = new Date(e.parameter.start);
  var endDate = new Date(e.parameter.end);
  var Calendar = CalendarApp.getCalendarsByName(calendar_name);
  var sheetName = calendar_name + "-du-" + Utilities.formatDate(e.parameter.start, FUS1, "dd-MMM-yyyy")
      + "-au-" + Utilities.formatDate(e.parameter.end, FUS1, "dd-MMM-yyyy")  
// 
  var events = Calendar[0].getEvents(startDate , endDate);
  if (events[0]) {
    var eventarray = new Array();
    var line = new Array();
    line.push('Titre : '+calendar_name,'Début ','Fin','Localisation','Durée','invités');
    eventarray.push(line);

    for (i = 0; i < events.length; i++) {
        var ID = events[i].getId()
    var lr = logsheet.getLastRow();
      logsheet.getRange(lr+1,1).setValue(events[i].getTitle()+' / '+Utilities.formatDate(events[i].getStartTime(), FUS1, "dd-MMM-yyyy"));
      for(nn=0;nn<members.length;++nn){
        logsheet.getRange(lr+1,nn+2).setValue(members[nn]);  
        Calendar[0].getEventSeriesById(ID).addGuest(members[nn])
      }
      line = new Array();
      line.push(events[i].getTitle());
      line.push(Utilities.formatDate(events[i].getStartTime(), FUS1, "dd-MMM-yyyy")+' à ' +Utilities.formatDate(events[i].getStartTime(), FUS1, "HH:mm"));
      line.push(Utilities.formatDate(events[i].getEndTime(), FUS1, "dd-MMM-yyyy")+' à ' +Utilities.formatDate(events[i].getEndTime(), FUS1, "HH:mm"));
      line.push(events[i].getLocation());
      line.push((events[i].getEndTime() - events[i].getStartTime()) / 3600000);
      var invitelist='';
      var list = Calendar[0].getEventSeriesById(ID).getGuestList()
          for(nn=0;nn<list.length;++nn){invitelist+=list[nn].getName()+', '}
      line.push(invitelist)
      eventarray.push(line);
    }
    Logger.log(eventarray)
    var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
    sheet.getRange(1,1,eventarray.length,eventarray[0].length).setValues(eventarray);
    sheet.getRange(1,1,1,eventarray[0].length).setBackgroundColor('#ffffcc');
    sheet.setColumnWidth(1, 450);sheet.setColumnWidth(2, 150);sheet.setColumnWidth(3, 150);sheet.setColumnWidth(4, 250);sheet.setColumnWidth(5, 75);sheet.setColumnWidth(6, 450);;
    sheet.setFrozenRows(1)
  } else {
    var startstring = Utilities.formatDate(e.parameter.start, FUS1, "dd-MMM-yyyy");
    var endstring = Utilities.formatDate(e.parameter.end, FUS1, "dd-MMM-yyyy");
    Browser.msgBox('Aucun événement entre le ' + startstring + ' et le ' + endstring +' dans votre agenda :'+calendar_name);
  }
}

Answer 4

In case people are looking for this in the future, here's my final code after integrating Waqar's suggestions. It works. When it creates a new event, it grabs the eventID and stores it in the spreadsheet in cell I2. It "updates" existing events by finding the event based on that eventID, deleting it, and replacing it with a new one.

  function CreateOrReplaceEvent() {
  var cal = CalendarApp.getCalendarById("[email protected]");
  var id = SpreadsheetApp.getActiveSheet().getRange('I2').getValue();

  if(id != 0){
  var event = cal.getEventSeriesById(id);
  event.deleteEventSeries();
  }

  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 5, numRows, 5);
  var data = dataRange.getValues();

  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];
    var event = cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
    var eventid = event.getId();
    SpreadsheetApp.getActiveSheet().getRange('I2').setValue(eventid);
    }
}