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

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 building
• Asking questions, dialogue extension prompts
• Supporting with reference or example
• Acknowledging/ replying / referring to another message
• Motivation and commitment to task
• Instructions/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 loneliness a shared thing? ).

I heard about some interesting projects that look to make "sense" of online discussions. For example, AcademicTalk looks at restricting the opening sentence of every message in a forum, essentially then categorizing it a certain way.

See also: Digital Dialogue.

I really like the idea of "constrained conversation" ... almost like a parlour game that forces you into somehow being more communicative. This reminded me of work by Simon Buckingham Shum on argumentation where you sort of construct a discussion from visual building blocks that I saw in the last millennium - you know, it feels like more than a thousand years ago sometimes.

But I found myself getting really worked up, and not in a good way, about the very idea of analysing discussion anyway. If you look at the numbers, the crudest form of measurement, you get crude results. If you choose to tag the discussions your perspective skews everything... and if you change the environment to something better ( than email ) then you've changed so much that measurement is pretty pointless anyway. Yes, you might be better able to understand the collaborative processes that are happening but they are in such an artificial environment your findings are meaningless.

And anyway, how do you even define collaboration anyway.... One person's successful collaborative experience might leave the other participants feeling exploited. Crowdsourcing anyone?

I was also reminded of Jer Thorpe's visualization work for the New York Times on the life-cycle of a tweet. Here, the crudest measures... tweets and re-tweets are shown in realtime in an infinite animated 3D space. It's the sort of thing we maybe all should have instead of an email intray... a collection of funky diagrams that we keep an eye on, jumping in when the ripples get too wobbly or when a diagram "goes quiet". ( Excuse me whilst I get worked up in a good way ).

There for me is the chicken and the egg. If you "hand categorize" your discussions, any worth is both tainted by the viewer ( pretty much like quantum physics ) or completely irrelevant in that any findings can't really be applied elsewhere. 

And if you work with the crude numbers, unless you have a LOT of data then you don't have patterns that might be spotted automatically. Imagine a conversation bot popping up mid flame war and saying " I notice that this discussion thread seems to losing its focus and becoming all about petty point-scoring, please desist! ". I could imagine that making all the difference to humanities ability to discuss things rationally.

All Of Which Leads Me To This...

An idea...

So, if you can't have any analysis that requires a researcher to add it, and you can't rely on the crude numbers what might you use instead?

Remember, I've already said that you also can't invent a fancy-dancey bells and whistles parlour game style environment to force people to behave differently so that you can now measure them properly.

You could just use email and forums. These forums and messages would have extra meta tools though ( which is only slightly fancy-dancey ) that would enable the participants to tag discussions, particularly for negative, anti-collaborative indicators.

Imagine that in the flow of a discussion forum, you could select a certain sentence and from a pop-up mark it as "Self aggrandising" or "Funny, but disrupting the flow of the discussion" or "Rude and disrespectful" or "Missing the point" or "Deluded". Now imagine that these scores were anonymous... but aggregations of them were shown on your profile.

The point of this, is that when things are good, most people don't see it. Good interaction design isn't even noticed, it disappears. Also, making negative, perhaps harsh judgmental comments that are attributed to you is quite a challenging thing to do... " passive aggressive "... "bullying!" etc. But it's often clearer why collaboration DOESN'T happen, than agreeing when it does.

I've always longed for an "Unlike" button in Facebook, not because I'm a snarky miserabilist, but just because I want to show disapproval without getting into long pointless arguments about it. And maybe, the only person who gets to see this feedback is the person at whom it is directed.... maybe only after you've gone beyond a certain threshold.

Technically, this would work just the same as regular emails and forums, albeit with a few extra markup tools. It might show us what mixtures of "types" of people produce good and bad collaborative experiences or it might show us who the Naysayers are in an organisation.

Maybe looking for what makes good collaboration based on what is there is a bit like understanding space... you need to look at what's not there, the black stuff, dark matter, to understand how it all works.

By: Unknown On 5:01 AM

Continue Reading

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 newly shared spreadsheet was nowhere to be found, either by browsing or by actual searching. It didn't exist.

Now, when they went back to Google Groups and saw the email that said "This Spreadsheet has been shared with you" ... that was sent to the Group but not distributed to all the members ( which is what I wanted ) ... and clicked the link to the spreadsheet in that email... they could access the spreadsheet fine.

But there's also this...

When the member went back to Google Drive and searched for the file, it showed up in the look-ahead part of the search form (see below), but not in the file listing....

That would suggest (perhaps) that having looked at the file ( via the automatic invitation email link ) that some flag is set that says this file is now findable. Or is it something else?

My original aim, was to be able to create groups, add files and calendars without bothering the heck out of people, but know that, once created people could search for, or stumble across them as needed. So, for example, someone might search for "Holiday" and hey presto, without ever been told that a form exists, they can find it and maybe a shared Calendar.... how handy would that be? An intranet that works....

But it would seem ( and I might be wrong ) that you can't be given access to something without being pestered about it... and that you can't find something until you have already explicitly accepted its existence.

By: Unknown On 2:52 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

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('YOUR_CALENDAR_ID@group.calendar.google.com');
   var eventID = cal.createEvent(title, startDate, startDate).getId(); // create/get event ID

   //recall the event for each element you want to add
   cal.getEventSeriesById(eventID).setDescription(eventDesc);
   cal.getEventSeriesById(eventID).setLocation(location)
   cal.getEventSeriesById(eventID).addGuest(email)
   cal.getEventSeriesById(eventID).addEmailReminder(30)

This workaround has meant that I could do away with the Task Queue sheet, which was becoming more complex than it needed to be. It looks pretty much looks the same and works quite well.

Caveats

I would say that I have a few concerns about how fast the interface works. Sometimes it feels like an age to open the "Booking dialog" above. And once you click the "Book this perch" button, there is a visible delay as the cells get filled in ( each cell needs to look up to see which row it is in, and which column, but this shouldn't be too arduous a task ).

I'm also a bit worried about the need for a GREAT BIG AUTHENTICATION dialog that scares the hell out of you with its a. size, b. big red border and c. ugly HTML. You only have to grant access to it once but still... after granting access to my script to add a calendar invite to your calendar, you then have to click "OK"... and THEN you can go back to where you were and do it all again ( this time with no big ugly dialog). It's awful. Look.

I have a few niggling doubts about permissions too. For example, in order for anyone to do anything useful ( adding data to the spreadsheet ) for example, I think you pretty much give them access to everything else. Of course in a utopian dreamworld, that means that anyone using the booking system can also speed up my code and improve things a bit. I look forward to that happening.

I have irksome niggles about how an why the onOpen() script sometimes works, installing the "Booking..." menu and sometimes doesn't.

In the process of making this I've also found a suspicious side-effect loophole of working with Google Groups. My intention was to be able to share this Booking System with a collection of students, adding them to a group "silently" email-wise... this doesn't work... more on this later.

I don't trust cats either.

I will make a copy available once I've tested it a bit more.

By: Unknown On 8:46 AM

Continue Reading