Teaching & Learning Portal


Using Google web forms and scripts to automate the workload

6 August 2013

The Department of Chemistry's Dr Andrew Wills explains the automated systems he's set up in order to ease the administrative workload.


How it works

Many tasks require the collection of information from staff, such as the logging of teaching cover during periods of absence and peer observation of teaching (POT) events for the internal quality review (IQR). In Chemistry we have been trying to automate some of the associated workload. We are also introducing systems to facilitate certain aspects of teaching. 

These systems run through Google. The simplest to set up are web forms. We have one for logging teaching cover when staff are absent. We need this information and line managers check against it before approving absences. When the staff complete the web form, which is structured to ensure that they have considered all of their teaching responsibilities, the information automatically goes into a spreadsheet and a summary is emailed to the line manager. There are no emails being sent back and forth to make sure that we have all of the data, which saves staff time, and line managers are given the information they need to manage their sections.

Moving beyond web forms, which are very easy to create, there are also scripts that run within Google. These can put information directly into spreadsheets or process data which is submitted together with data that is already in the spreadsheet. We use these to manage the peer observation of teaching (POT) process, which forms part of our monitoring of teaching quality. The spreadsheets contain general information about all of our teaching staff, such as full names, email addresses and sections. When the staff are planning a POT event, we can use this information to save them from having to type in lots of things that we already know; this means the forms are easy to use and save staff time. The script keeps an eye on the data and sends out an automatic email after the event was scheduled. This contains a link to another form where the staff confirm a summary of the event details and that the event took place. The information then goes straight into the spreadsheet which in turn goes to the faculty office. 

T his type of simplified collection and processing of information has obvious uses for teaching, and we are developing ways of using it that will speed up the feedback process. It can also help us manage more generally the student learning experience. An example of the type of things that concern us is preventing clashes and managing the student workload. To help this we log when coursework is set and due. The information goes automatically into Gantt plots, meaning that staff can quickly see what coursework the students are already doing. If the students have a lot on their plates, they can change when they set coursework or push back the submission deadline.

All of this has the general goals of cutting admin jobs, reducing email traffic, saving time, and improving the quality of the information that we have. I've created a video showing how to create Google web forms, which you can view by clicking the link below.

Further information