Pages

Wednesday, 30 January 2013

Using Google Spreadsheets to Record Chemistry Experiment Marks

Each year, around 200 chemistry students perform 20 Lab Experiments ( that’s roughly 4,000 a year ). Each test has a variety of marks to be kept by at least three people, the lab technician ( did they attend?), the tutor ( did they create the right chemistry and hand in their notes?) and the course leader ( are there any exceptions or mitigating circumstances etc).

What was previously a paper-based method had recently been made to work in our VLE, but the data captured was in a cumbersome wiki text format. And whilst the user interface was simple enough, the technology was struggling and getting the data collected from the VLE into our marks database required considerable human effort.

Working with David Pugh in Chemistry, we looked at using a Google Spreadsheets to collect the experiment data instead. After a few prototypes we have decided to use a very simple ( but quite wide ) spreadsheet to store the data and a web application “front end” for the markers to enter their marks.  I have worked with David, consulting about his requirements and have created him some Apps Script code. David is now editing the code, learning all about Apps Script and fine-tuning it to his needs. The ability to share small IT projects “in the cloud” using Apps Script is really empowering, for both David and I.

Whilst this project may at first glance seem a shade niche, but I often come across similar situations where technology has evolved and grown in the cracks between bigger systems.  The two systems here might be said to be “teaching” and the marks database ( SITS ).

It’s usually the case that these situations that the process ( or technology) requires a lot of upkeep and human input and that they don’t easily offer up accidental benefits, or usage that wasn’t envisaged when the original project was started. Now that we are taking control of our data in the “in between” stage, everyone is starting to see further possibilities of where this project might go next.

Implementation

After creating a prototype with the UI Builder, we decided that maybe a web application would be the best way forward. Both David and I are comfortable with simple HTML and we had an idea that we might need to use some of the excellent UI features of jQuery at some point.



Our web application had a very simple collection of screens, the Home Page (shown above) which leads onto a listing of students (not shown), each linked to a form with which markers could add the relevant student marks ( shown below ).




All the data is stored in a ridiculously simple spreadsheet. This was David's idea and significantly improved on my original design just because it essentially has one row per student, which hopefully will make later reporting or visualisation needs a breeze.


Specific tips/code/ideas that you can reuse


Keeping Your Code Tidy with a Single CSS file

In an attempt to keep our application tidy, we added this function and a file called css.html. The css.html file actually contains its own <style> tag.

function getCSS(){
 var template = HtmlService.createTemplateFromFile('css.html');
 return template.getRawContent()
}

What this means is that our four or five templates all begin with code like this and we only have one CSS file shared between them all. The jquery libraries were commented out but ready to be added back in should we need them. This made our templates cleaner and much easier to maintain.

<head>
<link type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.23/themes/smoothness/jquery-ui.css" rel="Stylesheet" />
   <!--script type="text/javascript" src="http://code.jquery.com/jquery-1.7.2.min.js"></script-->
   <!--script type="text/javascript" src="http://code.jquery.com/ui/1.8.23/jquery-ui.min.js"></script-->
   <?!= getCSS() ?>
 </head>

Note the ! in the <?!= getCSS()?> It’s easy to miss. The exclamation means actually display the code contained and don’t escape it. 




Using Script Properties to store Spreadsheet IDs


We also found that adding a spreadsheet id to the Script Properties made it easier to maintain our code, with it appearing only in one place, rather than being repeatedly repeated.

function get_ss_id() {
 // Gets a spreadsheet ID for this project from File > Project properties.
 // See: https://developers.google.com/apps-script/class_scriptproperties#setProperty
 return ScriptProperties.getProperty('spreadsheet_id');
}

We can then use a line like this, rather than adding the spreadsheet id each time.

var ss = SpreadsheetApp.openById( get_ss_id() )


General and Probably Obvious Tips


It quickly became clear that we should keep spreadsheet code and web application code in separate files. We didn’t realise that, because of security reasons that I don't really understand, Apps Script can’t REDIRECT a HTTP request, which is an unusual limitation. 

Also, because you also don’t have any control over your application’s URLs we found that our doGet() function behaves a little like a controller in an MVC sense and so keeping that code free of functions that work with the data ( the model ) made it much easier to read and maintain.

Always write tests! Almost every function we wrote has a test function created for it just to check that it is working properly. This makes the use of the debugger and logger much more productive.


Rolling Your Own Security

I was quite surprised by the “all or nothing” security model with Googe Web Apps which seems a bit poor.  Unlike Google Apps where you can set the permission levels, adding people and groups, with Google Web Apps you can only choose ( Everyone, Everyone at York or Just Myself ) which seems a bit limited.

It was easy to create some code like that shown below but I was surprised that access controls to the web application couldn’t be set up in the familiar Sharing dialog.

function get_supervisors_emails(){
 
 var ss = SpreadsheetApp.openById('OUR_SUPERVISORS_SPREADSHEET_ID')
 var sheet = ss.getSheetByName("Supervisors");
 var values = sheet.getDataRange().getValues();
 supervisors = []
 for(i = 1; i < values.length ; i++){
   supervisor = values[i]
   email = supervisor[7]
   supervisors.push(email)
   
 }
 return supervisors
}


var user = Session.getUser( );
var email = user.getEmail( );
var supervisors = get_supervisors_emails()
 
 // Noddy security
 if ( supervisors.indexOf(email) == -1 ){
       var template = HtmlService.createTemplateFromFile('NotSupervisorError.html');
       // This is used by the "<?= action ?> tag in the template
       template.action = ScriptApp.getService().getUrl( );
       template.email = email
       return template.evaluate();
 }



Conclusions


The project is now at the point where David is learning how it works, asking questions and making changes. Like me, David isn’t a programmer but is comfortable with simple HTML and Javascript. The current stage will be all about making the application work as easily as possible for the markers but David already has his eye on the next developments.

For example, the department needs to gather attendance data for immigration compliance, they will be able to show a marker’s average mark, they will be able to show students “falling behind” and integrate all of this into a “Lab Experiments Dashboard” showing key data items as visualisations. Watch this space for these developments.

Looking back, maybe we should have used Fusion Tables instead of Google Spreadsheets because our spreadsheet has grown quite large. I don’t think we will bump up against the cell limits that Google Spreadsheets have but we may have a use for the SQL-like means of querying our data.

The key benefit of this project will be about a department taking control over their data and complex processes and making them less arduous. Less time will be spent moving data from one area to another, there will be fewer human errors, and the data collected will be more “audit-friendly” since we log who edits it. But the part of the story I find most interesting is the new opportunities to better understand their own data, and ultimately to provide a better service to students.





2 comments:

  1. Is there any possibility to access the code of your project? I'm developing a similar app and it would be a nice help!

    ReplyDelete
  2. Sure, but I'll need to clean out student data etc. I will do it asap send me a mail if I haven't by the end of the week tom.smith@york.ac.uk

    ReplyDelete