When I first started at my new job, we had recently moved most of our employees to two floors in a new building. However, given that the majority of my colleagues were at the new location, we often hosted the ones from our smaller satelite office for meetings.
Needless to say, meeting rooms were scarse; oftentimes, you couldn't get a room for a meeting more than one week out. Office services tried to mitigate the damage via various policies (e.g., having to sign into a meeting within 10 minutes of it starting), but the problem continued.
We were granted a temporary reprieve when we opened a new floor with ~8 new rooms, but before long, we moved more of the other office in, moving us back to square one.
While observing what was taking up so much of our meeting rooms, I realized that a ton of them were booked for standing weekly meetings or one-on-ones, which were often cancelled a few hours ahead of time. Thus, you could often get rooms just by checking back relatively frequently, and Room Finder was born.
(Brief note: we use Google Calendar to book meeting rooms, without which the following will not work, though I'd bet the approach still stands)
Production of Room Finder was actually fairly quick (~8 hrs), given how well integrated Google Apps Script is with the rest of the Google infrastructure. That allowed me to run the script using Google Auth, allowing me to skip the messy noise of tokens and API authorization.
The script is relatively simple: if I have a meeting with "TBD" in the location, it will scan each meeting room during that time period, grabbing any better room that is available. As "better" is purely subjective, I instituted a 1-9 ranking system (lower is better, and it's mostly ranked by proximity to my desk). Google allows timed scripts, so I set it to run every minute.
This solution worked relatively well, but started erroring out, as I was invoking the Google Calendar API too many times per day, so I had to find out why.
I investigated the limits, thinking Google may have just changed them, but after implementing a counter, I realized I was calling the service ~800 times every time my scripts ran, meaning I was blowing past the 30k limit within the first hour every day.
Looking closer, my issue was easily addressable:
- Firstly, I was loading all events for the following week from each individual room calendar, then iterating through them to find the time of each event. I changed Room Finder to only look at relevant time periods (e.g., when I needed a room), and to exit before pulling calendars if I didn't need any rooms in the following week.
- Secondly, I had added a few checks (e.g., making sure the script didn't grab too small of a room), which ran in a suboptimal order. I switched these to run the "Better" check first ("don't look at switching to a worse room"), which eliminated quite a few calls about number of attendees of meetings.
After fixing those issues (which required a bunch of refactoring), I haven't run into the issue again.
There also has been some incidental maintainance/upkeep of the list itself, as new rooms have been added (yay for Floor #3), and a few have been taken away (converted to offices or unbookable interview rooms).
As for overall performance, I'd say this works ~98% of the time (only one meeting has failed to find a room outright). I've had a handful of awkward altercations whereupon I've accidentally snagged a room that people are trying to transfer (pleading for a room seems to be the method most people use), but giving those up quickly (and waiting for the next free room) have resolved that.
Lastly, an unexpected benefit is that I can now make meetings on my iPhone, and allow the script to grab the room in the background. I'm sure the official Google Calendar has these capabilities, but iCal (Apple Calendar?) cannot allocate a room on mobile, so this will grab one, typically on next run a minute later.
- Actually pretty happy with this implementation. I may add some features to filter rooms by feature (e.g., screen), but that's much less of a concern for now.