I’m facing an issue using Google Calendar and Google Apps Script to manage meeting room reservations within my organization.

Project Context: We have developed a Google Apps Script to:

• Iterate through calendars associated with meeting rooms.
• Identify events scheduled for the following day.
• Send notifications via email and Slack to organizers, asking them to confirm or release the room.
• Handle both recurring and non-recurring events.

The Issue:
We are encountering a problem with recurring events. Specifically:

• When users attempt to confirm or release a room via the links provided in the email notifications, they occasionally receive an "Invalid Event ID" error.
• This seems to stem from how recurring event IDs are handled. In particular, the instance-specific IDs (which include timestamps) differ from the base recurring event ID, causing mismatches.

Steps We’ve Taken:
We’ve implemented logic to generate unique IDs for recurring event instances using a combination of the base event ID and the instance start time.

let instanceId;
if (event.isRecurringEvent() && !eventUid.includes("_R")) {
instanceId = `${eventUid}_${startTimeFormatted}@google`;
} else if (eventUid.includes("_R")) {
instanceId = `${eventUid.split("_R")[0]}_${startTimeFormatted}@google`;
}

While this approach works in most cases, there are edge cases where it fails, especially for recurring events that have been modified or instances that no longer exist.

Request for Assistance:
We would greatly appreciate your guidance on:

• The best practices for managing recurring events and their instance-specific IDs via Apps Script.

Any documentation or resources that might help us better understand how event IDs are generated and handled for recurring events.

Recommendations for avoiding or resolving the "Invalid Event ID" errors.

I’m facing an issue using Google Calendar and Google Apps Script to manage meeting room reservations within my organization.

Project Context: We have developed a Google Apps Script to:

• Iterate through calendars associated with meeting rooms.
• Identify events scheduled for the following day.
• Send notifications via email and Slack to organizers, asking them to confirm or release the room.
• Handle both recurring and non-recurring events.

The Issue:
We are encountering a problem with recurring events. Specifically:

• When users attempt to confirm or release a room via the links provided in the email notifications, they occasionally receive an "Invalid Event ID" error.
• This seems to stem from how recurring event IDs are handled. In particular, the instance-specific IDs (which include timestamps) differ from the base recurring event ID, causing mismatches.

Steps We’ve Taken:
We’ve implemented logic to generate unique IDs for recurring event instances using a combination of the base event ID and the instance start time.

let instanceId;
if (event.isRecurringEvent() && !eventUid.includes("_R")) {
instanceId = `${eventUid}_${startTimeFormatted}@google.com`;
} else if (eventUid.includes("_R")) {
instanceId = `${eventUid.split("_R")[0]}_${startTimeFormatted}@google.com`;
}

While this approach works in most cases, there are edge cases where it fails, especially for recurring events that have been modified or instances that no longer exist.

Request for Assistance:
We would greatly appreciate your guidance on:

• The best practices for managing recurring events and their instance-specific IDs via Apps Script.

Any documentation or resources that might help us better understand how event IDs are generated and handled for recurring events.

Recommendations for avoiding or resolving the "Invalid Event ID" errors.

• google-apps-script
• slack
• meeting-request

• Please share all your script. Provide a minimal reproducible example. – leylou Commented Jan 20 at 17:24
• 3 Welcome to Stack Overflow. In the post history, I saw that this post was created using the wizard. Unfortunately, the post is verbose, but it still doesn't include enough details. As requested in the previous comment, please add a minimal reproducible example and try to focus on a specific problem. Please remember that Stack Overflow questions should be about a particular problem unique to programming. – Wicket Commented Jan 20 at 17:28
• Hello mates, thanks for your answer! you'll find my answer below! – Asaad Larhlimi Commented Jan 21 at 14:13

1 Answer 1

The issue is with recurring meetings. When users try to manage their recurring meetings, they sometimes see an "error: invalid event ID" because the event ID has been modified to include "_R". This change was made to avoid a previous problem where releasing one reservation would cancel all instances of the recurring meeting.

