Skip to main content

About this blog

The Collaborative Tools Project was an initiative at the University of York to encourage the use of blogs, wikis, messaging and web2.0 tools with both academic and support staff. Teams of people trialled a number of different solutions, including Wordpress, Yammer, Jive, ELGG, Statusnet,, Social Text, LifeRay Social Office  and many more.

One of the issues with this approach was that, until everyone was using a tool in anger, the perceived benefits and willingness to join in is kept at a minimal level. Many of the tools simply didn't deliver. I tended to find, the teams who were open-minded tended to make the best of whatever they trialled.

I was most drawn to tools that offered the ability to be easily modified, and had an active "development community" surrounding them. Most tools assume a model of collaboration that isn't a good fit for many of different types of ways of working we need at the university. At very least a tool, or at least, I thought at the time, should have a collection of relevant plugins or extensions that could get us closer to our user needs.

And Then Google Apps Happened

It was a bit of a whirlwind, but in a move quite unlike the University of York, it was decided that we would implement Google Apps at York. Many of our staff were already using them with personal accounts, under the wire, and there was a sizeable band of advocates wanting to use Google Apps in their teaching.

Since then, my work has inevitably involved working with individuals and teams to get the most from the Google Apps suite, often working in a consultative mode and aiming to enable people to solve their own problems.

Sometimes I contribute to Knowledge Sharing events where we both share basic tips and tricks ( you'd be surprised how many of these I'm still learning ) and "did you know you can..." taster presentations. These sessions end with an "Ask an Expert" free for all where staff can run ideas or problems past a number of people there to help.

What This Blog Is About

This blog is an attempt to share some of the work people around the university are developing, some of them small, some of them complex. All of them hopefully have a re-usable aspect, often sparking off a different idea or use for a solution.

I always try to share copy-and-pastable code fragments to show that even the more involved Apps Script stuff isn't that scary and give anyone learning to code in Apps Script a leg up.

This blog has put me in connection with people at the university who are already "solving their own problems" with Google Apps and also with Google employees interested in what we're doing here at York. And whilst I was writing this a random person saw a post I'd written about a bug and shared a brilliant answer that solved the bug.


  1. Dear TOM SMITH,
    I'm not a programmer but I draw inspiration of the code Google Apps script that you have created. It 's very clean and well organized. I will continue to follow him. I am convinced that over time the site will represent an excellent reference. Thanks for your work and make it available to the community. Raffaele Paparella (italy)

  2. Dear Tom,

    Thank you for your blog.

    I wonder if you will be able to assist me?

    I would like to print a 1000 empty QR code labels linking to a simple ID 10001 to 11000 on an Google spreadsheet. Thereafter I will stick it on my loose tools, scan it and then categorize each loose item.

    What would you suggest?

    This will be much appreciated.


    Werner van Wyk (South Africa

  3. Werner,

    do something like this...



Post a Comment

Popular posts from this blog

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

This article looks at using Apps Script to add new features to a Google Spreadsheet.

At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form.

Spreadsheets can be better than Forms for data collection because:

The spreadsheet data saves as you are editing.If you want to fill in half the data and come back later, your data will still be there.The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessaryThe commenting features are brilliant - especially the "Resolve" button in comments.
One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often all look too si…

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.

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems.

A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it.

The QR app looks like this.

The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this.

The Web Application The web application, which only checks items in or out and should be used on a phone in conjunction with a QR cod…