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 web application above is browsable here.

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 ])    
  }
     
  dataTable.build( );
  
  var chart = Charts.newTableChart()
      .setDimensions(1200, 500)
      .setDataTable(dataTable)  
      .setOption('allowHtml', true)
      
      .build();
  
  var name_of_callFilter = Charts.newStringFilter().setFilterColumnLabel("Name of Call")
        .setLabelStacking(Charts.Orientation.HORIZONTAL)
        .setLabel("Name of Call")
        .setRealtimeTrigger(true)
        .setCaseSensitive(false)
        .setMatchType(Charts.MatchType.ANY)
        .build()
  
  var departmentFilter = Charts.newCategoryFilter()
       .setFilterColumnLabel("Department")
       .setAllowMultiple(true)
       .setSortValues(true)
       .setLabelStacking(Charts.Orientation.VERTICAL)
       .setCaption('Department')
       .setSortValues(true)
       .build();
   
     var funderFilter = Charts.newCategoryFilter()
       .setFilterColumnLabel("Funder")
       .setAllowMultiple(true)
       .setSortValues(true)
       .setLabelStacking(Charts.Orientation.VERTICAL)
       .setCaption('Funder')
       .setSortValues(true)
       .build();
   
   
   var dashboard = Charts.newDashboardPanel()
       .setDataTable(dataTable)
       .bind(name_of_callFilter, chart)
       .bind(departmentFilter, chart)
       .bind(funderFilter, chart)
       .build();
   
   
   var app = UiApp.createApplication().setTitle("Research")
   var panel = app.createVerticalPanel().setSpacing(10)
   
   panel.add(name_of_callFilter).add(departmentFilter).add(funderFilter).add(chart );
   
   dashboard.add(panel)
   app.add(dashboard)

   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 )
   app.setWidth(1200)
   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)
  folder.addEditor(researcher_email)
  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.

By: Unknown On 6:54 AM

Continue Reading

Using Spreadsheets INSTEAD of Forms

Google Forms are a great way to quickly collect data into a spreadsheet but what if the data you are collecting is a bit too complex for a simple form to handle?

We've been experimenting with using a Google Spreadsheet, instead of a Form to gather information and finding that this approach has many advantages. We still use a Form to "initiate" the process, and the data gathered from the form is saved in a "central" spreadsheet.

When the form is submitted, the central spreadsheet makes a copy of a "template" spreadsheet. This spreadsheet is more "human readable" than a regular spreadsheet. When the form is submitted, the data is saved as normal, but it also fills in certain values in the copied template sheet, sets the right permissions and mails all the people who need to know about it a link to edit the template copy.

This template sheet has a "form-like" layout, including help ( shown at the right hand side ) and additional tools added with Apps Script. Tools include the ability to add a link to another Google Drive file. There are times when the data you want already exists ( as a document ) and you don't really want people to copy and paste that data into the form.

The copied template looks like this. Each of the fields has been sized ready to fit the data we expect. Some are even "colour bordered" to help different departments find their bits.

The "Central" Spreadsheet

The central spreadsheet, or the one that receives the input from the initial Form keeps a track of which files have been created, saving links to them in each row. It also has a "status" column that the copied spreadsheets know how to update as they get completed. This makes it really easy to add a simple "Status listing" web app to a Google Site ( shown below with dummy data ).

This means you can give nice "live" summaries to certain groups of people without scaring them with the prospect of looking a huge and hairy spreadsheet.

When Might I Use A Spreadsheet Instead of a Google Form?

If Your Data Will Be Very Textual and Complex

