A technical challenge for a Friday afternoon
We have had an interesting technical challenge posed by Chris Rusbridge, Director of the DCC relating to marking JISC bids and wondered if any of you clever people out there could come up with a solution.
For the jiscri call we received 94 bids each of these has to be marked by 3 markers, the marking process has approximately 5 sections with comments. That is a lot of data to process so an online submission system is essential. We use a web form for this. However, lots of our markers are mobile and entering marks directly into the web form is not convenient so the markers prepare their marks and comments offline and then add them to the web form at a later date. JISC provides a standard spreadsheet to help people make notes.
Copying comments and marks from the cells in the spreadsheet into the individual boxes on the web form is a time consuming and dull task and Chris is keen to find an automated way to do this.
I had a look at this myself last night and got halfway to a quick and dirty solution. Since the webform doesn’t have an API, I figured the easiest way to speed things up will be to make the pasting process easier. This can be done with enhanced clipboard systems such as ditto or clipdiary. However, getting the individual cells from the spreadsheet as unique entries on the clipboard in the correct order is a problem I couldn’t solve.
So over to you. Is there a way to use the clipboard solution to make Chris’ task easier or is there an altogether more elegant solution? Chris’ deadline is 10am Monday 11th May so answers before then would make Chris happy.
Here is a dummy example of the webform (it is from a completed marking process): http://survey.jisc.ac.uk/einfevaluation
I have put an example spreadsheet on google docs the text in the yellow boxes is what needs to be copied.
The way in which JISC bids are managed and marked is under review in the JISC policy department and they are looking for a manageable yet user friendly approach. This challenge is not part of that process so we are not looking for suggestions for different marking systems here, it is purely a technical challenge that happens to be related to marking.
We may be able to send a small gift to the most elegant solution but surely making Chris happy is the main prize here.
Comments
8 Responses to “A technical challenge for a Friday afternoon”
Leave a Reply
You are trying to collect lots of semi-structured data from lots of people in an efficient manner so as to maximise later use.
This is clearly a repository problem!
What you do is define a new deposit type (project review) and make the marking scheme a part of the JISC repository metadata schema. Then you use the EPrints Excel importer to ingest the reviewer’s spreadsheet, and use the editing workflow to allow the user to review and edit their submission.
Easy peasy. That’s what repositories are for.
You are trying to collect data from a spreadsheet to insert into a database?
Don’t make the user do anything at all!
There are APIs for handling Excel spreadsheets (e.g. Perl as well as the usual Microsoft .NET interfaces). Just provide a suitable spreadsheet template and then read the data from the resulting spreadsheets (which could be emailed for maximum convenience).
You are trying to make lifeeasier for your users without actually having to do any development yourselves?
Why not just recommend the use of a clipboard manager, like “Clipboard Evolved” for the Mac (http://www.youtube.com/watch?v=gYgMcOB8QtA). That will make it much easier to gather all the information from the spreadsheet app in one go, and then paste it into the web form without having to keep switching between apps and scrolling around.
You are trying to manipulate spreadsheet data by combining it into one or more summary spreadsheets?
Don’t do any processing at all! Dispense with cumbersome web interface. Trash the database. Do it all in a spreadsheet.
Just line up all your reviewers submitted spreadsheets onto your file system with appropriate file names. Then one or more master spreadsheets (ubersheets if you will) will automatically read the contents of the individual reviews using appropriate external data references in the cell formulas.
It’s just a simple use of spreadsheets.
I’m online but using the excel spreadsheet because it ensures i have all my answers lined up and can compare/check things once all entered. So this would be useful!
Chris
xNeat seems to work well in terms of a copy and paste solution for the PC. Also freeware. See http://bit.ly/LwE0 for download.
I had tried some similar tools to xneat, James but the problem I came up against was that I couldn’t find a way to bulk copy to the clipboard, each cell had to be copied individually. Did you solve that?
Adobe Acrobat can be set up to handle online reviews of documents using a ‘comments repository’. Any number of reviewers can comment offline and then upload the comments when back on line (I believe this uses WebDAV techology). More info at http://www.adobe.com/devnet/acrobat/online_review_admin.html.