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