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.
