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" />
   
   
   <?!= 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.

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

This article looks at using Apps Script to add new features to a Google Spreadsheet. At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form. Spreadsheets can be better than Forms for data collection because: The spreadsheet data saves as you are editing. If you want to fill in half the data and come back later, your data will still be there. The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessary The commenting features are brilliant - especially the "Resolve" button in comments. One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often...

Infinito14 May 2013 at 13:33
Is there any possibility to access the code of your project? I'm developing a similar app and it would be a nice help!
Tom Smith14 May 2013 at 13:42
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
aliyaa23 September 2017 at 14:23
Our web development work is the highest rated of the services we provide. The above is as a result of operating using values like passion and creativity. Our creativity when it comes to web design in Boca Raton is driven by our expert designer’s quest for perfection. https://www.full-spectrummedia.com/services/web-designing-development

Collaborative Tools Project

Search This Blog