Skip to main content

2. Building a Booking System With Google AppScript...

Given the swingeing criteria in my first post, I decided to start by creating the simplest interface I could. 

I began with a simple database of Perches in a spreadsheet and then in the Script Editor created a rough GUI with a couple of dropdown menus, and a couple of buttons that I would fill with data from the a mixture of a Perches calendar and this spreadsheet.

I decided not to keep track of bookings in a separate spreadsheet, simply because this felt like it would just be a whole heap of work. I would just use a calendar to store bookings. The guest of each event would decide who's booking it was. 

There are two areas of the interface, in the top bit, you can pick a date and book it ( it shows how many perches there are left ). In the bottom bit the dropdown menu is a list of dates you have booked and you can delete them. Like this...

The green blob at the bottom is just where I splat debug stuff. The list of perches is kept in spreadsheet called "Perches" and availability is worked out by simply counting the number of events on a particular day and taking that away from the number of perches. Rocket science.

I thought having LOADS of events in a calendar might look very crap but, it seems to cope.

And in Agenda View it worked even better.

And because the user is "added a guest", it magically appears in their calendar like this shown below, with an email reminder if I want. 

Note: Declining the invitation doesn't delete the original event from the calendar ( but, if done from the interface could ). 

How I Did It

Firstly, please forgive my crap Javascript ( and tell me how to do it clearer ) I created the UI in the UI Builder.... and created the doGet() code...

