Wednesday, 29 October 2014

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.


I love it when a plan comes together. Or when someone I've been working with really starts getting to grips with Google Apps.  Tom Grady shares what he's been doing with Apps Script.

I think he might have the bug.

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 of one of your files, like this...

But that is, to put it mildly a bit of faff... and if you put your files in a folder and make someone else the owner of the folder, the files still disappear when you leave ( the files don't inherit ownership from the folder ).

And then, you might get fancy and think you could create a solution with Apps Script.  So I tried that. My idea was to create a "dropbox" and a script to watch that dropbox and when a file is added to it, make a copy ( which I, or a departmental account would then own ). It worked fine. Except of course, the script can't delete the original file - because I don't own it. So, I was left with two copies of the file, one I ( or a departmental account ) owned, and the original. Sigh! ( The code below doesn't work by the way ) .

function check_dropbox() {

  var dropbox_folder = DriveApp.getFolderById("FOLDER_ID")  
  var main_folder = DriveApp.getFolderById("OTHER_FOLDER_ID")  
  var files = dropbox_folder.getFiles()
  while (files.hasNext()) {
   var file =
   var name = file.getName()
   var new_file = file.makeCopy(name, main_folder)
   Logger.log("Made a copy of: " + name)

Maybe you could write a script to simply move your files to someone else. Except you'd have to get  pretty fancy and page through your files if your script would need more than 9 seconds running time. Whilst this might seem like a good idea, you can't transfer ownership of a document to someone at another organisation.

What The Problem Is

The problem is that Google files are so tied to an individual. As an organisation, you need to be able to have documents that aren't tied to individual, but are tied to a role or a department.

And it gets worse as soon as say three universities want to collaborate on a project together. And remember, collaboration is what Google are supposed to be excellent at. Imagine these three universities want to collaborate by sharing documents.  You'd imagine that in the course of a project people might come and go, and ideally, you don't want files disappearing when people move on.

More subtly, you don't actually want any one university to own the files ( even if this was possible, which it isn't ). What is required is a form of shared ownership.

So Come On Google

Collaboration is your thing. I know these are easy problems to solve, but you can't argue that at times, we might not want to an individual, we might want our work to have longevity beyond our involvement and we might want to work fluidly with other organisations. 

At the moment I have someone asking, "We want to set up a five year project and share documents with three organisations. How do we do it with Google Drive?" ... and unless your view of collaboration is one where the documents are fleeting ephemeral things, rather than lasting records, there isn't really a Google-shaped solution that makes a lot of sense.

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 necessity I created (an as yet, unfinished ) Apps Script code library, to try and make doing jobs like the one above simpler. The point of this library is not to do anything fancy or specific but just to do those things that frankly Google should have rolled in as features anyway so that new coders could easily just wire their app together with a whole heap less complication.

The code library is called Handy Lumps because that's just what it is. Handy Lumps of code that you can re-use again and again. I won't tell you how to install Handy Lumps library into your code, but you can find that out easily enough. The project id is...


So What Does This Example Do, Tom?

In this example, someone fills in a Google Form to request some new software. What it then does is take that information and render it into a Google Document template file, and put it into a folder. Lastly, it saves the URL to the new file in the spreadsheet. It's amazing how many use cases look a bit like this.

It's also amazing how many processes start by looking like something mappable, something with a clear structure but actually are closer to an iterative collaboration. For example, the template that gets created has further questions in, which can of course be added to and bent into the shape that is required. And of course using Google Document +commenting feature you can easily bring someone new into the discussion for their advice and help.

So How Does This Example Work, Tom?

Let's look at the code. First I created a Form and then went to Spreadsheet and chose the Tools > Script Editor menu and added this. I'll explain what it does below.

function onFormSubmit(e) {

  //Get the values in a nice Array
  var values = HandyLumps.row_to_dict(e.range)
  var template_id = "TEMPLATE_DOCUMENT_ID"
  var folder_id = "FOLDER_ID" // Our Responses folder.
  var name = values['Name'] + " - " + values['What software are you requesting?']

  // Create a Google Doc
  var new_file_id = HandyLumps.copy_and_render_to(template_id, name, values, folder_id)
  var new_file = DriveApp.getFileById(new_file_id)
  var url = new_file.getUrl() 

  //Update the spreadsheet with a link to the new file
  var ss = SpreadsheetApp.openById("THIS_SPREADSHEET_ID") 
  var sheet = ss.getSheetByName("Form responses 1")//this sheet
  var row = e.range.getRow()
  sheet.getRange(row, 16 ).setValue(url)

  MailApp.sendEmail("", "New Software Request: " + values['What software are you requesting?'], url, {noReply:true})


The first thing the script does is turn the row of data into a nice array. This returns an array that looks like this {'timestamp':2014/29/10 10:55:45, 'name': Tom ...etc} It builds this array based on your header names ( and yes, it assumes they are unique for simplicity ) . Doing this avoids the issues with e.namedValues containing multiple items and gives me a simple array I can use later.

Next we tell the script the ids of the template document and into which folder we want the new documents to go.

We then create a new document from a template file. The template file has {timestamp} and {name} tags in which match my spreadsheet headers and get replaced with the values. To do this we use ...


This function returns the id of the new document created, so we then open it with DriveApp and get its URL. ( I did think about returning the File object, but often that's not what I needed anyway so decided on the simplest thing ).

I then use regular Apps Script to save that URL into the same row.

The last line mails a Google Group to let them know a new request has come in.

Ta Da!

There you have it. We've made quite a cute thing in a paragraph of non-scary code copy-and-pastee-style.

I'm all for the current trend to believe that "we all can be coders now" but I also think that the tools themselves could be made a damn sight easier to use before we welcome those brave souls willing to give it try.

Next Steps

More involved versions of the above code create a Google Doc from a template that has code in it, so that new document can show a sidebar ( for example to approve it, or give it a mark out of ten ) that let's someone move the document onto the next step. The data from the sidebar is of course saved into the right row using Handy Lumps functions like this...

HandyLumps.get_row_containing(ss_id,sheet_name, column_letter, match)

In the example above, a document's script might contain...

var doc = DocumentApp.getActiveDocument()
var doc_id = doc.getId()
var result = HandyLumps.get_row_containing("YOUR_SHEET_ID","SHEET_NAME", "M", doc_id)
var row = result[0]
var values =result[1]

... which essentially means that a document knows where to store its new data. And using cute things like Google Document's Named Ranges you can make a sidebar that stores people's textual contributions back into the original spreadsheet. I'll hopefully get to sharing that stuff later.