Solving Canceled Meeting Rooms With Apps Script

Today, I arrived at work a bit earlier than usual. After grabbing my morning coffee routinely, I opened up my Google Calendar to see what meetings I had for the day. Of course, our meeting room for our recurring daily stand-up got canceled again.

Whenever this happened, we ended up manually rebooking a different meeting room — not particularly time-consuming, but it was a bit annoying to do it every day. We'd be forced to book another room ad-hoc, or worse, sometimes ended up without a room.

But today, I decided enough was enough. I couldn’t stop thinking about it on the way home. Once I got back, I pulled out my laptop and got to work.

Solution? Automate this.

After some digging online, I confirmed that I wasn't alone in my struggle.

At this point I was no stranger to working with the Google Workspace API and messing around with Google Apps Script. So naturally my first thought was to use Apps Script.

On a high level, here's how I envisioned the workflow to be:

Considerations

Of course, there were some things I had to keep in mind:

• Don’t book another room if there’s already one reserved for the meeting
• Don’t hog meeting rooms on days we don’t need them (like work-from-home days)
• Only book the room on the actual day of the meeting

Implementation

1. Finding the daily stand-up meeting

The first step was to find the stand-up meeting (event) for the current day:

const STANDUP_EVENT_NAME = "Daily Standup";

/**
 * Finds the standup event for the current day.
 */
function findStandupEvent() {
    const calendarId = "primary";
    const today = new Date();
    const startOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate());
    const endOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 1);

    const events = Calendar.Events.list(calendarId, {
        timeMin: startOfDay.toISOString(),
        timeMax: endOfDay.toISOString(),
        singleEvents: true,
        orderBy: "startTime",
    });

    const standupEvent = events.items.find((event) => {
        return event.summary === STANDUP_EVENT_NAME;
    });

    if (standupEvent) {
        const startTime = new Date(standupEvent.start.dateTime).toLocaleTimeString();
        console.log(`Found standup event "${standupEvent.summary}" which starts at ${startTime}.`);
        return standupEvent;
    }

    console.log("No standup event found for today.");
    return null;
}

Here, I utilized the Calendar.Events.list API (reference) to retrieve events from the primary calendar, filtering for events with the title "Daily Standup".

By setting timeMin and timeMax, I was able to narrow down the search to events occurring within the current day.

Once I had the list of events, I simply used the find method to locate the stand-up event based on its summary (i.e. title of the meeting). If the stand-up was found, we’d just return the event object for later use.

2. Get all meeting rooms in the office building

After locating the stand-up event, the next step was to retrieve a list of meeting rooms:

/**
 * Retrieves a list of available meeting rooms (excluding phone booths and cockpits).
 */
function getAllRooms() {
    const rooms = AdminDirectory.Resources.Calendars.list("my_customer", {
        maxResults: 100,
        query: "buildingId=SG-1N AND floorName=3 AND resourceCategory=CONFERENCE_ROOM",
        orderBy: "capacity asc",
    });

    const availableRooms = rooms.items.filter((room) => {
        const resourceName = room.resourceName.toUpperCase();
        if (resourceName.includes("BOOTH") || resourceName.includes("COCKPIT")) {
            console.log(`Skipping ${room.resourceName} (phone booth or cockpit)`);
            return false;
        }

        if (room.capacity < 6 || room.capacity > 8) {
            console.log(`Skipping ${room.resourceName} (inadequate capacity)`);
            return false;
        }
        console.log(`Including ${room.resourceName}`);
        return true;
    });

    console.log(`Found ${availableRooms.length} available meeting rooms.`);
    return availableRooms;
}

Through some quick Googling (with site:stackoverflow.com) on how room resources work with Google Calendar, I discovered the AdminDirectory.Resources.Calendars.list API (reference). For this to work, I had to first enable the AdminDirectory API in my Apps Script.

This API allows fetching calendars for meeting rooms based on specific criteria (e.g. building, floor, and category):

const rooms = AdminDirectory.Resources.Calendars.list("my_customer", { maxResults: 100, query: "buildingId=SG-1N AND floorName=3 AND resourceCategory=CONFERENCE_ROOM"});
console.log(rooms.items[0])

// Output:
//
// { capacity: 1,
//   etags: '"-roQ5YNyqtVnJTuIfcddtIsUY9W3r0o3wv8vGq722Ls/hrna2LzZYMJYI0OOszX_2X4iKIw"',
//   generatedResourceName: 'SG-1N-3-M09 SOLO BOOTH (1)',
//   buildingId: 'SG-1N',
//   resourceId: '10744518274',
//   kind: 'admin#directory#resources#calendars#CalendarResource',
//   resourceEmail: '[email protected]',
//   featureInstances: [ { feature: [Object] } ],
//   resourceName: 'M09 SOLO BOOTH',
//   floorName: '3',
//   resourceCategory: 'CONFERENCE_ROOM' }

The getAllRooms function filters out rooms that don't meet my criteria, such as phone booths, cockpits, and rooms with inadequate capacity for my use case.

3. Checking Room Availability

With the list of potential meeting rooms in hand, the next step was to filter out those that were unavailable during the stand-up event's scheduled time:

/**
 * Checks the availability of a room during a given time range.
 */
