Tag Archives: #Dev8eD


For a while at JISC CETIS we’ve been keeping an eye on an open source platform called Booktype designed to help in the creation of books from print and electronic distribution in a range of formats.

As part of this weeks Dev8Ed Adam Hyde, who is the project lead on Booktype, gave an overview of the project highlighting some of the technical wizardry. Kirsty Pitkin has posted an overview on the session here. Because Dev8Ed was an ‘unconference event’ there was an opportunity for Adam to put one of his other hats on and talk about Booksprints.

A Book Sprint brings together a group to produce a book in 3-5 days. There is no pre-production and the group is guided by a facilitator from zero to published book. The books produced are high quality content and are made available immediately at the end of the sprint via print-on-demand services and e-book formats. - www.booksprints.net

You can read more about book sprints from the official site, but I thought it was worth sharing some of my notes and reflections on Adam’s session.

Who is already book sprinting?

Book sprints are already widely used by FLOSS Manuals, a community project to produce manuals for free and open source software.

Creating the right environment

Adam stressed that people should meet in a ‘real space’ for the duration of the sprint which is usually 3-5 days. The space is usually a shared house where people can work, sleep, prepare food and eat. As well as the physical space, mental preparation is designed to be light. Avoiding traditional publications models as a mindset appears to be key, also Adam mentioned that pre-preparing a structure can make the processes harder as more time is spent explaining this to team members than just collaboratively working on it in the first day. Something Adam also mentioned was that for each day you should start work at 9, finish at 5.


Table of contents (sometimes takes longer)Start with review, show text, discussInteractive process discuss, write move own. Switching roles (proof, tidy)Interactive process discuss, write move own. Switching roles (proof, tidy)Finish up layout
+critical point - getting people into the creative flow (finding chapter author key - looking what people are interested in)Interactive process discuss, write move own. Switching roles (proof, tidy)Finish writing new chaptersNo new contentFinish up layout


Picture copyright flossmanuals.net
Picture copyright Adam Hyde flossmanuals.net
[License: GNU GPL2]

Above is a rough timetable for a sprint. To elaborate slightly, first morningis spend all working together on a table of contents. Use post-it notes to write areas to cover, grouping, conflicting terms, what's missing, etc. Get people writing things as quickly as possible. Once this has been drafted the facilitator has a key responsibility in assigning the chapters to the the right people, using cues from the TOC session like people with particular knowledge dealing with a specific chapter. The facilitator has to have a strong hand - doesn't have to be topic specialist. They need to drive forward production. Chapters don’t have to be done in chronological order, the main thing to to get things rolling. The facilitator should encourage discussion, if someone is struggling with something move them on, but don’t pass partial chapters to other people as it slow things down.  At 5 all stop and relax.

Tuesday starts with a review. Text is shared and discussed. This iterative processes, which includes breaking tasks with switched roles, continues to the Wednesday. On Thursday no new chapters are written and existing work is tidied up. On Friday the focus is finishing and layout.

Adam mentioned one technique for removing structural roadblocks was printing chapters then laying them on the floor, giving people scissors and markers to let them do a manual cut’n’paste job.  In writing this post I found other tips, case studies and material here.

So book sprints look like a great way to get content out. At JISC CETIS we are in the early stages of planning our own book sprint, so hopefully soon I’ll be able to share my personal experiences of some rapid content development. One thing I’m interested to find out is if the technique suits particular disciplines. Do you think a small group of academics could publish a textbook on something like ‘introduction to microbiology’ in 5 days? Is this a way JISC should fund some content?

[Update: Some comment on this on Google+]


Last week was the latest event as part of DevCSI, Dev8Ed. As part of the event I put my name forward for a session on ‘hacking stuff together with Google Spreadsheets’. The session appeared to go down well as I was asked to repeat on the second day and along with Alex Bilbie’s HTML5/CSS3 session got lots of votes for the ‘best of Dev8Ed’.

