Skip to main content


Showing posts from 2012

Missing Media in Blogger - Boo!

When someone leaves the University of York, their account is suspended which has the knock on effect that in Blogger your blog posts are written by "Unknown".  You can still see the blog posts.

Worse than that, all the media uploaded by that person also disappears. See this post here.

I'm looking into the Blogger API to see if I can at least find a user's posts and re-create them as someone else as an awful workaround.

I've added an Issue to the Blogger forum.... tumbleweed ... and nobody is interested.

This all comes about because Blogger isn't a "Core App" of Google Apps but it's rubbish. Boo Google!  At least let us be able to transfer ownership of blog posts or something. People regularly come and go at a University and hobbling their blog posts seems ridiculous. This may force us to have to think about using Wordpress ... which will be a shame.

Ten Tough Collaborative Inbox Questions

I had an interesting meeting with the Law & Management team about the possibility of using a Collaborative Inbox ( which is a kind of Google Group ) rather than our current in-house issue management system.

We went through what they wanted to do when people report problems and they came up with a heap of great questions. With almost all their questions, my answer was, "Ooh, I'll have to look that up and get back tomorrow". Here are the answers.

For the purposes of trying to answer the questions, I am using a Google Collaborative Index Group called "Silly Questions" which has been set up to allow people to email it ( without being a member ) and has two members ( Mike and I ). I will be mailing the group from a test account called "Not Tom".

Q1. Can an issue be automatically given an ID? This was an based on someone having mailed in an issue, then later calling in and being able to refer easily to the particular issue. An ID can be the quickest way…

Collaborative Inboxes Now Work!

Google Groups' different types of groups ( Web Forum, Email List and Collaborative Inbox and the other one ) do more to muddy the waters of understanding Google Groups than actually describe what it is they actually do. Anyway...

 I previously have blogged here that Collaborative Inboxes didn't work because in order to receive a reply to an enquiry - it meant that you had to be a member of a group - and therefore able to see everyone else's enquiries. This is the equivalent of group therapy for a clap clinic, in that as a question asker, you get to see everyone else's questions.

I was only now, just showing someone interested in using Collaborative Inboxes, that particular quirk when I discovered a "cc The original sender" checkbox which is by default checked. Yay!

This means you could now use Collaborative Inboxes for private issues, that get responses from the experts who are members in that group. AND fellow experts can see your response. AND the person wh…

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 jQue…

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…

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.


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,…

Analysing Collaboration, But Not As We Know It

Yesterday I went to a presentation about Analysing Collaborative Processes and Interaction Patterns in Online Discussions from researchers at the OU.

I found myself getting quite fired up, not in a good way, about their early work, which looked at how 12 students had worked on a collaborative task - generating 29 messages ( this was 2001 folks ). They went on to categorise the messages (by hand) like this...

