4. Building a Booking System with Google Apps ( Code )

Note: This is the 4th of 3 previous posts about hacking Google Apps to attempt to create a usable Booking System.

First run this code from the Script Editor. It will make you a "Calendar Sheet" with X number of items as columns and Y dates as rows.

function create_a_blank_calendar_sheet(){
  // Run this from the Script Editor to create a Calendar Sheet.
   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.insertSheet("Calendar"); //this'll fail if there is one already...
 
  var result = Browser.inputBox("How many x items", "e.g 10 or 25 etc", Browser.Buttons.OK_CANCEL );
  if (result == "cancel"){
    //Browser.msgBox("CANCEL: " + result)
        }
   else{
     //headers?
     sheet.insertColumnsAfter(1 ,result);
     for(i = 2; i < result; i++){
       ss.setColumnWidth(i, 18);
     }
   
     var days_result = Browser.inputBox("How many days", "e.g 365", Browser.Buttons.OK_CANCEL );
     // dates down the sides
     var n = 1;
     for(i = 2; i < days_result; i++){
        var now = new Date();
        now.setDate(now.getDate() + n);

        day = now.getDay();
       sheet.getRange(i,1).setValue(now); // You can format the column without times yourself :-)
     
       //colour the weekend's background
       if (day ==6 | day == 0){
        sheet.getRange(i, 1, 1, result).setBackground("#d6d6d6");
       }
        n++;
     }
   }
 
}

 
Now some code to add an "Administration" menu that people can use to click on a cell and book a "something or other".

function onOpen() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheet = ss.getSheets()[0];
 
  //{name: "Create A Calendar Sheet", functionName: "create_a_blank_calendar_sheet"}, // This is just for setting up.
  var menuEntries = [
                    {name: "Book this perch...", functionName: "book_perch"}, ]
  ss.addMenu("Administration", menuEntries);        
                   
}

This is a bit of code where you name your column names. You might want to do this by hand, with "Apples, Oranges, Pears, Kumquats" etc.

function setup_headers(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Calendar")    ;  //Yours might be something else
 
  /*setup column names. Probably wise to stick to single words? I'm using p17, p18 etc.


  var number_of_columns = 70
  for(i = 2; i < number_of_columns; i++){
       sheet.getRange(1,i).setValue("p"+i);
   }*/

}

Now these two functions do the work of creating an Event in an actual calendar and marking the spreadsheet to say it has been booked.

function add_to_calendar(perch, str_date){
    var user = Session.getUser();
    var email = user.getEmail();
 
    var cal = CalendarApp.getCalendarById('york.ac.uk_5e8b7i5**************google.com');
    Logger.log (cal.getName() );// Just check you got the right one
   
    date = new Date( str_date );
    //weirdo hack date help! help! All day events always end up a day behind?
    date.setDate(date.getDate() + 1);
     
    var event = cal.createAllDayEvent( email, date );
    Utilities.sleep(6000); // This is so that Google Calendar can "catch up". Seriously.
                   
    var msg = "Perch " + perch + " booked on " + date + " for " + email + "." ; // So is this...
    Browser.msgBox(msg);
    Utilities.sleep(6000); //And this...
    try{
      event.addEmailReminder(60) ;
      event.addGuest( email ) ; // This adds it to their calendar as a request.
      event.setLocation(perch);
    }
    catch(e){
       Logger.log(e.message);
    }

  return 0 // All is OK
}
   
function book_perch(){
    // This is called from the Menu
   var user = Session.getUser();
   var email = user.getEmail();
                   
  var ss = SpreadsheetApp.getActiveSheet();
                   
  // work out which cell is selected. Probably need to work out IF it a "live cell"
  // or if it is booked already, if it's one of their bookings.
   
  var cell = ss.getActiveCell();
  var cellname = cell.getA1Notation();                    
  var range = ss.getActiveRange();
  var row = range.getRow();                    
  var colindex = range.getColumnIndex();
                   
  // get the header name, e.g "p34"                  
  var perch_name = ss.getSheetValues(1, colindex, 1, 1)
  // get the row date value                  
  var date = ss.getSheetValues(row, 1, 1, 1)
                   
  var error = add_to_calendar(perch_name, date);  
  if (error == 1){
     Logging.log("Something went wrong");
  }else{
      // Only fill in the cell if the Calendar hasn't failed.              
      cell.setValue(email) ;
      cell.setFontColor( "#ffffff" );
      cell.setBackground( "#990000" );
   }
}

And there it is. It'd be true to say, there's more that it doesn't do than it does. For example, if I delete an item created in the calendar ( for real ) the spreadsheet doesn't automatically make the slot available again, but this is doable with a Triggered check on each item.

It's an encouraging start though. My only nagging doubts about how to deal best with the delay between creating an event and being able to then manipulate it, because the code continues executing but the documentation says it can be minutes before your event appears in the Calendar. At the moment I'm just lobbing in a few delays() ... but that's not right is it?

