Thursday, 31 May 2012

3. Building a Booking System ( the afterthought )

After trying some different approaches like this to the Booking System problem ( see earlier posts ) I had an afterthought.

The Problem is Not The Problem

Someone suggested that "Resource Calendars" are something we/I should look at. Well, as far as I can see, Resource Calendars don't really help see when one of seventy items is free. Whilst the problem looks like a calendar problem, keeping a track of bookings, it's actually the opposite of a bookings system, it's a "What's free?" system.

Seventy Checkboxes? Really?

So I started wondering what seventy checkboxes in a spreadsheet row would look like. I would have to make graphics that showed selected or not and detect which image had been clicked by adding a macro to it. It began to look like this...

... You know how simple ideas suddenly ramp up to complex...  After discovering that ( I don't think ) you can't detect which cell just got clicked, I realised that I'd have to custom write each red checkbox into each cell.

I then tried creating a UI with the UI builder that had 70 checkboxes (one for each perch ). I imagined that you could have a row of these for each day. That might looks like this.

Thu 31 May 

Fri 1 Jun

Whilst seventy IS a big number, it ( I don't think ) isn't the biggest number there is. Maybe having a form with seventy items in it might not be a bad thing. Except there is no way to get nice checkboxes into a Google Spreadsheet.

Another Solution?

Initially, I thought that "doing it all in a spreadsheet" was a bad idea, recreating a booking system, but after a bit of playing I created this...

It's just a spreadsheet. You make a booking by selecting a cell... and choosing "Make a Booking" from the menu at the top. The script then adds it to the "Perch Bookings" calendar, sends email invitations ( so that it appears in the booker's calendar ) and colours the cell to red (also adding their email) to indicate that that perch is booked.


  • it doesn't update itself if someone was to just add bookings to the "Perch Bookings" Calendar, it could. 
  • it does allow "block bookings" by just setting the background colour of the cells
  • it does let people choose which perch they want
  • it could, when a cell is selected, show more info about that perch (e.g floor, phone, map etc)
  • it doesn't prevent overwriting bookings ( but probably could ).
  • it would require "setting up" in terms of defining the cells in terms of the dates down the side. This might be useful and a spreadsheet created for each term etc.
  • There are issues with, when creating events - they can take a while to "stick" and so we need work out a way to make sure that the event is actually in the calendar before manipulating further.

Anyone at York can view ( and I think edit ) the Perch Booking spreadsheet here... and make a booking ( hopefully ).

So far, I think this is my favourite solution ( whipped up this morning ) because it does the most, by doing the least, making fewest assumptions. I'll add the code, such that it is, soon.


  1. Tom

    Awesome post series - really cool to see how you have explored the problem. Just wanted to add a few suggestions myself.

    Have you looked at Appointment Slots in Google Calendar? I have considered those for room bookings (or anti-bookings...) - could be relevant?

    Also, I made something in my spare time which uses AppScript to book something, generate a UUID, and send an email with a cancel link - don't know if that's something that would be of interest.

    Finally, have you thought about having a system which merely lists the number of free perches (I think there's a similar system for washing machines on campus!), and users just 'swoop by' to use one, if one is still free. Then you get away from the 'booking and not using' problem.

    If the desks all have PCs, onto which you assume users will log, you could (could you? I don't know. Let's assume so) have some script which runs at logon for each machine and calls some web service, to log that the perch is in use, and again when they log out.

    Anyway, good luck with the quest!

  2. Thanks... I don't think Appointment slots help - they're for a conceptually different thing (segments of time ) as opposed to items of a resource ( desks ). I'd be happy to be proved wrong on that though...

    Also, there's 70ish of them...

  3. Chris, are you using Google AppsScript much? Or want to...

    I'd like to get a group together to share basically stuff we've done, ideally in the form of shareable libraries. Paul Bushnell is already doing some great stuff and may have a great "mail merge" library to share....

    I'm just learning, but I'm trying to share what I learn as I go...

  4. Hi Tom,

    Like the comment from Chris it's great to see you work through this problem. I don't know if you've considered using the onEdit trigger to handle changes (might help in case users forget to go to the menu option)

    Looking forward to seeing what else you come up with,

  5. Cheers, I like the look of that onEdit tinker. I could imagine maybe popping up a mini Gui when you click into a "bookable" cell...