function doGet( e){
  var app = UiApp.createApplication();
  //the bottom text thing is something I use for debugging...
   var bottomPanel = app.createHorizontalPanel();
   var contentBox = app.createTextArea().setSize('580px', '20px').setId('contentArea').setName('contentArea');
   contentBox.setStyleAttribute("color", "red");
   bottomPanel.setStyleAttribute("background-color", "yellow");
   label = app.getElementById("Label1");
   var user = Session.getUser();
   label.setText( user.toString() )
   // Get MY EVENTS
   my_events = getMyEvents()//See later...
   for (e in my_events){
     event = my_events[e];
     var the_date = event.getStartTime();
     Logger.log( "the_date:" + the_date);
     var the_str = the_date.toDateString() + " " + event.getLocation();
     app.getElementById("ListBox2").addItem(the_str );
        // Load the Perches Spreadsheet.
      var ss = SpreadsheetApp.openById("***************"); 
      SpreadsheetApp.setActiveSpreadsheet(ss); // Make this the one "in focus" 
      SpreadsheetApp.setActiveSheet( ss.getSheetByName("Perches")   );
      var range = ss.getDataRange().getValues()//Get all the Perches
      var perches = rangeToObjects(range);
      var number_of_perches =  perches.length;
      //Load the Treehouse Perch Bookings calendar
      var number_of_days_ahead = 10;
      var cal = CalendarApp.getCalendarById('******************'); //Logger.log (cal.getName() );// Just check you got the right one
      cal.setSelected( true ); // Is this really needed?
      //Create a "list of days", with calendar events in each item
      var days = new Array()
      for (i=0;i<=number_of_days_ahead;i++){
        var perches_available = number_of_perches
        var this_days_events = new Array();
        var future_day_start = new Date(); //now...ish!
        var the_hours = future_day_start.setHours(9);
        var the_minutes = future_day_start.setMinutes(0);
        var the_seconds = future_day_start.setSeconds(0);
        var the_millis = future_day_start.setMilliseconds(0);
        var future_day_end = new Date(); //now...ish!
        var the_hours = future_day_end.setHours(17);
        var the_minutes = future_day_end.setMinutes(0);
        var the_seconds = future_day_end.setSeconds(0);
        var the_millis = future_day_end.setMilliseconds(999);

        future_day_start.setDate(future_day_start.getDate() + i ); //Logger.log( future_day )
        future_day_end.setDate(future_day_end.getDate() + i ); //Logger.log( future_day )
        this_days_events = cal.getEvents(future_day_start, future_day_end);
        var number_of_this_days_events = this_days_events.length;
        if (number_of_this_days_events > 0){
           var perches_available = number_of_perches - number_of_this_days_events;
        var the_date_string = "" + future_day_start.getDate() + "/" + future_day_start.getMonth() + "/" +  future_day_start.getFullYear();
        var better_date_string = future_day_start.toDateString();
        if (perches_available > 0){
            //dont' show the ones that are full
            var the_string = better_date_string  + " (" + perches_available + " available )"
            //Add the items to the dropdown menu

  var handler = app.createServerClickHandler('bookPerch');
  return app;

... and this called ...

function getMyEvents(){
  var cal = CalendarApp.getCalendarById('*****'); //Logger.log (cal.getName() );// Just check you got the right one
  var user = Session.getUser();
  var email = user.getEmail();
  var now = new Date(  );
  var future = new Date(  );
  future.setDate(date.getDate() + 14 ) // Look forward two weeks?
  var events = cal.getEvents(now, future, [ CalendarApp.GuestStatus.YES] )
  var new_events = new Array()
  for (i=0;i<=events.length;i++){
    var event = events.pop();
    var guests = event.getGuestList();
    if (guests.length >=1){
      var g = 0;
      var glen = guests.length;
      for (g in guests){
        var guest = guests[g];
          var guest_email = guest.getEmail();
          if (guest_email == email){
            new_events.push( event);
  return new_events

... and the method that is called when the button is clicked...

function bookPerch(e){
   var cal = CalendarApp.getCalendarById('****'); //Logger.log (cal.getName() );// Just check you got the right one
   cal.setSelected( true ); // Is this really needed?
   var app = UiApp.getActiveApplication();
   var user = Session.getUser();
   var email = user.getEmail();
   var name =    user.getUsername();
   var panel = app.getElementById( "TextArea1" );
   //var source = e.parameter.source // what's been clicked
   var value = e.parameter.ListBox1
   //strip off the "( 32 available ) //hack!
   var date_str = value.replace(/ \(.*/g,"");
   var date = new Date( date_str );
   date.setDate(date.getDate() + 1 ); //WTAF? Dates are a nightmare!

  // STILL TO DO: get ALL the perches available
  // remove the perches currently booked on this day
  // select a random one from the ones left
  perch = "Perch 24"
  /*optAdvancedArgs = new Array()
  optAdvancedArgs.guests = email
  optAdvancedArgs.location = perch
  optAdvancedArgs.sendInvites = true*/

  // Crapola! Doesn't work. Known issue
      Logger.log( "date day:" + date.getDate() );
      event = cal.createAllDayEvent( name, date );
      //event.addEmailReminder(60) ;
      event.addGuest( email ) ;
      panel.setText(name +  " " + date.toString() + " " + event.isAllDayEvent().toString() );
    if (event != null ){
      //tidy up? event.deleteEvent() ;
    panel.setText("ERROR: " + ": " +  e.message );
  //Update dropdown
   my_events = getMyEvents();
   var listbox = app.getElementById("ListBox2");
   for (e in my_events){
     event = my_events[e];
     var the_date = event.getStartTime();
     Logger.log( "the_date:" + the_date);
     var the_str = the_date.toDateString() + " " + event.getLocation();
    app.getElementById("ListBox2").addItem(the_str );
   } //*/
  return app; // do we need to refresh the dropdown menu here? How does this work?


So there we have it. Nowhere near finished but working well enough to prove that, given very simple restraints, something simple is feasible.

Known Bugs

Is it me or is working with All Day Events a bit buggy? They almost always end up on the wrong day. I'm doing something wrong.

The interface needs a "loading" animation or something when the button is clicked ( it takes about 4 seconds and nothing happens. People will just double book ). I've got a suspicion I don't need to send the rootComponent up and down onClick... I get the feeling I need to understand the mechanics of what is going on underneath the a mouseClick just to make it a bit snappier.

I found a "Known issue" which goes along the lines of "Google value your data integrity more than anything else in world, so were quite happy giving you spurious error messages that are essentially lies as long as your data isn't corrupted. Great. It happens when I try to add to many items at once to the calendar and a lock happens ( I think )... Google reports it's a "mismatch of keys".... 

Oh, and this is a handy error screen too.  Great. Thanks again Google.

And of course, the fact that the requirements I started with are all wrong in that the were necessarily restricted. This is definitely one of those problems that you are trying to match the tools to the solution. If the solution can be kept "simple enough" then it can be done quickly and perhaps evolved. 

Sometimes quirks, like only being able to see two weeks into the future... an accident of working around something ... could actually be recast as a feature, making the end user less likely to block book, making the availability of perches more equitable. Ahem. 


Popular posts from this blog

Writing a Simple QR Code Stock Control Spreadsheet

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

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

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

A Working Booking System In Google Sheets

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!)