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

Showing When An Appointment Slot is FULL using Google Forms and Apps Script

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,B2)

...and the limit is a number we entered of how many places that slot has.

Secondly, we need to create a couple of functions that get fired when someone signs up to a session, like this...

function find_limits(rangeA1, their_choice){ //the values for the range and choice parsed in the main function

// the range is where you want to look in Group Totals var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet = ss.getSheetByName("Group Totals") var range = sheet.getRange(rangeA1) var values = range.getValues() //get the count and limit values for the question's range for the user's choice for ( v in values){ var row = values[v] if (row[0] == their_choice){ return [row[1], row[2]] //the first value (row[0]) is their choice

// the second value (row[1]) is the count 

// the third value (row[2]) is the limit } } } function test_find_limits(){ Logger.log( find_limits("B3:E23", "11:45-12:00")) }

and

function check_availability(item_id, count, limit, their_choice){ var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet = ss.getSheetByName("Group Totals") var form_id = "YOUR_FORM_ID_HERE" //the form ID is from the url. var form = FormApp.openById(form_id) var items = form.getItems() var item = form.getItemById(item_id).asListItem() var choices = item.getChoices() //pulls out the multiple choice question choices.  

//Can't pull out one particular choice so pulls them all out and iterates var choices_list = [ ] for (c in choices){ var choice = choices[c] if (choice.getValue() == their_choice){ if (count >= limit){ //It's up to the limit! choices_list.push(their_choice + " FULL!" ) 

}else{ choices_list.push(choices[c].getValue()) } }else{ choices_list.push(choices[c].getValue() ) } } item.setChoiceValues(choices_list) }

So, basically, when someone books an appointment, the script looks in the "Group Totals" sheet, and if the count is equal to the limited number of places, it changes the multiple choice items title to "10:00 - 10:30 FULL!".

What's interesting about this is that although you can't do live changes to the form, this script essentially changes the form items for the next person who uses it.

I did experiment with deleting the multiple choice item, but had a few funny results, so thought it best to just change its name. This can be a good idea anyway, to show to users that slots did exist but now they're gone.

Of course in this case someone can still book a full slot ( it doesn't prevent it) , but this process is, in our case, policed by a human anyway. This method is a way to heavily dissuade people from selecting full course slots.

Hope this helps.

By: Unknown On 3:02 AM

Continue Reading

From Survey To Google Spreadsheet To Google Document

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)
    
    p.setHeading( DocumentApp.ParagraphHeading.HEADING1 )
    
    for (i = 0; i < values.length; i++){
      var row = values[i]
      var value = row[h]
      var p = body.appendParagraph(value).setHeading( DocumentApp.ParagraphHeading.NORMAL )
      body.appendHorizontalRule()
      
    }
  body.appendPageBreak()
  }
  doc.saveAndClose()
}

function onOpen(T) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Export to Google Document", functionName: "document_from_spreadsheet"},
                     ];
  ss.addMenu("Admin", menuEntries);
}
  

There was a little cleaning up to do, to remove any choice-based or numerical data items ( we could of course paste those in as images ) but this code was all we need to start easier on the eye and brain analysis of the responses.

By: Unknown On 4:46 AM

Continue Reading

Strange Problem With Older Google Forms

I've had two people complaining about this this week.

If you have a Google Form in a spreadsheet that was made a while ago, you are still given the old form editing interface.

There doesn't seem to be a way to bring a Google Form up-to-date, which is a big pain if your form is very long and complex since the only way to do so is to just start all over again.

By: Unknown On 3:08 AM

Continue Reading

Using Google Docs To Make Google Spreadsheets Easier to Read

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 course do this by hand, but when your forms get very complex, or your headers are very long, it's easy to make mistakes.

This code adds a "Show Tags" menu to document, which, in a sidebar shows a list of the spreadsheets header names which can be inserted into the document.


function onOpen() {
  var menu = DocumentApp.getUi().createMenu('Tags')
  menu.addItem("Show tags..." ,"show_tags" )
  menu.addToUi();
}

function get_ss_headers(){
  var ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID')
  var sheet = ss.getSheets()[0] // Get the first one
  var range = sheet.getRange(1, 1, 1, sheet.getLastColumn() )
  var values = range.getValues()[0]
  return values
}

function show_tags(){
  var headers = get_ss_headers( )
  var app = UiApp.createApplication().setTitle("Insert Tags")
  var panel = app.createVerticalPanel();
  var list_box  = app.createListBox(true).setId('list_box').setName('list_box').setWidth(240)
  list_box.setVisibleItemCount(10 )

  for ( h in headers){
    var header = headers[h]
    list_box.addItem(header).setValue(Number(h), header)
   
  }
  panel.add( list_box)

  var handler = app.createServerHandler('insert_tag').addCallbackElement(list_box)
  var button = app.createButton('Insert!' ).setId('button').addClickHandler(handler)
  panel.add( button)
  app.add(panel)
  DocumentApp.getUi().showSidebar(app)

}

function insert_tag(e){
   var app = UiApp.getActiveApplication()
   var list_box = e.parameter.list_box
   var tag = "{" + list_box + "}"
   var doc = DocumentApp.getActiveDocument();
   var cursor  = doc.getCursor()
   cursor.insertText(tag)


}

Copy and paste this code into your document, changing the spreadsheet ID, then run onOpen(). It will ask for authorisation, then the menu will appear, like this.

