Improving Ethics Approval in Research With Google Forms and Spreadsheets

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 even knowing what language he was coding in. Brilliant.

One thing I'm starting notice over the last few weeks and months is how many people are working on processes... and/or process improvement. I keep meeting people with visual flow charts of their ideal worlds.  I wonder how far we should encourage Google Apps hacking ( where you potentially can get almost exactly what you want ) or whether we should be looking at tools that explicitly handle processes, like RunMyProcess ( shown below ) which even have their own tools to design your workflows and a gazillion integration plugins.

But then there's also an overlap with CRM and service desk applications. In most cases there's just stuff that requires stuff to happen that involves people to do stuff - which is pretty much what a CRM might do isn't it?

So, I'm wondering, given that I keep coming across people interested in creating workflows, should I be looking into a workflow lite app or heavyweight state of the art workflow system? Or should we keep crafting ( or crufting ) our own version?

By: Unknown On 7:43 AM

Continue Reading

An Alternative To Google Calendar's Appointment Slots

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't embed it Google Calendar but it could work with our York single sign-on, and add details to Google Calendar. The Apps Script environment is limited in many ways - making it difficult to create an elegant solution, but its integration abilities might make it "good enough" for booking tutorial slots.

The Demo

But before you get too excited. There are a few caveats about this version of Appointment Slots.  

The first caveat is that you shouldn't expect bug fixes. This is a proof-of-concept / stop gap project. 

The first screen you and your users will see are these.

These two hideous screens are there to warn you ( and your students ) that I have access to your Calendar ( to create and delete events ), to send email and other things. There's nothing I can do about these screens. In a better designed world, Google would design Apps Script so that I, as coder, maybe only had access to a sandboxed area, or calendars I made myself that you then subscribe to - which would prevent me doing anything nasty or stupid. 

Another caveat is that I can't embed this app in your Calendar. It's a separate application.

The last caveat is that it all sort of works backwards. What I mean by this is that when you, for example, create a Tutorials Appointment Slots, the times are blocked out in your calendar. When a student books one of the tutorial slots, I have to create the booking event in the student's calendar and invite you to it.  

This is a side-effect of Google's permissions model. Because a student can't directly add items to your calendar, they have to add items to their own. You can see what it looks like in your calendar below.

It's also a bit slow. 

Using The Demo

There are really only two screens. The Home screen, shown below, lets you create an Appointment Slot in any calendars you own. 

When you have created your Appointment Slots, you are shown a screen similar to the one below that has a "Share this URL" link in it, which you can send to students. You also receive an email with these details in so that you can create a number of Appointment Slots on different days and collect them into one email that you send to students.

The student would then see a screen like this one. They can click the "Book this slot" and an event is added to their Calendar - and an invite sent to the lecturer who made the Appointment Slots ( see what I mean about backwards? ).  

A student can release an appointment booking by clicking the release link. 

All the data for this application is stored in what's called a ScriptDB. I did look at updating the ScriptDB when a student deletes a booking from their Calendar. That may be a version 2.0 feature, if I have any more time for development.

Top Tip: Create a Separate Calendar Just for Appointments Before You Start

If you create a calendar just for Appointments, and then create your appointments in that calendar then there is a way for students to browse lots of different options. If you have made your calendar public you can then let people browse your calendar (go to January 10th 2013 ) like this. Students could then use the link in the event description ( yes, I know, I know ) to be able to book onto that particular day.

If you are happy with all of the above then to see the demo go here.

p.s Thanks to +Martin Hawksey for his help along the way.

By: Unknown On 8:30 AM

Continue Reading

