Skip to main content


Showing posts from 2013

Data-driven Visualisation Tools - A Resource List

This post follows on from Visualisation and Visual Tools - A Resource List

A visualisation can be everything from a complicated multi-dimensional data set that incorporates time and geographic information, to a couple of blobs that represent and idea.The Periodic Table of Visualisation may help you broaden your visualisation understanding and maybe reveal the difference between a Sankey Diagram and an Hyperbolic Tree or reveal the layout of a Radar Tree and a Mindmap.

The Information is Beautiful site lets you browse lots of different visualisations and visualisation styles. I'm particularly drawn to those visualisations that aren't complicated but convey a simple idea well, like the one below.

The Basics

For most simple visualisation needs, you really only need something simple. The tools in Google Forms ( to create surveys ) and Google Spreadsheets ( to store and manipulate your data ) and Google Charts ( to display your data in interesting ways ) are enough for most people.

Visualisation and Visual Tools - A Resource List

I recently had to put together a collection of tools for "thinking visually" and thought it would be good to share it here. It is basically a collection of tools for you to try.

The starting point was finding ways to display quantitative information but we also had to cover the creation of more emotive or softer images that communicate an idea, maybe very effectively, with less reliance on the numbers.

My mind is always drawn initially back to this image, which plots Napoleon's Army's size, position, direction and even the weather into one chart.

From: and of course the books of Edward Tufte.

But we also looked at images such as this one, that looks at data gathered about attitudes to the Hijab in Denmark. I find it interesting that even though I can't see what the percentages refer to in the image below, I am blown away by the idea of a headscarf pie chart. In some ways, it almost doesn't matter what the data…

From Survey To Google Spreadsheet To Google Document

Earlier today we were looking over the results of a survey we'd put out with Google Forms. The answers were well thought out, very long and textual and impossible to read in a spreadsheet.

As a group we want to read the responses and share our thoughts about them using the comment feature in Google Documents so I whipped up this script to move the all the data from a spreadsheet to a Google Document.

