Collaborative Tools Project

I often get people coming to visit me who have a spreadsheet they want to get more from. They either want to automate certain tasks, or create new sheets with aggregated data or share data with colleagues in new ways. The hope is that with a little bit of code, new vistas will open up. Often the data is in a spreadsheet, it isn't clean enough to do anything useful with. If code is to stand a chance at making a spreadsheet more useful, then the data itself needs to be "code ready". Below is an actual spreadsheet brought to me, with number of areas for that needed data cleaning. As we worked together, we realised, a healthy spreadsheet isn't just about making sure your data is logical, there are also other factors that contribute to how easy your data will be to work with. Use formulas well - A few easy to learn formulas can significantly ramp up what you can do quickly with data. It is worth investing even just a few minutes learning new formulas and wha...

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.

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 ...

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. UPDATE: This version works better now  https://docs.google.com/spreadsheets/d/1BV9CTI7sq3wJXolWmq2Gji5cEmq12ngg7GCugXUzSYY/edit#gid=0

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 , ...

BETTER VERSION HERE NOW 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 ...

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) ...

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 " letti...

BETTER VERSION HERE 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 be...

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. ...

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 changi...

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 cour...