Skip to main content

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.


  1. I can recommend you to use Ideals virtual data room for collaboration. You may use it as a cloud storage and deals room for online deals implemented./


Post a Comment

Popular posts from this blog

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 conjunctio

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 necessary The 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

A Working Booking System In Google Sheets

Working with Andras Sztrokay we had another go at a booking system. This time it was to enable staff to book out a number of iPads over a number of days. You select the days you want, then select the Booking menu. Andras did an amazing job. It even creates a daily bookings sheet so you can see who has which iPads. To see this in action, go  here  and  File > Make a Copy (I won't be able to support you this is just provided to maybe give someone else a leg up, good luck!)