function document_from_spreadsheet() { var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet = ss.getActiveSheet() var header_range = sheet.getRange(1,1, 1, sheet.getLastColumn()) var headers = header_range.getValues()[0] var data_range = sheet.getRange(2,1, sheet.getLastRow(), sheet.getLastColumn()) var values = data_range.getValues() var doc = DocumentApp.create(ss.getName() + " Exported") var body = doc.getBody() for (var h in headers){ h = Number(h) var header_name = headers[ h ] var p = body.appendParagraph(header_name) …

Blogging Archaeology Students

As part of my work one of the more fun things I get to do is to introduce groups of people to new tools and approaches. The students in the Visual Media module in Archaeology are using blogs to experiment with ways to raise the profile or extend the awareness of an exhibition or artefact.

There's everything here, from the Mitford sisters to a mummy in a museum in Bolton. There's even a blog by Edward VIII himself ( kinda ).

Like all bloggers, they need a little encouragement in the form of comments, so if you have a spare minute or two, could you add a thought or suggestion or response to one or more of these fine archaeology blogs...

Creating a Process Workflow with Google Spreadsheets

I thought I'd share this ongoing project I've been working on about creating workflow support for a team. I've referred to it before in a blog post called Using Spreadsheets Instead of Forms, in which I argue how using the commenting, "live saving", versioning and collaborative features of spreadsheet far exceeds what can be done with a simple Google Form (see below). This is especially so when the data you are collecting is long and complicated. ( Tip of the hat to Tim Saunders who had this idea ).

Having quickly trialled and liked bespoke workflow apps like Kissflow, and also read the documentation for bigger workflow systems like RunMyProcess, we realised that one of our first challenges was to define the mood or tone of this process. It was clear that the process we were trying to support was more consultative and discussion-based than a hard passing of numbers and approvals in a clear process flow. Our process needed to be more about "letting the right …

The Apps Script and Google Spreadsheet Room Booking System

You may remember my previous posts about attempts to create a room booking system with Apps Script. This system uses Apps Script to populate a spreadsheet with weekly sheets, that contain lots of "Book me" links ( see below ).

The "Book me" links open a very small web application that is essentially a confirmation screen with a "Book" button.

When a student books a room, the web application says "Booked" and adds the booking to a central calendar and invites the student as guest, so that it appears in their calendar.

One very important aspect of this booking system was the booking quotas that student are given. Each student can only book 3 hours a day in each room. The student is allocated a colour, not just because it looks nice, but because, as you can imagine when a large amount of students are wanting to finish their projects with a finite resource, it can get quite busy. The admin team previously has been spending HOURS policing the bookin…

Emailing Someone a Fancy PDF When They Submit a Google Form

Google Forms are a great way to gather and process Application Forms, but often the data entered is very text heavy and apart from a Google Spreadsheet being a hopeless way to read text, the submitted might need a "better formatted" version of their submission.

Here's a code snippet that builds on the ideas in a previous post,
Using Google Docs To Make Google Spreadsheets Easier to Read, where we use Apps Script to make it easy to add {tags} to a document that correspond to the names of headers in a spreadsheet and then use that document as a template.

This code, gathers the student's name and email and shows how to create a rendered Google Document and then email the PDF of their data back to them when they submit a Google Form.

var folder_id = 'YOUR_FOLDER_ID_HERE'var institution_folder = DocsList.getFolderById( folder_id ) var personal_folder = institution_folder.createFolder(student_name) var template_id = 'YOUR_TEMPLATE_DOC_ID' // The…

Creating A Shared Logging System

This is an approach we've used and re-used a number of times. Imagine you want a group of people to share some information using a Google Form. But although you don't really want to share the spreadsheet of the collected data, you do want people to use a subset of it.

In this example, we will create a "Research Logger". Here's the first form, go fill it in.

The Confirmation Page of the Form has a link to a web application made earlier, like this.

The web application uses a Table Chart visualisation to show a subset of the data but has really nice filters so that you can drill down on the information. It looks like this.

The web application above is browsable here.

The code to display a table like this is... function doGet(e) { var spreadsheet_id = 'YOUR_SPREADSHEET_ID' var ss = SpreadsheetApp.openById(spreadsheet_id) var sheet = ss.getSheetByName("Form Responses") var last_row = sheet.getLastRow() var last_column = sheet.getLastC…

Using Hangouts on Air To Run A Distributed Mini Conference

Sara Perry ( Archaeology ) wanted to run a mini-conference as part of her Gender and Digital Culture work using Hangouts.
The plan was to have two presentations, one from Sara in York and one from Lucy Shipley and Jim Osborne from the University of Southampton. The presentations would be screen-shared. The audio was amplified so everyone in the room could hear it ( of course ).
There'd be some general discussion ( between everyone ) time and then we'd split into teams that discussed the issues raised and add ideas and questions and solutions into a shared Google Document.
The participants, at both York and Southampton were given an iPad that was already logged into the document. Potentially, the "general public" could have "tuned in" via the YouTube stream and if anyone had have been watching it via Google+ then they would have had the opportunity to ask questions of the speakers. They used Twitter for the backchannel in this case though.

Sara ( as seen by…

Tidying Up Spreadsheet Data Gathered In A Google Form

Google Forms are a great way to make it easier to get the right data from your colleagues, but after a while your spreadsheet data can get very messy and you need to organise it a bit.

For example, Jo created a form so that people could submit requests to go on external courses that asks for all the data needed for them to be able to make a decision about it. It asks how much it costs, how much the hotels and travel will be and who will benefit from the course etc. It works really well.

But now that lots of people have submitted it, and had their course requests approved, she wanted to tidy up the spreadsheet without losing the data so that it was easy to process a small list of current requests. Funnily enough, two other people in the last two weeks have come to me with identical needs, so here's an example that works.

What it does... All this script does is, if you set a column called "Status" to "OK", then it moves that row of data to a hidden sheet. The she…

Strange Problem With Older Google Forms

I've had two people complaining about this this week.

If you have a Google Form in a spreadsheet that was made a while ago, you are still given the old form editing interface.

There doesn't seem to be a way to bring a Google Form up-to-date, which is a big pain if your form is very long and complex since the only way to do so is to just start all over again.

Linking a Google Doc To a Form For Assessment

In the previous blog post, I showed how we get data from a Form and render it into a Google Document.

In this post, I want to show how the Document, as it is created can have a link appended to it to another Google Form that will be used for marking that document. We have used this where people are submitting application forms and lecturers are grading those applications.

First, create your new evaluation form, deciding what field will be autopopulated with data from the application form, for example, student name and institution etc. Also add the form items you want to use for marking, which might include drop down menus or multiple choice or paragraph text areas.

 and then select then choose the menu Responses > Get pre-filled URL. Once you have filled in this form you will be able to add some code to your Google Spreadsheet like this... and work out which value you need to map onto the bit that says... entry.1021949580 ...obviously all of these will need changing for your values…

Using Google Docs To Make Google Spreadsheets Easier to Read

A lot of our staff are using Google Forms to gather lots of data into spreadsheets, from Grant Application forms to self assessment questionnaires and more.

Spreadsheets are of course great places to store lots of data, but when that data is mainly textual, it is ridiculously hard to read and edit in a spreadsheet.

Our solution has been to generate a Google Doc of the data in a row of data. Sometimes this happens automatically and is emailed to the relevant people and sometimes we add a little interface to be able to say "Make a Google Doc with this row" to the spreadsheet.

The challenge is making it easy to set up.

Our Solution
We've used this a few times. First we create our Google Form and add some data. The spreadsheet now has a list of column headers across the top.

Now, we make a template Google Doc. In Tools > Script Editor we add some code that allows us to insert the spreadsheet header names as funny tags, like this, {Username}. You could of course do this by…

A Difficult Day With Google Apps ( Boo! )

Yesterday was a difficult day with Google Apps. Almost everything I tried to create was stopped dead in its tracks. I am making some spreadsheets that support an approval workflow process and so far it had been going really well.

I have a Google Form, that initiates the process. This keeps a list of the workflows. These are displayed in a web app. When the form is submitted, a copy is made of a master spreadsheet is made and the people fill in the data in that.

At various points of completion, a controller person fires off scripts like "Initial Approval", "Approved by the Teaching Committee" and the relevant people are notified that they now need to "fill in their bits".

So far so good.

I began thinking that, at some points it would be really useful to assemble the relevant data, notes, reminders and links into a Gmail draft so that the controller person might check it over, add any extra message and then send it. But with Apps Script you can't create…

Confusion about Apps Script Projects in Spreadsheets.

I'm in some confusion about how Apps Script projects work when embedded in a Google Spreadsheet. In my current spreadsheet, when I choose "Script Editor", I see this...

As you can see, there are multiple projects in the spreadsheet. I don't get this. It always happens that when I copy a spreadsheet too... that I end with a "Copy of XXX" and "XXX" Apps Script projects inside the spreadsheet. 
I can add extra projects via the "Create a new project" link but I can't remove projects from spreadsheets. This gets more confusing if both "Copy of Web App n Stuff" and the "Term Week Dates Booking Project" have a doPost () function in them. Which function gets called? 
Why would I want more than one Apps Script project in a spreadsheet?
Why can't I flip a project out to be a standalone Apps Script project?
When making copies of spreadsheets - why do I end up with multiple projects in a spreadsheet?
How might I remove a…