here's a part of my script :

    function eventLister() {
  const isTestMode = false; // Switch to 'false' for production
  const testOrganizers = ['XX'];
  const roomInfoSheetId = 'XXX'; 
  const roomInfoSheet SpreadsheetApp.openById(roomInfoSheetId).getActiveSheet();
  const roomData = roomInfoSheet.getDataRange().getValues();

  const spreadsheetId = 'XXX'; 
  const sheet = SpreadsheetApp.openById(spreadsheetId).getActiveSheet();
  sheet.clearContents(); 
  sheet.appendRow(['roomName', 'eventIDModified','eventID', 'organizerEmail', 'startTime', 'event title', 'confirmationMailSent', 'confirmedStatus', 'slackMessageTs']);

  for (let i = 1; i < roomData.length; i++) {
    const roomName = roomData[i][0];
    const calendarId = roomData[i][1];
    const date = new Date();
    const dayOfWeek = date.getUTCDay();

    if (dayOfWeek >= 1 && dayOfWeek <= 4) {
      date.setUTCDate(date.getUTCDate() + 1);
    } else if (dayOfWeek === 5) {
      date.setUTCDate(date.getUTCDate() + 3);
    }

    let events;
    try {
      events = CalendarApp.getCalendarById(calendarId).getEventsForDay(date);
    } catch (error) {
      Logger.log(`Error accessing calendar ${calendarId}: ${error.message}`);
      continue;
    }

    const expiration = new Date();
    expiration.setUTCDate(expiration.getUTCDate() + 1);
    expiration.setUTCHours(0, 1, 0, 0);
    const expirationFormatted = Utilities.formatDate(expiration, "UTC", "MMMM dd, yyyy HH:mm:ss Z");

    for (const event of events) {
      const organizerEmail = event.getCreators()[0];
      if (!isTestMode || testOrganizers.includes(organizerEmail)) {
        const eventUid = event.getId().split('@')[0];
        const startTimeFormatted = Utilities.formatDate(event.getStartTime(), 'UTC', "yyyyMMdd'T'HHmmss'Z'");
        
    let instanceId;
      if (event.isRecurringEvent() && !eventUid.includes('_R')) {
    instanceId = `${eventUid}_${startTimeFormatted}@google.com`;
     } else if (eventUid.includes('_R')) {
    instanceId = `${eventUid.split('_R')[0]}_${startTimeFormatted}@google.com`;
     } else {
    instanceId = `${eventUid}@google.com`;
      }

        const data = [
          roomName,
          instanceId,
          eventUid,
          organizerEmail,
          event.getStartTime(),
          event.getTitle(),
          'no',
          'no',
          '' // slackMessageTs
        ];
        sheet.appendRow(data);
        sendConfirmationEmail(event, organizerEmail, roomName, spreadsheetId, calendarId, expirationFormatted);
        sendSlackNotification(event, organizerEmail, event.getTitle(), roomName, expirationFormatted, spreadsheetId, calendarId);
      }
    }
  }
}

function sendConfirmationEmail(event, organizerEmail, roomName, spreadsheetId, calendarId, expirationFormatted) {

  if (!organizerEmail.endsWith('@XXX')) {
    Logger.log(`L'organisateur ${organizerEmail} n'est pas dans le domaine @XXX. Email non envoyé.`);
    return; 
  }

  if (organizerEmail.endsWith("[email protected]") || organizerEmail.endsWith("[email protected]")) {
    Logger.log(`Organizer ${organizerEmail} has left the company. E-mail notification ignored.`);
    return;
  }

  const calendar = CalendarApp.getCalendarById(calendarId);
  const subject = `Please confirm your room booking for meeting: ${event.getTitle()}`;
  const eventUid = event.getId().split('@')[0];
  const startTime = event.getStartTime();
  const startTimeFormatted = Utilities.formatDate(startTime, 'UTC', "yyyyMMdd'T'HHmmss'Z'");
  const calendarTimezone = calendar.getTimeZone();
  const niceDate = Utilities.formatDate(startTime, calendarTimezone, "EEEE, d MMM yyyy' at 'HH:mm");

  let instanceId;
  if (event.isRecurringEvent() && !eventUid.includes('_R')) {
    instanceId = `${eventUid}_${startTimeFormatted}@google.com`;
  } else if (eventUid.includes('_R')) {
    instanceId = `${eventUid.split('_R')[0]}_${startTimeFormatted}@google.com`;
  } else {
    instanceId = `${eventUid}@google.com`;
  }

  const paramsToEncryptConfirm = `calendarId=${calendarId}&eventId=${instanceId}&confirmed=yes&spreadsheetId=${spreadsheetId}&expiration=${expirationFormatted}&organizerEmail=${organizerEmail}`;
  const encryptedParamsConfirm = cipher.encrypt(paramsToEncryptConfirm);

  const paramsToEncryptDeny = `calendarId=${calendarId}&eventId=${instanceId}&confirmed=no&spreadsheetId=${spreadsheetId}&expiration=${expirationFormatted}&organizerEmail=${organizerEmail}`;
  const encryptedParamsDeny = cipher.encrypt(paramsToEncryptDeny);
Etc..... same e-mail function for slack notification ....