Skip to main content

Building a Booking System with Google Apps ( THE MOAN! )


In a previous post I shared my initial attempts to create a Booking System with Google Apps. The approach was simple enough... I created a very simple form ( using Google Apps UI Builder ) and I would store peoples' booking information straight into a calendar, with some other bits and bobs being stored in a spreadsheet ( like the list of bookable things ).

The end result of my last attempt was passable, but not something I was particularly proud of in any way. So, my next step was to try and make it a bit more robust and maybe try some other approaches.

So. I feel the need to get this post out of the way first. It's a massive moan and will only clutter up the post that follows this one. The moans below are either because of Google Apps peculiarities or because of my stupidity. I'm the last person to be able to tell which is which.

Google Apps MOAN, MOAN, MOAN

Creation of Events in Google Calendar Is A Pain

When creating an event in a Google Calendar using AppsScript, the executing code isn't stopped ( until the event exists). This seems to be a known error/feature and is there because Google want to ensure your data is OK. Well thank you very much Google. Maybe that's why they also return the non-sensical error message "Service error: Calendar : Mismatch: etags = ". Very handy. Thanks.

The upshot of this feature is that when you create an event, with a date and title, you can't then alter anything about that event. You can't add a description, or add guests or anything.... UNTIL a non-specified amount of time has passed. I found 10 seconds worked ( Utilities.sleep( 10000) ) sometimes, but not reliably. It also breaks if you try and create an event with all the parameters in one go.

What this means, is that you can't have an application that creates events that is tied to a User Interface, because people aren't that patient. I even tried adding code that kept trying to create an event in a while loop.

    while( keepTryingToCreateSoddingEvent(title, date, email, notes)){

Not fun. And it didn't work.

All Day Events Are A Pain

I've distant memories of this being a pain in other languages. When creating All Day Events, you have to pass in a DateTime object... which the observant will notice, have a TIME bit. I found myself having to do this...


var event = cal.createAllDayEvent(title, date)

...just to be sure. And it still failed. Interestingly, I ended with a checkerboard effect calendar where "every other day" failed to be created. I'm guessing this is down to a BST issue with trying to create an All Day Event from 1AM to the next day at 1AM...  Who knows.... I couldn't get it working. I found if I made events 09:00AM to 17:00PM they worked more often.

Permissions of Access Issues

I'm guessing this is going to bite me in the arse anytime soon. I have created a solution ( read mess) that is a spreadsheet and a calendar with some AppsScript lurking. I'm not entirely sure what I have to do to make all of this usable by somebody else in terms of permissions. 

I have tried to use the permission calls to lock down the editing of certain sheets, but this isn't what I expect the issue will be. I can't wait.

Missing onOpen

This is an odd one. I think, if you add code not in a function in an AppsScript, that it may fiddle with onOpen getting called. So the first time I "tested" this spreadsheet with someone that wasn't me, the menu I'd created didn't load. Great.

Speed of the UI

The speed of the user interface ( UI ) form both in terms of loading and user interaction responsiveness isn't massively impressive. I thought I'd try a trick of closing the UI directing after the user clicks the submit button, to give the illusion of speed, but unless I bang in a Browser.msgBox dialog box, the experience is like wading through treacle in a long leather skirt with diving boots on.

Loading of Shared Resources

I'm not convinced that loading a Calendar, or Spreadsheet on EVERY function call is fast, but when I tried to only load them onOpen, connections started getting broken. 

Lack of Bog Standard Functions/Library for working with Spreadsheet data

This is a minor gripe, but the suggested ways of working with spreadsheets, in terms of the ObjService library really doesn't match my way of thinking or my spreadsheet structure come to that. The idea that you read spreadsheet data into an array, work on that array, then write it back in one big lump just doesn't fit my data.

I event tried leveraging Google Spreadsheet functions to do some heavy lifting, but both QUERY() and FILTER() don't seem to do what I need. This may be down to my lack of understanding. I wish I knew.

So. There we have it. A BIG MOAN. Hopefully that's cleared the air enough to be able to write a useful blog post about creating a Booking System with Google Apps.

I have no idea if I've managed to cobble something together that is actually useable by the people that asked for it. I hope so. Testing needed...


Post a Comment

Popular posts from this blog

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems. A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it. The QR app looks like this. The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this. The Web Application The web application, which only checks items in or out and should be used on a phone in conjunctio

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

This article looks at using Apps Script to add new features to a Google Spreadsheet. At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form. Spreadsheets can be better than Forms for data collection because: The spreadsheet data saves as you are editing. If you want to fill in half the data and come back later, your data will still be there. The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessary The commenting features are brilliant - especially the "Resolve" button in comments. One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often

A Working Booking System In Google Sheets

Working with Andras Sztrokay we had another go at a booking system. This time it was to enable staff to book out a number of iPads over a number of days. You select the days you want, then select the Booking menu. Andras did an amazing job. It even creates a daily bookings sheet so you can see who has which iPads. To see this in action, go  here  and  File > Make a Copy (I won't be able to support you this is just provided to maybe give someone else a leg up, good luck!)