Save in a google sheet the modifications of the events of my google calendar

Question

in a google apps script, I have created a trigger that is triggered when an event in the calendar is modified. With the following script, I manage to write in the google sheet the modification date but I would like to have the event id as well.

function recordModification() {
  var calendarID = 'xxx@gmail.com'; 

  var calendar = CalendarApp.getCalendarById(calendarID);
  var lastModifiedEventId="???";
  var sheet = SpreadsheetApp.getActiveSheet();
  var now=new Date();
  sheet.appendRow([now, lastModifiedEventId]);    
}

I found variable triggerUid in https://developers.google.com/apps-script/guides/triggers/events#eventupdated but I don't know how to use it.

Answer 1

I believe your goal as follows.

• You want to retrieve the event ID of latest updated event in Google Calendar.
• Your function recordModification can be executed by the trigger when the event is updated.
• When the existing event is modified, you want to put the date object and the event ID to the Spreadsheet.

In this case, I would like to propose the following flow.

1. Retrieve the event list of all events from the calendar.
2. Sort the event list with the updated time.
3. Check whether the event is modified.
4. Put the date object and the event ID to the Spreadsheet.

Sample script:

In this script, Calendar API is used. So, before you use this script, please enable Calendar API at Advances Google Services.

function recordModification() {
  var calendarID = 'xxx@gmail.com';
  var now = new Date();
  
  // 1. Retrieve the event list of all events from the calendar.
  var eventList = [];
  var pageToken = "";
  do {
    var events = Calendar.Events.list(calendarID, { maxResults: 2500, pageToken: pageToken });
    if (events.items.length > 0) eventList = eventList.concat(events.items);
    pageToken = events.nextPageToken;
  } while (pageToken);
  if (eventList.length == 0) return;
  
  // 2. Sort the event list with the updated time.
  eventList.sort((a, b) => new Date(a.updated).getTime() < new Date(b.updated).getTime() ? 1 : -1);
  
  // 3. Check whether the event is modified.
  var updated = new Date(eventList[0].updated);
  var created = new Date(eventList[0].created);
  if (updated.toString() == created.toString() || updated.getTime() < now.getTime() - (3 * 60 * 1000)) {

    // In this case, the event is not updated. The event is created.
    // When you want to process for this situation, please put the script here.
    console.log("Event is not updated. Event was created and/or others.")

  } else {
    
    // 4. Put the date object and the event ID to the Spreadsheet.
    var lastModifiedEventId = eventList[0].id + "@google.com";
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.appendRow([now, lastModifiedEventId]);
  }
}

• In this script, from your question, when the existing event is modified, the values are put to the Spreadsheet. Even when the event is created, the values are not put to Spreadsheet. If you want to also run this when the event is created, please modify the script as follows.

    function recordModification() {
      var calendarID = 'xxx@gmail.com';
      var now = new Date();
  
      // 1. Retrieve the event list of all events from the calendar.
      var eventList = [];
      var pageToken = "";
      do {
        var events = Calendar.Events.list(calendarID, { maxResults: 2500, pageToken: pageToken });
        if (events.items.length > 0) eventList = eventList.concat(events.items);
        pageToken = events.nextPageToken;
      } while (pageToken);
      if (eventList.length == 0) return;
  
      // 2. Sort the event list with the updated date.
      eventList.sort((a, b) => new Date(a.updated).getTime() < new Date(b.updated).getTime() ? 1 : -1);
  
      // 3. Put the date object and the event ID to the Spreadsheet.
      var lastModifiedEventId = eventList[0].id + "@google.com";
      var sheet = SpreadsheetApp.getActiveSheet();
      sheet.appendRow([now, lastModifiedEventId]);
    }
  

Note:

• In this script, as the event ID, ###@google.com is used. If you are required to use only ### of ###@google.com, please modify above script.
• In this script, in order to check whether the event is modified, the created time and updated time are compared. For this, when the function is run, the event created within 3 minutes is retrieved. About this, when you want to modify this, please modify (3 * 60 * 1000) of above script.
• When the trigger doesn't work, please install it again.

Reference:

• Events: list