Platform Dilemma ( UI Builder vs HTML application )

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 add jQuery ( a Javascript library ) which means I can lovely widgets such a calendar drop-downs or type-ahead scrolling ( where you start typing the students' name and it makes suggestions ) etc. jQuery really helps to make an interface user-friendly... and if you imagine that the lecturer may have to enter 30 at a time, you can see why it needs to be easy and quick.

With a UI Builder interface, there are fewer widgets available, but the Chemistry dept. themselves will ultimately be able to continue hacking anything I build as I work with them. It's very easy to just open up a UI Builder window and start adding things or moving them around whereas working with jQuery and HTML may be too big a leap into the geeky unknown.

By: Unknown On 8:00 AM

Continue Reading

The Day I Dropped Round The Security Guy's

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 Google Calendar new event form by hacking the url variables easily. Like this...

http://www.google.com/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.

Conclusions?

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.

By: Unknown On 3:54 AM

Continue Reading

The Day The Security Guy Dropped By...

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, and give them View access, but if you do that, then any menus ( which load the interface that makes changes ) don't load and so you don't get to be able to add data by proxy as it were.

If this route, of selectively locking bits of the file was almost possible, my old method of using a Task Queue that ran once a minute would mean that all the permissions, rather than being about the ActiveUser, would be tied to what's called the EffectiveUser ( the person who wrote the code and started the triggers that calls the code ).

Hang on, even I'm losing it now.

At this point, I thought... hang on... I can put MOST of the code into a standalone script library. In this way the ActiveUser would only be able to edit the code that displays the user interface. Oh. Still not right, because at that point naughty hacker could add anything they want.

And you see there is the problem. In order to do anything with this spreadsheet we're both looking at, you pretty much have to give people access to Read/Write all spreadsheets. Regardless of how innocuous the thing you are trying to do, the ActiveUser will be presented with an authentication dialog that looks like this...

And it says, "Only authorize the script if you truly trust the author".... Truly trust? Truly? Madly? Deeply? I don't even truly trust myself... how can I make a decision on that?

So basically, my dreams of an organisation creating and sharing solutions only work, if by sharing you mean...

You can take a copy of this data for yourself, and run the scripts on what is now your data

... what this doesn't mean is that...

We can work on the same data, using shared code and do anything useful with it.

All I wanted, he sighed wistfully, was to be able to collaboratively fill in a spreadsheet, using a slightly better interface than the formula bar but in order to do that the ActiveUser ( for those still paying attention, that's YOU! ) have to click a dialog that says you truly trust me, with all your data, email, calendars etc.

It's not going to happen is it?

In this case, it would be easily fixable if I could make the Scripts in a spreadsheet have the permission for you to run ( and maybe even read ) them but not to be able edit them. Or maybe I could say that I only want to edit THIS spreadsheet, and not have write access to ALL YOUR SPREADSHEETS!

As ever, permissions come to bite us in the arse. Ouch.

p.s I wonder what the hell Google are thinking with regards to all the AppsScripts/WebApps like these that are appearing in Chrome AppStore, which seem to also have a "truly trust" dialog in them, and none of which I have yet dared to run. Would you?

p.p.s Arthur's "solution" is to write the whole thing as a standalone web app, but, from a philosophical point of view I wanted to create solutions that other people could take and evolve to suit their needs. And also, writing a web app is quite hard.

By: Unknown On 9:16 AM

Continue Reading

6. Booking System and Permissions (Update)

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

What this then means is that when the student makes a booking, they need to agree via a big ugly authentications dialog that they agree to have ME tinkering on their behalf.

What all this means is that if I use a central calendar I would need to add the student to a Google Group and make that Google Group able to manage all events on that calendar in order to add stuff to that calendar UNLESS I revert back to using a triggered script.

Are you still with me?

It would seem that I need to have TWO separate ways of adding events ( one via a trigger and the other when the student makes a booking) and work with calendars independently ).

That is to say, when a student makes a hot desk booking...

• An event is added to the students' calendar using... CalendarApp.getDefaultCalendar()
• An event is added to a task queue which will ultimately use ... CalendarApp.getCalendarById('YOUR_CAL_ID@group.calendar.google.com'); 

This means that as well as having a spreadsheet, that is effectively uncoupled from the calendar which it represents, I also have a central calendar that is completely uncoupled from each an every students' calendar. This means that were I to delete a booking in the central calendar, then the student would not be informed that this had happened.

Does this even matter?

I knew when I started using a spreadsheet as a pseudo-calendar, that it might be a bit of work to "sync" either what was in the calendar with what was in the spreadsheet and vice versa, but I'm now in the position of not actually needing a central calendar, I mean, what's the point of it exactly? I guess the answer to that is different presentation views ( week, day, agenda etc ).

But this get's less useful the more bookings there are ( which was the original need to even begin this project ) because as soon as you have 30 or so booking on one day, the calendar interface starts becoming useless, it's just too jumbled.

So the spreadsheet should become the golden master really.... there shouldn't be a central calendar and maybe that will clear up any permissions issues ( it will at least remove some of the icky corners of the code ) along the way.

Uh oh!

There are now some oddities with regards Google Drive and Google Groups. I think I'll save those for another post... maybe later.

By: Unknown On 3:48 AM

Continue Reading

When Is Just Enough is Too Much?

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 be able to book a desk if someone else has already booked it ( sensible enough ) arises. 

Except that an administrator might want to quickly and easily unbook a desk if someone phones in sick, freeing the desk up for other users.

An administrator might simply want to block out a number of desks, which might be done by setting the background to grey. Simple.

These sorts of things are easy when "it's just a spreadsheet" but they quickly become another feature to get bugs / slow down /need an interface / need maintaining when what you are looking at is software ( rather than "just a spreadsheet" ). 

Finding the balance between the two is essential. What we need is a collection of people getting on doing great things with great tools, not the creation of another bottleneck of slightly less professional software development.

As our collective experience with AppsScript grows, we'll learn and develop approaches that we can share that people will be able add to their projects.

By: Unknown On 7:04 AM

Continue Reading

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

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

By: Unknown On 5:00 AM

Continue Reading