It was a bit weird talking to a room of mainly hardcore coders about hacking stuff with spreadsheets but hopefully they got an insight into a product that is quickly loosing it’s shackles as a glorified calculator into something else (one of my favourite blogs right now is Bruce Mcpherson’s Excel Ramblings which as well as slipping into Google Apps Script has some wonderful posts on subverting MS Excel).

Below is the video from my first (shorter version) of my presentation followed by slides hosted on Slideshare and Google Drive, and here is the delicious stack of links.

Hacking Stuff Together with Google Spreadsheets from UKOLN on Vimeo.

Hacking stuff together with Google Spreadsheets


Another post related to my ‘Hacking stuff together with Google Spreadsheets’ session at  Dev8eD (A free event for building, sharing and learning cool stuff in educational technology for learning and teaching!!) next week. In this example Google Apps Script is used to create a custom user interface that can be used in Google Spreadsheets, allowing tutors to enter feedback and grades based on individual assessment criteria pulled from a central data source (another Google Spreadsheet). The system then generates personalised feedback forms (Google Documents) based on the data and distributes them to students.

As part of my work outwith JISC CETIS I have been helping staff at Loughborough College with their LSIS funded Fast Tracking Feedback project. As part of this project I’ve helping staff create a system that standardises and speeds up the return of assignment feedback to students. This project has generated a number of outputs for the wider community including training material and some code snippets (sending free SMS | generating Google Documents from Spreadsheets).

As my official involvement in the project comes to the close there is another chunk of code and resources to push out in the wild. It’s complete copy of the beta system currently being piloted with staff at Loughborough College. If you want to get an idea of how it works here’s a short video demonstrating the system I did as a lightning talk at GEUG12.

The code

If you want to pick over the code for this I’ve dumped a copy in github. This is more for reference as the code makes use of the Apps Script GUI Builder for parts of the interface, which can’t be extracted from the Spreadsheet. For a functional version you’ll need to make a copy of the four documents linked to below (this is followed by some instructions on setup and usage). I should also point out that this system has been build around the British BTEC qualifications. An example of the assessment and grading criteria is on page 3 of this document. Hopefully there is enough reusable code for other qualification systems.

Files you’ll need

  • Master Spreadsheet – this is the main document with all the Apps Script in it.
  • Master Template (you’ll need to File > Make a copy) – this is a Google Document used as a template for the form
  • Criteria Sheet – spreadsheet of units and courses with the related assessment criteria/rubric
  • Student Lookup – spreadsheet of student fullnames and related Google Apps ids (used because the App Script Group Services can only return ids and email addresses)

The basic setup

  1. Place all four copied files into a folder in Google Docs (you can name the folder anything you like).
  2. Change the share setting on the folder so that either ‘Anyone with the link’ or ‘People at your Google Apps domain with the link’ can view.
  3. Open your copy of the Master Spreadsheet and open Tools > Script Editor…
  4. On lines 17-19 copy and paste the document id/keys for Master Template, Criteria Sheet and Student Lookup. You can get these by opening the documents and looking at the browser url for the highlighted bits.
    Where to find spreadsheet/document keys
  5. From the Script Editor you can also open File > Build a user interface… and then open the importStudentList and click on the ‘Enter the group …’ label to edit the text in the property pane on the right-hand-side and then similarly for the textfield beneath it. There’s also the option to customise/add logos to the entryForm GUI
  6. In the Criteria Sheet create your list of units/courses and associated assessment criteria
  7. In the Student Lookup sheet import a list of Google Apps Ids and names

There are additional options in the code to change folder names, pass, merit, distinction colouring.
That’s it. Enjoy and any of your thoughts are welcomed in the comments (I can’t make any guarantees to respond to all)

The Fast Tracking Feedback project is funded by the UK’s LSIS Leadership in Technology (LIT) grant scheme.

