Collaborative Tools Project

I have just met with a colleague who is using Google Forms to improve the Ethics Approval process for research projects. They are taking a slightly different path to the one I might have plumped for, and are creating, on-the-fly Google Documents, filling in the appropriate sections and then sending them to people to add comments to etc. We thought he may have worked himself into a corner because although there is a function to capture when people submit a form ( onFormSubmit() ), there isn't one to capture if you "allow the user to edit responses" - there's no onFormUpdate(). We created a workaround by having a time-based trigger that just checks to see if the updated_timestamp is greater than the last_checked_timestamp. The best part, for me, of working with my colleague was when I referred at some point to my JavaScript skills being far from honed and he asked "So is this JavaScript then?" and laughed incredulously. He was happily coding away without...

You might not know but Google are dropping the Appointment Slots feature from Google Calendar. The academic community has been pretty miffed about this decision because it's one of the features they've really taken to their hearts and were actively using. Appointment Slots are a great way of making any number of tutorial slots available for tutorials and letting students pick which times suit them. Google responded to the academic outcry with a list of alternatives . This list included paid for services with no clear pricing model for enterprise, downloadable open source software and, rather desperately, a few web2.0 tools that weren't even relevant. Few of these tools integrated with Google Calendars or Contacts, none would have worked with our log ins, and of course, none would be embedded in your Google Calendar making it easy to create overlapping or clashing appointments. So, I wondered how useful a tool I could create using Apps Script. Of course I couldn...

I'm currently working with the Chemistry dept. to help use Google Docs for recording students lab experiment marks. It sounds simple enough until you find there are over 170 students in a year and there are at least 20 tests to be done, and the students get broken down into groups and rotated, and different people need to log different bits of information (that the student attended, their mark, that their mark has been agreed etc ). That's well over 2000 marks a year.... which in the scale of numbers isn't the biggest, I know, but that's not where the dilemma is. Part of the problem is the different ways different people need to access creating the students marks but I'll come to that another day. The dilemma is that, I have the need for a really simple to use interface for entering students' marks. I could either use the UI Builder built into AppsScript OR I could create an HTML application in AppsScript. With an HTML application, it's a breeze to a...

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

It's always a pleasure when Arthur the online security guy at York drops by for a cup of tea. Today he pointed out, kind of him to bother really, that.... When you run an AppsScript in a Google Spreadsheet, it is run by the ActiveUser i.e the person that is logged in and working with the spreadsheet. In order to run the AppsScript, which edits the spreadsheet, you need Edit permission on that spreadsheet. Stay with me. Because you've got Edit permission on the spreadsheet, the container for the AppsScript, you've also got Edit permission on the AppsScript. That means, that you ( the ActiveUser ) can edit the script to say... get a copy of all my Documents ( assignments etc ) and upload them to a homework cheating site over here... and do it from your actual email address. It could send rude messages from you, the ActiveUser. AAAAAARGHHHHH! It's a massive security hole. You could lock down the spreadsheet so that users can't edit the cells, a...

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

Recently I've been working on trying to create a Booking System using Google Spreadsheets, Apps Script and Google Calendars.  And today, a request for a system stunningly similar came in, except this time, instead of being for booking hot desks, the bookable items are tape measures, cameras, radars, computers and exotic mystery items such as a "FM36 B". Lots of them. The thing that strikes me about the similarity of the needs, is that already with the booking system, the relationship has more of the developer/client than I'd like. You could say I'm failing in managing expectations slightly. I'll get it back on track. Not that there's any problem, but there is this... At what point is something software and no longer just a cool spreadsheet? One of the really great things about spreadsheets is that they're almost agnostic about what you put in them. As soon as you start hanging interfaces off them, then the notion that perhaps you shouldn't b...

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