Once you've added all your fields, you need to first, create a Google Folder and note the ID of it ( you can see it in the URL ) and then add some code to the spreadsheet to render a spreadsheet row into a Google Doc. ( Caveat: This does assume that your header names are unique - with one particularly complex form with multiple pages and stages, we titled questions as a.name, a.institution and b.name, b.institution and so on. )

Go to your spreadsheet and add this code via Tools > Script Editor...


function create_google_doc() {

  var ss = SpreadsheetApp.openById('YOUR_SPREADHEET_ID')
  // Logger.log( ss.getName())
  var sheet = ss.getSheetByName("Form Responses")
  var row = SpreadsheetApp.getActiveRange().getRow()

  //get headers
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0]
  var range = sheet.getRange(row, 1, 1, sheet.getLastColumn())
  var values = range.getValues()[0]

  //Build a dict
  var tags = {}
  for (h in headers){
    var header = headers[h]
    tags[header] = values[h]
  }
   
  try{
    //Get some hard-wired values ( CHANGE THIS FOR YOUR NEEDS )We need some data to name the file
    var student_name = values[2] + " " + values[1]
    var student_email = values[4]
   
    //Make a Google Doc
    var new_doc_title =  student_name + " - Registration Form" // CHANGE THIS TOO.
    var template_id = 'YOUR_GOOGLE_DOC_TEMPLATE_ID' // The ID of your template file
    var template_doc = DocsList.getFileById(template_id)
    var new_doc_id = template_doc.makeCopy(new_doc_title).getId()
   
   // Move new document
    var destination_folder = DocsList.getFolderById('YOUR_FOLDER_ID')
    var doc = DocsList.getFileById(new_doc_id)  //Move to destination folder
    doc.addToFolder(destination_folder)
   
   
    var new_doc = DocumentApp.openById( new_doc_id )
    //Render the values into the doc
    var s = ''
    for ( var t in tags) {
     
      var tag = "{" + t + "}"
      var value = tags[t]
      s+=  tag + " " + value + "\r" // Just for debugging
      new_doc.replaceText(tag, value )
    }
    Logger.log(s)
    //Replace any unreplaced tags for tidiness
    new_doc.replaceText("\{.*?\}", "" )
   
    //Share it to the student, optional
    //new_doc.addViewer(student_email)
 
    //Add URL to the Spreadsheet
    var url = new_doc.getUrl()
    var range = sheet.getRange(row, sheet.getLastColumn()+1 ).setValue(url)
    Browser.msgBox("Document created for '" + student_name + "'  in folder 'Wherever'")
    return new_doc
   
  }catch(e){
    Logger.log( e)
  }

}

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var menuEntries = [ {name: "Create Google Doc", functionName: "create_google_doc"}                                      ]
  ss.addMenu("Admin", menuEntries)
}

function url_escape(s){
  var s = encodeURIComponent(s)
  return s
}


Lastly make sure that anyone generating a Google Doc has access to the Template Document, otherwise the code won't work ( they only need View access ).

And there you have it, we use these sorts of scripts for all sorts of occasions where reading form submissions in the spreadsheet isn't appropriate. We've even added code that adds to bottom of the document, a prepopulated URL that examiners can click to complete a form for marking that document, with the student's name and other details already filled in.

By: Unknown On 4:34 AM

Continue Reading

Linking a Google Doc To a Form For Assessment

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 changing for your values and email.



function make_prefilled_url ( values ){
  var title = values['Title'][0]
  var firstname = values['First Names'][0]
  var surname = values['Surname'][0] 
  var institution = values['Institution'][0]
  var department = values['Department/School'][0]  
  var mode = values['Mode of Study'][0]
  var university_id = values['University ID Number'][0]
  var condition = values['Has an offer of a place of study already been received'][0]
  var project_title = values['Project Title'][0]
  var project_summary = values['Project Summary'][0]
  var url = 'https://LINK_TO_YOUR_EVALUATION_FORM/viewform?'
  
  url+= "entry.1981746791="+ url_escape (title )
  url+= "&entry.522456082="+ url_escape( firstname)
  url+= "&entry.1635227300="+ url_escape( surname )
  url+= "&entry.1575537957="+ url_escape (institution )
  url+= "&entry.1021949580="+ url_escape( department   )
  url+= "&entry.1125873153="+ mode 
  url+= "&entry.1336223027="+ url_escape( university_id )
  url+= "&entry.582559888="+ condition 
  url+= "&entry.303787572="+ url_escape( project_title )
  //url+= "&entry.1796510964="+ url_escape( project_summary )
  
  return url 
}

function url_escape(s){
  var s = encodeURIComponent(s)
  return s 
}

Once you've worked out how to create a pre-filled URL, you can then go back to the code that generates the Google Doc ( the application form ) and make sure that each document has a link to the evaluation form.

This shows you how to add a link to a Google Doc.


function onFormSubmit(e){ 
  // Get values
  var values = e.namedValues 


<<< Your other code here to generate your Google Doc >>



   //Append an pre-populated form URL to the new document 
    var eval_url = make_prefilled_url(values)    
    var link_text = "To evaluate this application, click here"
    var par = new_doc.getBody().appendParagraph(link_text)
    par.editAsText().setLinkUrl(0, link_text.length -1 , eval_url)



It's difficult to give you copy-and-pastable code to do this because it's a bit messy, but workable enough and once you understand the concepts, it allows you to easily chain forms and processes together, making a much smoother experience for everyone involved.

By: Unknown On 4:34 AM

Continue Reading