Update attendees when editing a Google calendar event with Google apps script

Question

Situation

I have a calendar with a lot of events on it (staff appraisals).

I've made a load of modifications (changing the length of the event etc.) but the invitations are going to people who have a lotus notes calendar (poor people).

This means that unless I trigger what would be called "Send notification?" in the click-with-your-mouse version of things, they have no way of knowing that the event has been updated.

(Similar Q)

In this example, the event I'm trying to trigger is the same one as is triggered when the Send Update? modal is accepted with send.

Code

Here's some example code that gets all the events on the Appraisals calendar and changes their location to 'the moon'.

function fixInvitations(){
  //get the callendar named "Appraisals"
  var cApp = CalendarApp.getCalendarsByName("Appraisals")[0];
  var events = cApp.getEvents(new Date(), new Date("Dec 30 2014"));

  for (eIndex in events){
    var event = events[eIndex];
    event.setLocation("the moon");
  }
}

Question

How do I trigger an update to all parties invited to an event so that the changes are reflected in their calendars?

Currently these events are now on the moon, but the update hasn't told people who are on non-Google calendars about the change.

Helpful, but not that helpful fact

The update email that manually triggering sends contains a .ics file (Gist of the contents). This contains a VCALENDAR and a VEVENT. From the Wikipedia page on VEVENTs

For sending an UPDATE for an event the UID should match the original UID. the other component property to be set is:

SEQUENCE:<Num of Update>

I.e., for the first update:

SEQUENCE:1

So if there was a way to manually build an email with a .ics attachment it would solve the problem, but that feels like massive overkill. This is mentioned here but not resolved.

Answer 1

I was struggling with this one a bit and got some help from ekoleda+devrel@google.com: Ability for Calendar to send email invitation to users added via addGuest http://code.google.com/p/google-apps-script-issues/issues/detail?id=574

A couple of pointers to hopefully save people some time:

Note that CalendarApp and the Advanced Calendar service use different event ID formats:

• CalendarApp returns event IDs with "@google.com" at the end ex. b3gv...a5jrs@google.com
• The Advanced Calendar service/Calendar API expects an event ID that does not have @google.com ex. b3gv...a5jrs

Here's some working code:

function sendInvite(calendarId, eventId, email) {
  var event = Calendar.Events.get(calendarId, eventId);
  if(event.attendees) {
    event.attendees.push({
      email: email
    });
  } else {
    event.attendees = new Array({email: email});
  }
  event = Calendar.Events.patch(event, calendarId, eventId, {
    sendNotifications: true
  });
}

One final note for those following along - you won't receive an email notification if you're the event owner and you're trying to add your own email address. This makes sense in the calendar world since you wouldn't go into your own calendar event, add yourself as an attendee, and expect to be prompted as to whether or not you want to inform yourself. However, it's probably a common way for people to try and test things (I don't have a domain test account and I can't always reliably test things with my personal gmail account). So if you're testing with your own account and thinking that things aren't working please keep this in mind. Once you try things with a non-event owner account the invite will be sent via email.

Answer 2

I got an email from Gooogle today saying that they've added a feature to app script that makes this possible.

Updates: Status: Fixed Owner: ekoleda+devrel@google.com Labels: -Priority-Medium

Comment #11 on issue 574 by ekoleda+devrel@google.com: Ability for Calendar to send email invitation to users added via addGuest http://code.google.com/p/google-apps-script-issues/issues/detail?id=574

This is now possible using the Advanced Calendar service:

https://developers.google.com/apps-script/advanced/calendar

You can use Calendar.Events.patch() to add attendees, and if you set the optional parameter sendNotifications to "true" the attendees will get an invite.

https://developers.google.com/google-apps/calendar/v3/reference/events/patch

I'm going to try to solve this problem today and then edit this response to reflect that.

Answer 3

The Google Calendar API supports the flag sendNotifications for event updates: https://developers.google.com/google-apps/calendar/v3/reference/events/update

I would file a feature request for App Script to expose the flag and in the meantime use the Calendar API directly for updating events just like an external API: https://developers.google.com/apps-script/guides/services/external

Answer 4

