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();

  app.add(app.loadComponent("MyGui"));

  //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");

   contentBox.setText('OK');

   bottomPanel.add(contentBox);

   bottomPanel.setStyleAttribute("background-color", "yellow");

   app.add(bottomPanel);

 

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

   

   }

 

    try{

        // 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;

        }else{

          //

        }

       

        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

            app.getElementById("ListBox1").addItem(the_string);

        }

      }

     

    }

 

    catch(e){

      Logger.log(e);

      contentBox.setText(e)

    }

 

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

  handler.addCallbackElement(app.getElementById("ListBox1"));

  app.getElementById("Button1").addClickHandler(handler);

 

  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?
   cal.setTimeZone("Europe/London");
 
   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 http://code.google.com/p/google-apps-script-issues/issues/detail?id=1055
   
    try{
      Logger.log( "date day:" + date.getDate() );
     
      event = cal.createAllDayEvent( name, date );
      //event.addEmailReminder(60) ;
      event.addGuest( email ) ;
      event.setLocation(perch);
 
      panel.setText(name +  " " + date.toString() + " " + event.isAllDayEvent().toString() );
    }
  catch(e){
    if (event != null ){
      //tidy up? event.deleteEvent() ;
      Logger.log(e.message);
    }
    panel.setText("ERROR: " + ": " +  e.message );
  }
 
  //Update dropdown
 
   my_events = getMyEvents();
 
   var listbox = app.getElementById("ListBox2");
   listbox.clear();
   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.