By: Unknown On 8:54 AM

Continue Reading

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.

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.

By: Unknown On 4:56 AM

Continue Reading

From Python to AppScript

In previous posts, I've been exploring what you can do with Google Apps, the APIs and AppScript.

The joy of the APIs is that I can talk to them with my favourite ( textual ) programming language python which in real terms normally means noodling around in the interpreter - eventually saving a page worth of code into a script.

I'm comfortable with python, but if I'm to share what I'm doing as a "you can too" thing then I need to knuckle down, give in, and finally learn the basics of Javascript, or rather AppScript. I've always resisted Javascript, because:

• I really hate braces
• I loathe semi-colons
• The string handling seems goofy.toString()
• itOftenUsesThisTypeOfNotation( )
• There's never an interpreter lying around when you need one

And the forloops are hideous!  Something that is so naturally fluid in python, seems like line noise to someone like me. It seems crazy that forloops, something you do all the time, are so verbose... I mean...  a simple Javascript forloop has 65 characters and python's only 50. That's probably like 300% difference...

Javascript forloop

for(i = 0; i < 5; i++){
    document.write("Counter i = " + i);
}

Python forloop

for i in range(0,5):
    print "Counter i = " + str(i)

Anyway...

Having actually spent a few minutes ACTUALLY TRYING to learn Javascript, I'm starting to quite like it, although with prejudices embedded this deep, it's going to be hard work.

I think I may have an idea what a prototype is... but I'm not sure I will ever get the applying of a function to something clear in my head... sure it looks useful, but most of the stuff I want to do is DEAD SIMPLE...

So. Given that I'm not a programmer, and I want to collaborate with people who probably aren't programmers either, on finding really simple solutions,  I'm diving in and seeing how far I get across before I sink.

By: Unknown On 4:10 AM

Continue Reading

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. 

By: Unknown On 1:53 AM

Continue Reading

1. Building a Booking System With Google AppScript

Photo credit: Paul Kelly

We had an interesting enquiry for a mini "booking system" come in this week. The need is for a simple online way for research students ( 200ish of them ) to book hot desks (between 30 and 100 ) in the Berrick Saul Treehouse. They call them "perches".

Having looked at our ENTERPRISE room booking system, they were wondering if there was anything simpler that could be done with Google Apps perhaps. Maybe it could be done with a Calendar?

The Illusion of the Simple Problem

If someone puts it to you, like that, it sounds perfectly feasible doesn't it? How hard could it be? During my spare time this week it was suggested I take a look and see whether it would be possible... not to actually do it, just to see if what looked like a simple problem really was as simple as it sounded.

The person who requested this was on leave this week, so I've not been able to ask questions to get to the bottom of what they really want. So I've had to have a think about it one afternoon whilst juggling a massive collection of assumptions ( all of which will probably prove to be wrong ).

Getting Started

Anyway, so my initial thoughts were that there were three ways of doing this:

1. Google App Engine: Too complex. Who wants to write another booking system from scratch? I haven't got the time or the ability.
2. Google Spreadsheet(s) with an AppScript UI. Difficult to visualise/manage for non-technical staff, for example, if someone wanted to add a collection of "perch bookings" by hand
3. Google Calendar with an AppScript UI. It would seem sensible to use a calendar for dates ( isn't it? ). Maybe we'd be able to connect with students' calendars so that their bookings would appear for them and they'd also get reminders etc. I'm not sure what having 70ish bookings on one day would look like though... it might be a mess too far.

And so, with a pad and paper I doodle some questions, or gotchas, or worries. It wasn't long before it was full... it included:

• How do people currently book a perch?
• How are perches identified? 
• Have you EVER been too full? - What happens then?
• How many perches is a student likely to want to book? Every day for a term, twice etc?
• Would you expect people to be able to book a range of dates?
• Would you expect people to be able to choose their perch?
• What is the duration of a booking? By the hour? By the day?
• Is there a personal limit on the number of bookings you can make? Per day? Per term?
• Is there a "fairness" policy, randomness or just first come, first served arrangement for bookings?
• Are there penalties for unused bookings?
• Can students see who has booked other perches?
• How far ahead can people book a perch? Next week, next month, a term, forever?

Given that they're called "perches", I think the best method should be flapping about until one becomes available. Based on what would be easiest  simplest, I worked on the assumptions that:

• Perch booking are for an entire day
• You are assigned perches at random ( you don't get to choose which perch )
• There aren't any limits on how many you can book
• You can't book ranges ( however admin staff may be able to do this to you )
• I would leverage the CalendarEvent as much as possible. 
• The user interface ( UI ) will need to take a back seat to it "just kinda working". 

But also, I wanted to not get too far with a solution. Partly because of all the unanswered questions and assumptions at the beginning but also because this exercise, if successful might be actually created by someone else who I might work with for an hour or so, included in which I'd teach them everything I know about Google AppScript... 

Here goes...

By: Unknown On 9:13 AM

Continue Reading