Override existing google calendar event when new e

问题:

I am using a Google Form to schedule appointments based on open events on my Google Calendar. I've sectioned off time slots on the calendar and if the title of the event is "Open", then the time slot can be used to create an event. I would next like to know if it's possible to override and delete the existing open event with the new appointment that is created.

Here is the code I am using to push events to my calendar as they are created in the form:

var calendarID = "";
var startDtID = 5;
var endDtID = 5; // Column containing date/time
var nameID = 2; // Column containing name
var emailID = 3; // Column containing email
var phoneID = 4; // Column containing phone
var formTimeStampID = 1; // column containing timestamp

function getLatestAndSubmitToCalendar() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();

  var startDt = sheet.getRange(lr,startDtID,1,1).getValue();
  var endDt = sheet.getRange(lr,endDtID,1,1).getValue(); // remove hour and minute for the start/end time
  var desc = "Comments :"+sheet.getRange(lr,phoneID,1,1).getValue(); // set comments as description, add in timestamp and submission
  var title = sheet.getRange(lr,nameID,1,1).getValue()+" - "+sheet.getRange(lr,emailID,1,1).getValue(); // create title using name and email

    createEvent(calendarID,title,startDt,endDt,desc);
}; // run create event function

function createEvent(calendarId,title,startDt,endDt,desc) {
  var cal = CalendarApp.getCalendarById(calendarID);
  var start = new Date(startDt);
  var end = new Date(endDt);

  var event = cal.createEvent(title, start, end, {
      description : desc
  }); // set options for event
};

The CalendarEvent class has a method to delete events through deleteEvent(), but I am confused as to how I can determine the right event to be deleted. How can I match up the date and time of the current event with that of the one that already exists so I can override it?

回答1:

You'll need to call calendar.getEvents() first, then loop through the event(s) you want to delete, and call .delete() on each of them. Here's the API link: https://developers.google.com/apps-script/reference/calendar/calendar#getEvents%28Date,Date,Object%29

Something like this:

var now = new Date();
var twoHoursFromNow = new Date(now.getTime() + (2 * 60 * 60 * 1000));
var events = CalendarApp.getDefaultCalendar().getEvents(now, twoHoursFromNow,
    {search: 'meeting'});

for(int i = 0; i < events.length; i++){
    if this is the right event (compare title, times, etc?){
        events[i].deleteEvent();
    }
}

In this case, you'll probably want to compare the descriptions. Also note that you can pass options to the getEvents call; one you can use to filter during the search is the search parameter like I did above. It filters the returned events on that text.

EDIT: Ideally, you'll want to use this: https://developers.google.com/apps-script/reference/calendar/calendar#getEventSeriesById%28String%29

That gets the event by the ID; I answered with the assumption you didn't have the ID stored anywhere though. If you do, all the better, and you'll be certain you're replacing the right event.

回答2:

It works! Here is my completed code for adding an event and deleting one that occurs at the same time.

function createEvent(calendarId,title,startDt,endDt,desc) {
  var cal = CalendarApp.getCalendarById(calendarID);
  var start = new Date(startDt);
  var end = new Date(endDt);

  var event = cal.createEvent(title, start, end, {
    description : desc
  });

    var events = cal.getEvents(start, end, {
    search: 'open'
  });  

  for (i in events){
    events[i].deleteEvent();
  }
};