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

1ykOx87hMWudgdOl3i9XND-zeV8WEieBjVwxcPYG_2iDvX5kd70KpbfIl

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("YOUR-GOOGLE-GROUP-HERE@york.ac.uk", "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 ...

HandyLumps.copy_and_render_to() 

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.

By: Unknown On 4:27 AM

Continue Reading

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 people know where they are up to" and asking for input than a mechanistic model.

As we worked we realised that the flow itself was remarkably simple and was mainly about making documents available to people for commenting and letting teams of people know what the status is.

We realised that we could break most stages of the process into simple interactions and added them to the spreadsheet as menu items, for example, "Submit initial request" or "Submit to Team B" or "Reject request" etc.

These menu items do three things:

• Show a dialog telling the user what they were about to do
• Sent emails to the relevant people and groups
• Updated the project's status in a central spreadsheet

And to help us to speed up the process of development, we created a Processes spreadsheet that looks like this...

Each process gets rendered against the NamedRanges in the spreadsheet, so that {MainContact} becomes "Joanne Blogs" or whatever. Sometimes, even emails are rendered this way like {HeadOfDepartmentEmail} in the CC of the email that gets sent out.

Some special values had to be sneaked in such as {folder_link} and {link} which means that emails can contain links to the current document or GDrive folder.

Working this way has meant that fine-tuning who gets sent what is a LOT quicker, it not being hard-coded. It makes the authoring of those automatic emails which usually get sent to groups rather than people so that people can easily turn off email notification if need be. 

The menu items typically have code that look something like this...

function utc_approved( url ){
  if (is_a_member_of( "planning-utc-controllers@york.ac.uk" ) == true ){
    run_process( "Process:10", url )
    set_status("UTC approved")
  }else{
    Browser.msgBox("Not allowed", "You need to be a member of the UTC controller group to run this", Browser.Buttons.OK_CANCEL)
  }
  Browser.msgBox("UTC approved" )
}

... and the run_process() function simply shows a dialog, renders and sends out emails and sets the status of the request. Some menu items have code that moves a folder to an "Archived" folder for tidiness sake, but nothing too complicated.

I think the thing I learned from this project, once again, is that, like Booking Systems, although they always present themselves at the door as a the same thing, they are always in a clever disguise and really are something very, very different indeed. And by the time they've got through the door and taken their coat off it's already too late.

By: Unknown On 3:40 AM

Continue Reading

Testing Kissflow To Create an Approval Process

I was really impressed with Kissflow ( which integrates with Google Apps ), a tool for running processes.  I'm working with lots of departments at the moment that are doing, or trying to improve their processes using Google Spreadsheets but this may offer a more complete solution.

It works like this....

First you design a form as if it was one big form for the entire process, with different sections. 

Next, you create a diagram of your workflow that can contain, approval requests, requests for more information, and queries. You can also create parallel processes and I liked the way you can create sub-process blocks.

At each stage you add the people who need to complete it.

The next step is to then sort of define who needs to see which bits of information at which stage.

 And finally you publish and initiate your process.

I have created one to request to create a new programme. It basically goes,

1. Create a request
2. Get approval to go ahead
3. Check funding
4. Broader consultation
5. Final Sign off
6. Notify everyone that needs to know it has been approved

And then once you initiate a request, people can participate either from a nice dashboard or from their email intray. I need to look into this further, but using the demo version, first run through, I like it.

Are there any other great tools that people are using to run quite complex processes?

By: Unknown On 3:04 AM

Continue Reading

Improving Ethics Approval in Research With Google Forms and Spreadsheets

I have just met with a colleague who is using Google Forms to improve the Ethics Approval process for research projects. They are taking a slightly different path to the one I might have plumped for, and are creating, on-the-fly Google Documents, filling in the appropriate sections and then sending them to people to add comments to etc.

We thought he may have worked himself into a corner because although there is a function to capture when people submit a form ( onFormSubmit() ), there isn't one to capture if you "allow the user to edit responses" - there's no onFormUpdate().

We created a workaround by having a time-based trigger that just checks to see if the updated_timestamp is greater than the last_checked_timestamp.

The best part, for me, of working with my colleague was when I referred at some point to my JavaScript skills being far from honed and he asked "So is this JavaScript then?" and laughed incredulously. He was happily coding away without even knowing what language he was coding in. Brilliant.

One thing I'm starting notice over the last few weeks and months is how many people are working on processes... and/or process improvement. I keep meeting people with visual flow charts of their ideal worlds.  I wonder how far we should encourage Google Apps hacking ( where you potentially can get almost exactly what you want ) or whether we should be looking at tools that explicitly handle processes, like RunMyProcess ( shown below ) which even have their own tools to design your workflows and a gazillion integration plugins.

But then there's also an overlap with CRM and service desk applications. In most cases there's just stuff that requires stuff to happen that involves people to do stuff - which is pretty much what a CRM might do isn't it?

So, I'm wondering, given that I keep coming across people interested in creating workflows, should I be looking into a workflow lite app or heavyweight state of the art workflow system? Or should we keep crafting ( or crufting ) our own version?

By: Unknown On 7:43 AM

Continue Reading