If you want to gather lots and lots of long bits of text, a Google Form might not be the best way for people to enter that data, mainly because a Form expects you add your data in one sitting. ( You can allow people to be able to edit their responses, but somehow this still doesn't feel right ).

We've found that when data is long and textual, even viewing it in a standard spreadsheet if difficult, and so have created lots of scripts to render someone's form data into a Google Document in a more readable format.

If Your Data Needs To Be Filled In Collaboratively

It's much easier to work on a shared spreadsheet that looks like a form than in a per-row spreadsheet.
Additional benefits of using spreadsheets, as opposed to Forms, include:

• all the changes are saved in the file's "Revision History". You can see who added or deleted what and when.
• All the edits are saved on-the-fly.
• You can easily lock down parts of the spreadsheet so that people don't accidentally change it.

The "Insert Comment" feature, with the ability to "+" add someone is fantastic. If a part of the input isn't clear to you, you can ask for help from someone and they get a link to come and comment on that part of the document.

If Your Data Is Modular

In our spreadsheet, we even have a template sheet that, using custom made menus can be used to make "more sheets like this one". This is great in those circumstances when you might want people to submit something like recipes, each with the same items ( such as ingredients, method and picture ) and you want to be able to allow them to add one, two or twenty recipes.

Conclusions

So far, we haven't used this in anger yet, but as an approach I really like it. It uses a combination of Google Apps and doesn't get too fancy. There is a huge temptation to make this sort of system do things that maybe don't need doing.

Keeping it simple has been the main design ethic.

Most of the things we thought we'd need in terms of functionalities have turned out to already exist ( such as "Named and Protected Ranges" ) in the tools themselves, OR they can be achieved by just agreeing to work a certain way ( no software creation required ).

One of the things I like about this approach is that at the end of the day, they are all just first class Google files that one could, if need be, keep updated by hand, but it makes the whole process easier by gently easing all the things you'd need to do by hand, sending emails, adding permissions, creating files, adding calendar entries etc.

I'll let you know how we get on.

By: Unknown On 5:40 AM

Continue Reading

Using Google Forms For Qualitative Research

This week, I saw presentations from students performing qualitative research in Archaeology. The focus of their projects was an Android/iPhone heritage app called York's Churches which has been developed to encourage people to explore the life and history of York city centre churches.

Go get the app for Android or iPhone yourself, it's lovely.

The students' projects involved a mixture of focus groups, ethnographic work and Google Forms with iPads to gather data. The projects were mainly looking at how they might better raise awareness of the application with tourists and what improvements mi

The Google Forms and iPads were used in various ways, including...

• As a data capture tool when surveying members of the public
• They were used to demo the application on the street
• The forms were used to ease the transcribing of data they recorded with pen and paper, which might be questions that they answered themselves ( for example, "Did they seem genuinely interested?" )
• The students also mailed the Google Forms surveys to Bed & Breakfast owners

The students' presentations were fun and showed how they'd mastered the tools. They'd used various charts to visualise their data and TagClouds for textual visualisations. They also started to realise that they wanted more sophisticated analytics and perhaps needed to explore more complex tools than Google Forms.

They also had a few good stories about the rambling lunacy of the general public. They learned a lot.

By: Unknown On 3:02 AM

Continue Reading

Moan: Google Refreshes Google Forms

Google's announcement that Google Forms have been refreshed was welcome, it's always encouraging when a company updates a core tool you and your colleagues regularly use.... like say, Blogger. For example. Ahem.

Ahem.

Anyway, watching the videos about what had changed I notice that they've added the ability to share editing/viewing forms with people. That's great but it's sort of what I come to expect from Google, that nice Share dialog in many ways IS Google Apps. It doesn't feel like an innovation, it feels like a neglected corner being given a spring clean.

The relationship between Forms and Spreadsheets has been altered. It's never been clear that when you create a Form a Spreadsheet will magically be created for the results and now you can have a Form that doesn't have an associated Spreadsheet. I'm not sure if they've made it clearer, just different. We'll see.

And the demo in the video above, of being able to copy a list of items into a multiple choice question is a feature that I bet there's been at least one request for ( I'm exaggerating of course ). Where did that feature come from except from the developer's own weird fantasies? Or am I being harsh?

Google Forms have has a CSS face lift, it looks like they'll look more inline with other core Google Apps which is a good thing. It looks like they have core features we expect from Google Apps ( like pretty nifty sharing permissions ).

Google Forms doesn't have the ability to insert pictures or movies yet? I wonder why not. This would be a complete no brainer and let people whip up their quizzes with picture rounds or super-lightweight training videos with questions.

The complete lack of theme editing ability is a worrying trend I'm coming to expect from Google. Like a Google Site theme, you can choose any theme as long as it is white, black or frankly, insultingly stupid. Being able to add a header to your Form would be handy... In fact, it'd be good in all sorts of Google Apps, if Google Apps organisations could do the equivalent of providing branded templates.

So, as I said, I'm always happy to see improvements, but when they're what we expect anyway, or what we never needed, or not what people have been begging for I wonder what's coming next?

By: Unknown On 7:20 AM

Continue Reading