Skip to main content


Showing posts from May, 2012

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)
for(i =2; i < result; i++){

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 …

3. Building a Booking System ( the afterthought )

After trying some different approaches like this to the Booking System problem ( see earlierposts ) 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 wr…

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 bracesI loathe semi-colonsThe 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…

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 avai…

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…

Getting CSV data into Google Spreadsheets Automatically

Today I was attempting to get CSV data from Estates' Alarm System into Google Docs as a spreadsheet. There were two ways to try and achieve this...

Create an AppScript in Google that pulled a .CSV file from a web serverWrite a (python) script on the local machine that pushed the data into Google Spreadsheet by using the API. The Google AppScript Way As you know, my JavaScript ain't great, but it initially looked like it was going to work... Some code like this below and using the Array to CSV functions from here, looked promising.

function encode_utf8( s ){
//This is the code that "I think" turns the UTF16 LE into standard stuff....
return unescape( encodeURIComponent( s ));

function get_csv(){
var url =' BA Alarms.csv';// Change this to the URL of your file
var response = UrlFetchApp.fetch(url);
// If there's an error in the response code, maybe tell someone