Making Your Own Version of Appointment Slots with Apps Script

Making Your Own Version of Appointment Slots

In a previous post I showed an alternative version of Google Calendar's Appointment Slot's functionality created in Apps Script. You can see it in action here.

Since making this app Google have, rather fantastically dropped their dropping of the Appointment Slots feature. This app may be useful to anyone wondering how to make an Apps Script Web App that loads jQuery and uses HTML templates.

If you want to edit the code that runs it, you can get a copy of the script here:

https://script.google.com/d/1Helzgu286Rh9QuHh4dbUq9SloMpb3USwygXSyXP9JcL6erToXpoVK_o5/edit

Then you need to go to...

File > Make a copy

... and then...

File > Manage Versions

... and create a new version of the app. This is needed when you want to publish your web app. So next, then go to ...

Publish > Deploy as web app

You might want to restrict Who has access to the the app setting to just people from your domain.

In theory you can now share your web app's URL and start making changes to the application. Most of the files are just HTML templates, but the main two code files are Code and Web App. Good luck!

By: Unknown On 5:29 AM

Continue Reading

An Alternative To Google Calendar's Appointment Slots

You might not know but Google are dropping the Appointment Slots feature from Google Calendar. The academic community has been pretty miffed about this decision because it's one of the features they've really taken to their hearts and were actively using. Appointment Slots are a great way of making any number of tutorial slots available for tutorials and letting students pick which times suit them.

Google responded to the academic outcry with a list of alternatives. This list included paid for services with no clear pricing model for enterprise, downloadable open source software and, rather desperately, a few web2.0 tools that weren't even relevant. Few of these tools integrated with Google Calendars or Contacts, none would have worked with our log ins, and of course, none would be embedded in your Google Calendar making it easy to create overlapping or clashing appointments.

So, I wondered how useful a tool I could create using Apps Script. Of course I couldn't embed it Google Calendar but it could work with our York single sign-on, and add details to Google Calendar. The Apps Script environment is limited in many ways - making it difficult to create an elegant solution, but its integration abilities might make it "good enough" for booking tutorial slots.

The Demo

But before you get too excited. There are a few caveats about this version of Appointment Slots.  

The first caveat is that you shouldn't expect bug fixes. This is a proof-of-concept / stop gap project. 

The first screen you and your users will see are these.

These two hideous screens are there to warn you ( and your students ) that I have access to your Calendar ( to create and delete events ), to send email and other things. There's nothing I can do about these screens. In a better designed world, Google would design Apps Script so that I, as coder, maybe only had access to a sandboxed area, or calendars I made myself that you then subscribe to - which would prevent me doing anything nasty or stupid. 

Another caveat is that I can't embed this app in your Calendar. It's a separate application.

The last caveat is that it all sort of works backwards. What I mean by this is that when you, for example, create a Tutorials Appointment Slots, the times are blocked out in your calendar. When a student books one of the tutorial slots, I have to create the booking event in the student's calendar and invite you to it.  

This is a side-effect of Google's permissions model. Because a student can't directly add items to your calendar, they have to add items to their own. You can see what it looks like in your calendar below.

It's also a bit slow. 

Using The Demo

There are really only two screens. The Home screen, shown below, lets you create an Appointment Slot in any calendars you own. 

When you have created your Appointment Slots, you are shown a screen similar to the one below that has a "Share this URL" link in it, which you can send to students. You also receive an email with these details in so that you can create a number of Appointment Slots on different days and collect them into one email that you send to students.

The student would then see a screen like this one. They can click the "Book this slot" and an event is added to their Calendar - and an invite sent to the lecturer who made the Appointment Slots ( see what I mean about backwards? ).  

A student can release an appointment booking by clicking the release link. 

All the data for this application is stored in what's called a ScriptDB. I did look at updating the ScriptDB when a student deletes a booking from their Calendar. That may be a version 2.0 feature, if I have any more time for development.

Top Tip: Create a Separate Calendar Just for Appointments Before You Start

If you create a calendar just for Appointments, and then create your appointments in that calendar then there is a way for students to browse lots of different options. If you have made your calendar public you can then let people browse your calendar (go to January 10th 2013 ) like this. Students could then use the link in the event description ( yes, I know, I know ) to be able to book onto that particular day.

If you are happy with all of the above then to see the demo go here.

p.s Thanks to +Martin Hawksey for his help along the way.

By: Unknown On 8:30 AM

Continue Reading

Building A Booking System With Google Apps

In my previous post, Building a Booking System With Google Apps, I tried to use Google's UI Builder to be a front-end to saving events into a Bookings Calendar for students to book hot desks in the Berrick Saul Treehouse. I wasn't totally happy with the results... still.

Lately, I have tried a completely different, simpler approach ( with quite a few groans about icky Google Docs issues along the way ) which has a sort of spreadsheet visualisation of the bookings that have been made. 


It looks and works like this.

Rather than taking apart the code in fragments, the entire spreadsheet is available here. Go take a look, from the File menu choose Make A Copy. 

If you create a Calendar and change the Calendar ID in the Script Editor you might be able to get it working for you. There are setup scripts to generate a "calendar-like" spreadsheet. The perches sheet has a list of columns that you might want to alter to suit your needs. The weirdest part is the "Booking Queue" sheet, which is essentially a huge workaround. 

Do let me know if you work out how to do any of this in a cleaner/better/faster way. Thanks.

By: Unknown On 7:31 AM

Continue Reading

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

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