Thursday, 10 October 2013

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.

Wednesday, 9 October 2013

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.

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" )

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)


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()


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.institution and, 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]
    //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
    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 )
    //Replace any unreplaced tags for tidiness
    new_doc.replaceText("\{.*?\}", "" )
    //Share it to the student, optional
    //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
    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.