Collaborative Tools Project

BETTER VERSION HERE NOW I've had a number of requests for code examples for my Booking Spreadsheet. I have held off sharing the code mainly because it became very complex and wasn't very useful as a starting point for anyone else. But recently, I had to whip an Appointments spreadsheet together that didn't have personalised colouring of cells, that didn't create lots of sheets for a whole term, or permissions etc.  This spreadsheet is just a list of "Book me" links that passes some data through to a simple web form, and then saves the person's email into that cell's value. If you want to do something similar using this spreadsheet,  a. File > Make a Copy - to get your copy b. Tools > Script Editor > File > Manage Versions > Save New Version c. Publish > Deploy as web app - to copy your web app URL d. Change all the variables in the code, there's only a few e. Run the Setup code - to regenerate the ...

BETTER VERSION HERE You may remember my previous posts about attempts to create a room booking system with Apps Script. This system uses Apps Script to populate a spreadsheet with weekly sheets, that contain lots of "Book me" links ( see below ). The "Book me" links open a very small web application that is essentially a confirmation screen with a "Book" button. When a student books a room, the web application says "Booked" and adds the booking to a central calendar and invites the student as guest, so that it appears in their calendar. One very important aspect of this booking system was the booking quotas that student are given. Each student can only book 3 hours a day in each room. The student is allocated a colour, not just because it looks nice, but because, as you can imagine when a large amount of students are wanting to finish their projects with a finite resource, it can get quite busy. The admin team previously has be...

After discovering that my direction of work for the Booking System was from a security perspective , deeply flawed, I thought that I could perhaps work around giving people access to the code by embedding a web application within a Google site. I thought this would be a big structural change, but it only took a few minutes. It looks like this. 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 Goog...

The original idea was to use a calendar for hot desk ( or perches as they're called ) bookings, that students could add their bookings to. The idea was to use a spreadsheet, to essentially show which hot desks ( or perches ) which were already booked. The idea was for the script to add an event to the booking calendar, and add the student to the event as a guest. All of these lovely ideas would mean that there was one central calendar that admin people could check, that people could add their own bookings and also receive something in their calendar so they wouldn't to forget to show up. Except, none of this works... ... or rather, because I was cornered into creating a Booking Task Queue sheet because adding events didn't work reliably I therefore ran the event adding code from a Trigger ( once a minute ) rather than as  it happens. This of course means that the script runs as ME ... the script author and not THE STUDENT ... who is using the booking system. ...

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