There's a slightly different approach. Firstly the spreadsheet is embedded as view only. The spreadsheet is only used a visualisation of availability now - there's no direct manipulation of any data.
Because, almost without thinking, I made the published web app a HTML based one, it meant that I could easily add jQuery and interface niceties like the date choosing dropdown (shown above).
Because all the code runs as me, and I've already authorized the code, the end user isn't presented with any awful dialogs. I make adding the booking something that the end user does, by hand themselves. You can pre-populate a Google Calendar new event form by hacking the url variables easily. Like this...
calendar/event?action= Template&text=Toms+Next+ Birthday&dates=20130903/ 20130903&sprop=website:http:// york.ac.uk.libcal.com&details= Bring+CAKE!
Docs on how to do that here.
Well, it's good to know that I have something that we can happily share around the university securely.
The disappointing aspects are that...
Google Spreadsheets don't seem to allow you separate the concepts of a "data editor" and a "code editor" in any useful way. That means, if you allow someone to add data, they can change the way that data gets added. The only way to "share" collaborative data is to cludge an awkward and ugly interface in the front of it. This means that, AppsScript is best used within a spreadsheet for individual ( or selected ) users... not in a collaborative scenario.
Whilst "jumping ship" into a HTML based web app, rather that using widgets in a Spreadsheet does add more complexity but also brings more control. I may end up not using the spreadsheet viewing gadget and build a table of bookings by scratch, one that can take direct manipulation to book multiple hot-desks across multiple days. I had wanted to leverage many of the spreadsheet's abilities, I now have to think of the spreadsheet as backend-data storage. I also have to grapple with jQuery to build a table as complex as a the spreadsheet shown above (with some cells coloured in).
When working with the UI Builder, it isn't possible to have copy-and-pasteable UIs or share them amongst different spreadsheets. This makes for unmaintainable code, with each spreadsheet containing its own hand-crafted UI. Were UIs standalone objects, like AppsScripts are becoming, then they could at least be duplicated, or loaded and copied-n-pasted.
Google's whole "Publish Your Apps Script" concept is flawed. Who would be daft enough to click the dialog that says, give this unknown author complete access to all my docs and emails?
What All Of This Means
Over the last few months we've seen a lot of new developments from Google, many of them very welcome, but new developments are often very easy self-contained concepts... I would like to see a bit more depth and sophistication from Google in how the bits fit together, where the gaps are and where things could be smoother.
For example, the UI Builder components could include a date-picker surely? And what about type-ahead scrolling perhaps. And how about different classes for working with data, instead of SpreadsheetApp how about CodeAuthorSpreadsheetApp ( which can't read all the user's data )?
The feeling that I'm getting is that AppsScript ( and UI Builder widgets ) when used with spreadsheets is only useful for data that you own... and isn't appropriate for shared data... a web app front-end to working with shared data seems much more sensible ( but a fair bit more work ). It means that only the hot-desk admin team get to do the more complex things, but hey... it sort of makes sense.
Google really could do to take a look at HyperCard and make it work in AppsScript. It would solve a million problems ( yes, they're mostly all mine, but ... ).
My next challenge is to help develop a better way of storing students marks than the system that is currently used. This seems already to be breaking down into a spreadsheet with UI Builder widgets to do certain admin tasks and a web app for exam markers to record results.
I'll let you know how I get on and share the code/files from the Booking System when I've got it running smoothly.