Collaborative Tools Project

It's often the case that you want and need to be creating a database to store your data, but Google Spreadsheets are just so handy aren't they? But Google Spreadsheets are very good at relational data. Here's an example where, you want to have one column for the name of your recipe and another for the ingredients ( comma separated ). How you use this script is you click on the cell you want to be relational and choose the Admin > Show Relationship Editor. This opens up a dialog window showing you all the options included so far. You then alter the ingredients and it saves a comma separated list into the spreadsheet. Here's the spreadsheet . Use File > Make a copy to see it work and rummage around in the code. If anyone can help make the UI prettier I'd be grateful, thanks.

We wanted to improve the way people at the University request new software and tools. This is a process that requires lots of people's feedback and needs to be very flexible. We need to get software experts to look at it, security teams, the support teams, teaching experts to see if is a good pedagogical match. We need the licensing to looked at and the usability and accessibility. The list is astonishingly long and in these cases it often gets so that your process map just starts to look like infinite spaghetti. No wonder it didn't quite work, infinite spaghetti is always troublesome. Much of my work involves trying to find a workable solution to a fiendish problem.. it's simplicity hunting. And when working with people around the university it's clear that they really don't want a tool that solves their immediate problem, they want abilities that solve problems like these. This is a very different thing. And besides I personally couldn't create So, out of ...

The Problem Tim Saunders, is fast becoming the poster boy for my belief that getting non-technical people coding is good idea. Tim started in the University of York accommodation office and inherited a task of managing students' requests to change their room. This room changing process was paper-based and requires various peoples' agreement and signatures. It resulted in a student having to carry an increasingly dog-eared form to college administrators and back to Tim and then to the old college administrators. It was slow, actively encouraged signature forging and reliably error prone. All the data collected then needed to be entered into SITS, our student database, which involves various charging and set up costs, so it really helps if this data is correct, having being verified by everyone in the chain. The Solution Armed with a some self-taught Javascript using the online learning platform CodeAcademy , Tim thought that the pile of paper forms in his offic...

Each year, around 200 chemistry students perform 20 Lab Experiments ( that’s roughly 4,000 a year ). Each test has a variety of marks to be kept by at least three people, the lab technician ( did they attend?), the tutor ( did they create the right chemistry and hand in their notes?) and the course leader ( are there any exceptions or mitigating circumstances etc). What was previously a paper-based method had recently been made to work in our VLE, but the data captured was in a cumbersome wiki text format. And whilst the user interface was simple enough, the technology was struggling and getting the data collected from the VLE into our marks database required considerable human effort. Working with David Pugh in Chemistry, we looked at using a Google Spreadsheets to collect the experiment data instead. After a few prototypes we have decided to use a very simple ( but quite wide ) spreadsheet to store the data and a web application “front end” for the markers to enter their marks.  ...

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

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