function isRoomAvailable(roomGeneratedResourceName, roomEmail, startTime, endTime) {
    const freebusy = Calendar.Freebusy.query({
        timeMin: startTime.toISOString(),
        timeMax: endTime.toISOString(),
        items: [{ id: roomEmail }],
    });

    const busyTimes = freebusy.calendars[roomEmail].busy;
    const isAvailable = busyTimes.length === 0;

    console.log(`${roomGeneratedResourceName} is ${isAvailable ? "available" : "not available"} during the specified time range.`);
    return isAvailable;
}

To achieve this, I use the Calendar.Freebusy.query API (reference), which allows checking if a room is free or busy during a specific time range.

The response provides an array of busy time slots for the specified room(s), for example:

const rooms = Calendar.Freebusy.query({
  timeMin: startOfDay, 
  timeMax: endOfDay,
  items:[{"id": "[email protected]"}],
})

console.log(rooms.calendars["[email protected]"].busy) 

// Output:
//
// [ { end: '2024-07-18T04:00:00Z', start: '2024-07-18T03:00:00Z' },
// { end: '2024-07-18T07:00:00Z', start: '2024-07-18T06:00:00Z' },
// { end: '2024-07-18T10:00:00Z', start: '2024-07-18T09:00:00Z' } ]

So, if the array is empty, it means the room is available during the given time range. Otherwise, it's considered unavailable (busy).

Using this, I could filter out the meeting rooms that were already booked during the stand-up event's scheduled time, leaving me with a list of available options.

4. Book the meeting room

Once I was able to find the stand-up event, retrieve all meeting rooms in the office building, and check their availability during the desired time range, the last step was to actually book a meeting room:

/**
 * Books an available room for the standup event.
 */
function bookMeetingRoom(event) {
    console.log(`Attempting to book a room for the standup event "${event.summary}"...`);

    // Check if a room is already booked
    const existingRoomAttendee = event.attendees?.find((attendee) => attendee.resource);
    if (existingRoomAttendee) {
        console.warn(`A room "${existingRoomAttendee.displayName}" is already booked for this event.`);
        return;
    }

    const availableRooms = getAllRooms();
    const startTime = event.start.dateTime;
    const endTime = event.end.dateTime;

    const availableRoomsForEvent = availableRooms.filter((room) => {
        return isRoomAvailable(room.generatedResourceName, room.resourceEmail, new Date(startTime), new Date(endTime));
    });

    if (availableRoomsForEvent.length === 0) {
        console.warn(`No available rooms found for the standup event "${event.summary}".`);
        return;
    }

    // OR: randomly select a room instead
    // const randomIndex = Math.floor(Math.random() * availableRoomsForEvent.length);
    // const selectedRoom = availableRoomsForEvent[randomIndex];

    const selectedRoom = availableRoomsForEvent[0]; // FIXME: come up with a preferred room algorithm

    console.log(`Selected "${selectedRoom.generatedResourceName}" for the standup event.`);

    const attendees = event.attendees || [];
    attendees.push({
        resource: true,
        responseStatus: "accepted",
        displayName: selectedRoom.generatedResourceName,
        email: selectedRoom.resourceEmail,
    });

    const updatedEvent = {
        attendees,
        ...event,
    };

    Calendar.Events.update(updatedEvent, "primary", event.id); // NOTE: comment out this line if you do not want to actually book the room during testing
    console.log(`Booked "${selectedRoom.generatedResourceName}" for the standup event "${event.summary}".`);
}

The bookMeetingRoom function first checks if a room is already booked for the event. Again, we don’t want to accidentally double-book a room!

Here, I retrieve the list of available rooms and filter them based on their availability during the stand-up event's time range. If there are no available rooms, then oh well.

Finally, I simply call the Calendar.Events.update API (reference) to update the event with the new attendee list, effectively booking the selected room for the stand-up event by including the meeting room as an attendee.

Putting everything together

The final step was to integrate everything into a single file (e.g. Code.gs) with an entry point:

/**
 * Entry point function to be triggered for booking a room for the standup event.
 * This function orchestrates the process of finding the standup event,
 * checking if a room is already booked, and booking a new available room if needed.
 */
function bookStandupRoom() {
    const today = new Date();
    const dayOfTheWeek = today.getDay();
    const isWorkFromHomeDays = [0, 1, 2, 6].includes(dayOfTheWeek);

    if (isWorkFromHomeDays) {
        console.log(`Skipping job as today is WFH day.`);
        return;
    }

    const standupEvent = findStandupEvent();
    if (standupEvent) {
        bookMeetingRoom(standupEvent);
    }
}

This function first checks if the current day is a work-from-home day. If so, it skips the entire booking process. Otherwise, it proceeds to book a meeting room for the daily stand-up that day.

Simple!

Run this daily

With everything in place, the final step was to set up a time-driven trigger daily at 8 AM. The trigger would call the bookStandupRoom function, automating the entire process and ensuring that an available meeting room is booked for the stand-up event each day.

Here's what it would look like every day from the execution log:

The Result

Honestly, it felt pretty cool to see the meeting room getting booked for real every day as I walked by before the meeting. I know it sounds kinda weird, but it was one of those awesome moments that brought back the joy of coding and fixing my own problems.