Skip to main content


Showing posts from 2014

Module Chooser Using Javascript AND Apps Script

I had hoped that there might be a new Google Forms Add-on to do this, but no. I guess I'll have to have a go at doing it myself later.

Imagine you want your students to make a choice of four modules from a list of modules you're offering. To make the form easier to "not get wrong" it'd be good if when you chose module one, it disappeared from the following form items...

... like this.

The above is a hosted HTML on Google Drive ( because the Caja sanitation, or Chrome killed my Javascript ).

When a student chooses their preferred modules, the form is submitted to a regular doPost() method in a Google Spreadsheet's Apps Script like this...

function doPost(e) { Logger.log("Hi there") try{ //Get values from form var email = Session.getActiveUser().getEmail() var module_one = e.parameter.module_one var module_two= e.parameter.module_two var module_three = e.parameter.module_three var module_four = e.parameter.module_four …

One-To-Many Relationship in a Google Spreadsheet

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.

The Problem With Google

I'm too old to be fan of technology, but I quite like lots of it, and you can't argue that Google have definitely taken the lead on collaboration. At the core of all its products is the idea that what you are working on, you will want to involve other people, as collaborators, as commenters, as mentors or viewers.

But Google's model of collaboration is all wrong. Or rather, we've adopted Google tools at the university and although they provide the best tools for collaboration, their model of collaboration is hurting us. 
Google's model of collaboration best matches a small business and individual. This is reflected in how Google Drive works. 
For example, in Google Drive, if you create a file, only you can delete it. That's great isn't it? Except because a file is yours, when you leave the university, unless your admins move ALL your files to someone else, they're gone. 
Before leaving the university, you could individually make someone else the owner o…

Creating a Documentation Process With Google Forms, Documents and Spreadsheets.

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

Inserting a Link To A Google Drive File in a Google Spreadsheet

It seems Google are changing how you use the Google Drive File Chooser which always looks a bit goofy if you are using it in a spreadsheet because of poor design.

I had a go to see if I can resize the dialog at all and I don't think so. Here's my version of their code which demonstrates how you can wire it to insert a link to a Google Drive file.

There's an example file here: Drive Dialog example. Simply go to the File > Make a copy menu to see the code using Tools > Script Editor menu.

Where Do Good Ideas Come From?

Many people think ideas happen in a flash, a moment of inspiration, that eureka moment. They also believe that it's often "other people" that have ideas, either a boss or lone genius hunting down those illusive light bulbs.

The funny thing is, that the reality is, ideas can be slow things, taking time to come into being and most often they happen in discussion or collaboration. From what I've seen, the best ideas don't come from senior management, or from a sole genius but from people working together, people actually doing stuff in the real world. Many of these "good ideas" can be almost accidental.

My role at the university has been to both introduce people to the Google Apps suite, both evangelising them and working with people to help them realise their ideas.

Here's a story of an "accidental idea" that I think is good and is a great example of what I call "people actually stuff" and collaboration and discussion.

Someone in F…

Showing When An Appointment Slot is FULL using Google Forms and Apps Script

I'm sorry this isn't a finished solution you can just copy and paste. It's more of an example, sharing THAT this can easily be done which may help you figure out how to do it your case.

Lots of people at the University of York are using Google Forms to allow people to sign up to events. They use forms rather than Appointment Slots because they want to work with the data to generate registers for the people running the events.

But often these events have a capacity, that is, once 20 people have signed up to them, they're full.

There isn't much you can do with Google Forms to "live lookup" data and change form items if they're full, so we have developed workarounds to mimic this behaviour.

Firstly, having created our Form in the regular way, we create an extra sheet that keeps a track of how many people have have signed up, like this...

The count column has a formula in it like this...
=COUNTIF('Form responses 1'!G:G,B2)
...and the limit is a numb…

Gmail Scheduled Send Email

Someone recently asked for the ability to schedule send an email. It was for a link to a class feedback form and ideally they'd like to be able to send it near the end of the class.

We looked at Boomerang, which had issues with authenticating with the York domain, and broke the CSS of Gmail slightly. We tried mx Hero, a Chrome app but after a tip off from a colleague and reading the reviews and discovering it at times didn't send the email, or used http rather than https etc we thought it might be better to look into making our own solution.

Apps Script to the rescue!

I wanted something that was really simple, and easily editable to do what you want.

I made an Apps Script to check your Gmail Drafts folder. If there's a message whose subject begins with a date, like this, it sends it ...

[27/06/2014] Hello, this is a message from the future!

How to install it... 1. Go to the Apps Script here and choose the menu, File > Make a Copy. You will want to change the admin_email …

Email Me When Someone Edits a Google Document

Someone asked me if they could receive an email when a document has been edited.

function document_checker() {   var doc_id = "YOUR_DOCUMENT_ID_GOES_HERE"
  var doc = DriveApp.getFileById(doc_id)   var last_update = doc.getLastUpdated()   var scriptProperties = PropertiesService.getScriptProperties()    var last_checked = new Date( scriptProperties.getProperty('LAST_CHECKED') )   Logger.log( "Last checked: " + last_checked + " " + typeof last_checked )   Logger.log( last_update > last_checked )   if (last_update > last_checked){     Logger.log( "Sending mail" )     MailApp.sendEmail("YOUR_EMAIL_GOES_HERE", "Document has been edited", doc.getUrl() )     scriptProperties.setProperty('LAST_CHECKED', new Date() )   } }
function setup(){  var scriptProperties = PropertiesService.getScriptProperties();  scriptProperties.setProperty('LAST_CHECKED', new Date() ); }
So... follow these steps.
a. Create an Apps Script in Goog…

How I Learn ( And What I'm Learning )

This post might be a bit off-topic, as it's more about my own learning.

As part of whatever job I'm doing, there are normally regular forays into learning new things. Times when I have to throw myself into something I know pretty much nothing about. It's often learning something that I have a hunch will be useful for making things with, but sometimes it's more esoteric and tangentially related to my work.

As I threw myself headlong into another foray, I noticed that I was noticing the feelings and approaches I was going through as it happened. I thought I'd share it, but I don't really have a reason why. I just have a gut feeling that I should. So here it is...

The Task in Hand The cunning plan, in its simplest form, is to create a sort of Twitterwall for our Google Education Conference in our 3Sixty space at the University of York. It's largish room with projectors pointing at 4 huge walls. ( I wrote about it in previous blog post here ).
Research The Alte…

360 Degrees of Tom Smith ( What More Could You Want? )

Yesterday was fun. Sara Perry is planning to use the amazing 3 sixty space in The Hub on Heslington East for a module on museum exhibition design. The 3 sixty is a room in which you can display images on all four walls and play audio. It's quite a big space as you can see. If Sara is 5'10" how big do you reckon that wall is? Anyone?

So, before heading off there, with only a lunch break to spare I decided to get a better idea of what it could do. I downloaded the PowerPoint template file that you can use to create the content that you might display. 
I decided, like a megalomanic to see if I could make a room that was filled with my head in a really, nightmareish and ominous way. So I used the built in camera on my laptop to video myself, slowed it down, added effects and some audio from Sunn O))) and put the videos I made into the Powerpoint.
The video was like this... ( play them both at the same time )... as is meant to be displayed on opposite walls.

... and this ...

A Simple Example Booking Project in Google Spreadsheets

I've had a number of requests for code examples for my Booking Spreadsheet. I have held off sharing the code mainly because it became very complex and wasn't very useful as a starting point for anyone else.
But recently, I had to whip an Appointments spreadsheet together that didn't have personalised colouring of cells, that didn't create lots of sheets for a whole term, or permissions etc. 
This spreadsheet is just a list of "Book me" links that passes some data through to a simple web form, and then saves the person's email into that cell's value.
If you want to do something similar using this spreadsheet, 
a. File > Make a Copy - to get your copy b. Tools > Script Editor > File > Manage Versions > Save New Version c. Publish > Deploy as web app - to copy your web app URL d. Change all the variables in the code, there's only a few e. Run the Setup code - to regenerate the "Book me" hyperlinks to point at your new web…