Another post related to my ‘Hacking stuff together with Google Spreadsheets’ session at Dev8eD (A free event for building, sharing and learning cool stuff in educational technology for learning and teaching!!) next week. In this example Google Apps Script is used to create a custom user interface that can be used in Google Spreadsheets, allowing tutors to enter feedback and grades based on individual assessment criteria pulled from a central data source (another Google Spreadsheet). The system then generates personalised feedback forms (Google Documents) based on the data and distributes them to students.
As part of my work outwith JISC CETIS I have been helping staff at Loughborough College with their LSIS funded Fast Tracking Feedback project. As part of this project I’ve helping staff create a system that standardises and speeds up the return of assignment feedback to students. This project has generated a number of outputs for the wider community including training material and some code snippets (sending free SMS | generating Google Documents from Spreadsheets).
As my official involvement in the project comes to the close there is another chunk of code and resources to push out in the wild. It’s complete copy of the beta system currently being piloted with staff at Loughborough College. If you want to get an idea of how it works here’s a short video demonstrating the system I did as a lightning talk at GEUG12.
If you want to pick over the code for this I’ve dumped a copy in github. This is more for reference as the code makes use of the Apps Script GUI Builder for parts of the interface, which can’t be extracted from the Spreadsheet. For a functional version you’ll need to make a copy of the four documents linked to below (this is followed by some instructions on setup and usage). I should also point out that this system has been build around the British BTEC qualifications. An example of the assessment and grading criteria is on page 3 of this document. Hopefully there is enough reusable code for other qualification systems.
Files you’ll need
- Master Spreadsheet – this is the main document with all the Apps Script in it.
- Master Template (you’ll need to File > Make a copy) – this is a Google Document used as a template for the form
- Criteria Sheet – spreadsheet of units and courses with the related assessment criteria/rubric
- Student Lookup – spreadsheet of student fullnames and related Google Apps ids (used because the App Script Group Services can only return ids and email addresses)
The basic setup
- Place all four copied files into a folder in Google Docs (you can name the folder anything you like).
- Change the share setting on the folder so that either ‘Anyone with the link’ or ‘People at your Google Apps domain with the link’ can view.
- Open your copy of the Master Spreadsheet and open Tools > Script Editor…
- On lines 17-19 copy and paste the document id/keys for Master Template, Criteria Sheet and Student Lookup. You can get these by opening the documents and looking at the browser url for the highlighted bits.
- From the Script Editor you can also open File > Build a user interface… and then open the importStudentList and click on the ‘Enter the group …’ label to edit the text in the property pane on the right-hand-side and then similarly for the textfield beneath it. There’s also the option to customise/add logos to the entryForm GUI
- In the Criteria Sheet create your list of units/courses and associated assessment criteria
- In the Student Lookup sheet import a list of Google Apps Ids and names
There are additional options in the code to change folder names, pass, merit, distinction colouring.
That’s it. Enjoy and any of your thoughts are welcomed in the comments (I can’t make any guarantees to respond to all)
The Fast Tracking Feedback project is funded by the UK’s LSIS Leadership in Technology (LIT) grant scheme.