Skip to main content

3. Building a Booking System ( the afterthought )

By



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.

Currently:


  • 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.






















Labels:

Comments

  1. Anonymous31 May 2012 at 13:20

    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!

    ReplyDelete
  2. Tom Smith31 May 2012 at 14:22

    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...

    ReplyDelete
  3. Tom Smith31 May 2012 at 16:57

    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...

    ReplyDelete
  4. Martin Hawksey31 May 2012 at 17:37

    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) https://developers.google.com/apps-script/guide_events#Simple

    Looking forward to seeing what else you come up with,
    Martin

    ReplyDelete
  5. Tom Smith31 May 2012 at 20:00

    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...

    ReplyDelete

Post a Comment

Popular posts from this blog

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

By
This article looks at using Apps Script to add new features to a Google Spreadsheet. At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form. Spreadsheets can be better than Forms for data collection because: The spreadsheet data saves as you are editing. If you want to fill in half the data and come back later, your data will still be there. The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessary The commenting features are brilliant - especially the "Resolve" button in comments. One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often
111 comments
Read more

Writing a Simple QR Code Stock Control Spreadsheet

By
At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems. A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it. The QR app looks like this. The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this. The Web Application The web application, which only checks items in or out and should be used on a phone in conjunctio
59 comments
Read more

A Working Booking System In Google Sheets

By
Working with Andras Sztrokay we had another go at a booking system. This time it was to enable staff to book out a number of iPads over a number of days. You select the days you want, then select the Booking menu. Andras did an amazing job. It even creates a daily bookings sheet so you can see who has which iPads. To see this in action, go  here  and  File > Make a Copy (I won't be able to support you this is just provided to maybe give someone else a leg up, good luck!)
Post a Comment
Read more