Joint knowledge buildingAsking questions, dialogue extension promptsSupporting with reference or exampleAcknowledging/ replying / referring to another messageMotivation and commitment to taskInstructions/information - coordination messages... and then diagrams were drawn. I then found myself getting all worked up, not in a good way, about the diagrams, in which ( for me ) too much liberty had been taken with the spacial layout of the data, robbing it of potential meaning. For example, orphan messages were collected at the side, when maybe they should have been clustered ( is lonelin…

Bug or Feature? Google Groups and Google Drive, You Decide.

Photo: joffreylacour

I recently asked people if they'd be willing to help me work out what happens in terms of notification emails when you start sharing Google Documents with them. About 60 people said they'd be happy to take part.

So ( and this is part of the Booking System work ) I create a Google Group called Google-Guinea-Pigs and added everyone to this group. I set the email notification for this group to be "Web Only" because having found people willing to help, I didn't want to immediately start filling up their in trays with "You have been added to ... etc " emails.

So now, I wanted to share my spreadsheet with members of that group. I clicked the "Share" button and added the email address, google-guinea-pigs-group@.... etc. That seemed to work OK.

Next, I went to an unwitting member of the Google Guinea Pigs. When they went to Google Groups, in the My Groups list was indeed Google Guinea Pigs.

But when they went to Google Drive, the …

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 me…

5.0 Building a Booking System With Google Apps

I think I have a booking system that is close to working. A big leap forward was made when someone on the Google Apps message boards shared a way of avoiding the painful timeouts and false error messages after creating a calendar entry.

You basically create an event, getting its ID and then get it again, using that ID rather than working with an object. Who'd have thought? Anyway, this is the code that works.

var cal = CalendarApp.getCalendarById('');
var eventID = cal.createEvent(title, startDate, startDate).getId();// create/get event ID

//recall the event for each element you want to add
This workaround has meant that I could do away with the Task Queue sheet, which was becoming more complex than it needed to …

York Council Spending Above £500

So. Jon happened to tweet a link to York Councils spending on items over £500. I couldn't believe it when they were a collection of PDFs. What the actual...

Having found the CSVs here,  I tried to import them into one single Google spreadsheet, but the import seemed to fail ( or run very, very slowly ) pulling in extra data.

So, I created a Google Fusion Table and that quickly whipped in the 6 files for this year. Then I could aggregate the sum of moneys spent and group them by supplier name. You can see the company data here... it looks like the picture below.

Interesting reading? Well, each item might need more explaining. The top two spends this year so far are the people organising new offices for the Council ( York Investors LLP £6,704,386) and Yorwaste Ltd £2,383,920 ( are these the people that empty our bins? ).

Next on the list is REDACTED - PERSONAL DATA £1,682,939 which I can only assume is spent REDACTED REDACTED etc HSBC REDACTED.

At number 11 is Streamline Taxis with …

Can You Use a Collaborative Inbox for an Enquiries Email Address?

Google Groups have added a few new flavours of group recently. As well as a regular Email List, you can now make a Web Forum, a Q&A Forum and a Collaborative Inbox - all slightly different takes on the same thing.

As part of the move to Google, one of the biggest challenges are  what we call "non personal email accounts", for those accounts like Traditionally the handling of these accounts was done by a number of people, all sharing the log in details. In a Google-ized world, having accounts that can't be audited is "not the done thing".

Our first trawl for non personal accounts found thousands of them. This included accounts for projects, conferences, departments, etc. With many of them, nobody knew who was replying ( or not ) to any enquiries.

Gmail has the ability to delegate access to your account to someone else, but this still doesn't solve the problem. Essentially we need some account…

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 …

Building A Booking System With Google Apps

In my previous post, Building a Booking System With Google Apps, I tried to use Google's UI Builder to be a front-end to saving events into a Bookings Calendar for students to book hot desks in the Berrick Saul Treehouse. I wasn't totally happy with the results... still.

Lately, I have tried a completely different, simpler approach ( with quite afew groans about icky Google Docs issues along the way ) which has a sort of spreadsheet visualisation of the bookings that have been made. 

It looks and works like this.

Rather than taking apart the code in fragments, the entire spreadsheet is available here. Go take a look, from the File menu choose Make A Copy. 
If you create a Calendar and change the Calendar ID in the Script Editor you might be able to get it working for you. There are setup scripts to generate a "calendar-like" spreadsheet. The perches sheet has a list of columns that you might want to alter to suit your needs. The weirdest part is the "Booking Que…

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 …

Building a Dashboard with Google Apps

The other day I went for a chat with reluctant bloggerPaul Bushnell in Estates ( pictured right) to find out what they've been doing with Google Apps. The answer is lots, almost too much to fit into one blog post, but I'll have a go.

Estates have created a system for working with the numerous fix requests they get, from broken toilets to fire alarm faults to potholes and people stuck in lifts.

1. Data Logging with Android Tablets The team in Estates all have tablets with which they can get access to all the recent reports and log when problems have been dealt with and add any notes. For this they mainly just use simple spreadsheet data entry, rather than Forms or fancy user interfaces.

2. Workflow System  Once a problem has been dealt with, it gets moved from one sheet, onto the next. From here, the job might get logged as "we need to make sure this never happens again" and moved into other sheets. 
3. Working With External Suppliers In each of the spreadsheets, …

Creating "Homework" Google Sites

Tom Stoneham came to us with an interesting problem... "Can I automatically create 80 or so HomeWork Google Sites from a template for students? And when the deadline has been reached can their access be revoked and links sent out to examiners". The students' task will be create a site about a particular philosopher. The prototype looks like this...

I'd had a stab at solving this earlier to see it was possible, and maybe too quickly I jumped for python. But in the spirit of making something that a. worked, b. was sharable, c. I wouldn't have to maintain ( hopefully ), I thought I'd have a go a re-doing it in AppScript.

Having met with Tom, there were a few addition requirements:

Can student sites have unique IDs that are mapped on to a marking sheet?Can the URLs be kept in a list because, if you have 80 students then 8 markers may be given 10 students each?What is the best way for the University of keep the snapshot but still give the student the ability to tak…