Another post related to my ‘Hacking stuff together with Google Spreadsheets’ (other online spreadsheet tools are available) session at  Dev8eD (A free event for building, sharing and learning cool stuff in educational technology for learning and teaching!!) next week. This time an example to demonstrate importHtml. Rather than reinventing the wheel I thought I’ve revisit Tony Hirst's Creating a Winter Olympics 2010 Medal Map In Google Spreadsheets (hmm are we allowed to use the word ‘Olympic’ in the same sentence as Google as they are not an official sponsor ;s).

Almost 4 years on the recipe hasn’t changed much. The Winter Olympics 2010 medals page on wikipedia is still there and we can still use the importHTML formula to grab the table [=importHTML("http://en.wikipedia.org/wiki/2010_Winter_Olympics_medal_table","table",3)]

The useful thing to remember is importHtml and it’s cousins importFeed, importXML, importData, and importRange create live links to the data, so if the table on wikipedia was to change the spreadsheet would also eventually update.

Where I take a slight detour with the recipe is that Google now have a chart heatmap that doesn’t need ISO country codes. Instead this is happy try to resolve country names.

heatmap missing dataOnce the data is imported from Wikipedia if you select Insert > Chart and choosing heatmap, using the Nation and Total columns as the data range you should get a chart similar to the one below shown to the right. The problem with this is it’s missing most of the countries. To fix this we need to remove the country codes in brackets. One way to do this is trim the text from the left until the first bracket “(“. This can be done using a combination of the LEFT and FIND formula.

In your spreadsheet at cell H2 if you enter =LEFT(B2,FIND("(",B2)-2) this will return all the text in ‘Canada (CAN)’ up to ‘(‘ minus two characters to exclude the ‘(‘ and the space. You could manually fill this formula down the entire column but I like using the ARRAYFORMULA which allows you to use the same formula in multiple cells without having to manually fill it in. So our final formula in H2 is:


Using the new column of cleaned country names we now get our final map

Interactive map: Click for interactive version

To recap, we used one of the import formula to pull live data into a Google Spreadsheet, stripped some unwanted text and generated a map. Because all of this is sitting in the ‘cloud’ it’ll quite happly refresh itself if the data changed. 

The final spreadsheet used in this example is here

Tony has another Data Scraping Wikipedia with Google Spreadsheets example here


Next week I’ll be presenting at Dev8eD (A free event for building, sharing and learning cool stuff in educational technology for learning and teaching!!) doing a session on ‘Hacking stuff together with Google Spreadsheets’ – other online spreadsheet tools are available. As part of this session I’ll be rolling out some new examples. Here’s one I’ve quickly thrown together to demonstrate UNIQUE and FILTER spreadsheet formula. It’s yet another example of me visiting the topic of electronic voting systems (clickers). The last time I did this it was gEVS – An idea for a Google Form/Visualization mashup for electronic voting, which used a single Google Form as a voting entry page and rendering the data using the Google Chart API on a separate webpage. This time round everything is done in the spreadsheet so it makes it easier to use/reuse. Below is a short video of the template in action followed by a quick explanation of how it works.

Here’s the:

*** Quick Clicker Voting System Template ***

The instructions on the ‘Readme’ tell you how to setup. If you are using this on a Google Apps domain (Google Apps for Education), then it’s possible to also record the respondents username.

record the respondents username

All the magic is happening in the FILTERED sheet. In column A cell 1 (which is hidden) there is the formula =UNIQUE(ALL!C:C). This returns a list of unique questions ids from the ALL sheet. If you now highlight cell D2 of the FILTERED sheet and select Data > Validation you can see these values are used to create a select list.

create a select list

The last bit of magic is in cells D4:D8. The first half of the formula [IF(ISNA(FILTER(ALL!D:D,ALL!C:C=$D$2,ALL!D:D=C4))] checks if there is any data. The important bit is:


This FILTERs column D of the ALL sheet using the condition that column C of ALL sheet matches what is in D2 and column D matches the right response option. This formula would return rows of data that match the query so if there are threee A responses for a particular question, three As would be inserted, one on each row. All we want is the number of rows the filter would return so it is wrapped in COUNTA (count array).

Simple, yes?