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

BETTER VERSION HERE

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)){
      Utilities.sleep(3000)
     }

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...

date.setHours(0,0,0,0)

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...