Thursday, 14 November 2013

Creating A Shared Logging System

This is an approach we've used and re-used a number of times. Imagine you want a group of people to share some information using a Google Form. But although you don't really want to share the spreadsheet of the collected data, you do want people to use a subset of it.

In this example, we will create a "Research Logger". Here's the first form, go fill it in.

The Confirmation Page of the Form has a link to a web application made earlier, like this.

The web application uses a Table Chart visualisation to show a subset of the data but has really nice filters so that you can drill down on the information. It looks like this.

The code to display a table like this is...

 function doGet(e) {    
  var spreadsheet_id = 'YOUR_SPREADSHEET_ID'
  var ss = SpreadsheetApp.openById(spreadsheet_id)
  var sheet = ss.getSheetByName("Form Responses")
  var last_row = sheet.getLastRow()
  var last_column = sheet.getLastColumn()
  var range = sheet.getRange(2, 1, last_row-1, last_column)
  var data = range.getValues( )
  var dataTable = Charts.newDataTable()      
        .addColumn(Charts.ColumnType.STRING, "Added by")     
        .addColumn(Charts.ColumnType.STRING, "Department")      
        .addColumn(Charts.ColumnType.STRING, "Researcher")  
        .addColumn(Charts.ColumnType.STRING, "Funder")  
        .addColumn(Charts.ColumnType.STRING, "Name of Call") 
        .addColumn(Charts.ColumnType.STRING, "URL") 
  for ( r in data){
    var row = data[r]

    var username = row[1]
    var department = row[2]
    var researcher_name = row[3]
    var researcher_email = row[4]
    var tags = row[5]
    var notes = row[6]
    var funder = row[7]
    var name_of_call = row[8]
    var deadline = row[9]
    var research_title = row[10]
    var folder_url = row[21]
    if ( researcher_email != '' & typeof researcher_email != 'undefined' ){
      researcher_name = '' + researcher_name + '' 
    folder_link = 'files'
    dataTable.addRow( [username, department, researcher_name, funder, name_of_call, folder_link ])    
  } );
  var chart = Charts.newTableChart()
      .setDimensions(1200, 500)
      .setOption('allowHtml', true)
  var name_of_callFilter = Charts.newStringFilter().setFilterColumnLabel("Name of Call")
        .setLabel("Name of Call")
  var departmentFilter = Charts.newCategoryFilter()
     var funderFilter = Charts.newCategoryFilter()
   var dashboard = Charts.newDashboardPanel()
       .bind(name_of_callFilter, chart)
       .bind(departmentFilter, chart)
       .bind(funderFilter, chart)
   var app = UiApp.createApplication().setTitle("Research")
   var panel = app.createVerticalPanel().setSpacing(10)
   panel.add(name_of_callFilter).add(departmentFilter).add(funderFilter).add(chart );

   var label = app.createLabel().setText("TOTAL: " + data.length + " research bid projects").setStyleAttribute("color", "#442233").setStyleAttribute("font-size", "18px")
   app.add( label )
   var link_to_form = app.createAnchor("Add a new Research bid", "https://LINK_TO_YOUR_FORM/viewform")
   link_to_form.setStyleAttribute("color", "blue").setStyleAttribute("font-size", "18px")
   app.add( link_to_form )
   return app

One added useful feature is that when someone submits the form, a GDrive folder is created for that item and they are added as an Editor to that folder. A link is then added to the table for ease of access.

The code automatically create a GDrive folder when the form is submitted is...

function onFormSubmit(e) {            
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Form Responses")
  var row = e.range.getRow() 
  var values = e.namedValues
  var department = values['Department']
  var researcher_name = values['Researcher name']
  var researcher_email = values['Researcher email']
  var name_of_call = values['Name of Call']
  var funder = values['Funder']
  var folder_title = department + " - " + researcher_name + " - " + funder + " - " + name_of_call
  var destination_folder = DriveApp.getFolderById('CHANGE_TO_YOUR_FOLDER_ID')
  var folder = destination_folder.createFolder(folder_title)
  var folder_url = folder.getUrl()

  sheet.getRange( row, 22).setValue( folder_url ) 

This collection of a Google Form, a Google Spreadsheet and a Web Application means that staff can easily add information and be able to easily browse the information other people have added. I think although calling a Table Chart a visualisation might be a bit grand, they are incredibly useful ways of presenting information in a navigable and filterable format.

No comments:

Post a Comment