I think one solution is to create a spreadsheet with the column headers: Event ID, Event Name, Description, Start Time, End Time, Location,Guest List, and then add this script to the spreadsheet. calendarImport() will import all of your calendar events and write them to the sheet. Then, you can modify the event in the spreadsheet line (change location to the Moon), and run calendarCreateEvent to create a new event based on the changes you made. It will automatically send out notifications to all old attendees to accept the new event, as it is a new invitation.

I am pretty close to that solution, but I have a hangup, now, on accessing the calendar to create a new event. I think it's because I'm calling it from an onEdit event function. It's getting late, but if you rewrite the third part of the script to get the events data range, then a for/if loop to check the added versus modified date, then you should be able to create a new event, and delete the old event. This would push a notification of the change in event details to everyone who had accepted the old event already.

I think, given the code example you put up in the beginning, you probably already know how to do that, but if you don't, I could probably throw something together tomorrow night to search for old events that have been recreated and delete them.

Here's my spreadsheet and code in action.

function calendarImport(){
  //http://www.google.com/google-d-s/scripts/class_calendar.html#getEvents
  // The code below will retrieve events between 2 dates for the user's default calendar and
  // display the events the current spreadsheet
  var cal = CalendarApp.getDefaultCalendar();
  var calId = cal.getId();
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetName = SpreadsheetApp.getActiveSheet().setName(calId +" Calendar Data");
  var events = cal.getEvents(new Date("March 9, 2014"), new Date("March 14, 2014"));
  for (var i=0;i<events.length;i++) {
    //http://www.google.com/google-d-s/scripts/class_calendarevent.html
    Logger.log(events);
    var details=[[events[i].getId(),events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime(),events[i].getLocation()]];
    var guestList = events[i].getGuestList();
    var guestArray = [];
    for (var n in guestList){
      var guestEmail = (guestList[n].getEmail());
      guestArray.push(guestEmail);
      Logger.log(guestArray);

    }

    var row=i+1;
    var range=sheet.getRange(row+1,1,1,6);
    range.setValues(details);
    var guestRange = sheet.getRange(row+1,7,1,1);
    guestRange.setValues([guestArray]);
    var dateAdded = Utilities.formatDate(new Date(), "GMT-6","MM/dd/yy HH:mm:ss");
    var dateAddedRange = sheet.getRange(row+1,8,1,1);
    dateAddedRange.setValue(dateAdded);
  }
}

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var calId = CalendarApp.getDefaultCalendar().getId();
  var sh = ss.getSheetByName(sheetName);
  var actSht = event.source.getActiveSheet();
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  Logger.log(index);
  var dateCol = actSht.getLastColumn();
  var calId
  var lastCell = actSht.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), "GMT-6", "MM/dd/yyyy HH:mm:ss");
  lastCell.setValue(date);
  var modifiedRow = sh.getRange(index,1,1,ss.getLastColumn()).getValues();
  Logger.log(modifiedRow[0][7])
  if (modifiedRow[0][7] < modifiedRow[0][8]){
    var firstAdded = modifiedRow[0][7];
    var dateModified = modifiedRow[0][8];
    calendarCreateEvent(index,firstAdded,dateModified,calId);
  }


}

function calendarCreateEvent(index,firstAdded,dateModified,calId){
  var sheet = SpreadsheetApp.getActiveSheet();
  var added = firstAdded;
  var modified = dateModified;
  var startRow = index;  // First row of data to process
  var calId = calId;
  Logger.log(calId);
  Logger.log(startRow);
  Logger.log(added);
  Logger.log(modified);
  if (modified - added > "0"){
    var numRows = 1;   // Number of rows to process
    var dataRange = sheet.getRange(startRow, 1, numRows, 9);
    var data = dataRange.getValues();
    var cal = CalendarApp.getCalendarById(calId);
    for (i in data) {
      var row = data[i];
      var eventId = row[0]
      var title = row[1];  // First column
      var desc = row[2];       // Second column
      var tstart = row[3];
      var tstop = row[4];
      var loc = row[5];
      var guests = row[6];
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc, guests:guests});//.addGuest(guests);
      var newEventId = newEvent.getId();
      Logger.log(newEventId);

    }
  }
}