Using Google Spreadsheets To Improve Student Accommodation

The Problem

Tim Saunders, is fast becoming the poster boy for my belief that getting non-technical people coding is good idea.

Tim started in the University of York accommodation office and inherited a task of managing students' requests to change their room.

This room changing process was paper-based and requires various peoples' agreement and signatures. It resulted in a student having to carry an increasingly dog-eared form to college administrators and back to Tim and then to the old college administrators. It was slow, actively encouraged signature forging and reliably error prone.

All the data collected then needed to be entered into SITS, our student database, which involves various charging and set up costs, so it really helps if this data is correct, having being verified by everyone in the chain.

The Solution

Armed with a some self-taught Javascript using the online learning platform CodeAcademy, Tim thought that the pile of paper forms in his office and queue of frustrated students could be improved with some Google applications.

He used a combination of tools to help manage the flow of information, including Google Forms, Spreadsheets and Apps Script to automatically send emails to college administrators and students.

The (massively simplified version of the ) process begins with a student filling out a Request to Transfer form ( shown below ).

The data collected from the form is added to spreadsheet (shown below) that has extra data integrated with it about the rooms features and contract details. An email is sent to the current college administrator that they can then approve or deny and then, once approved it is then sent to the prospective college administrator to confirm availability.

In the spreadsheet above you can see notes on when to "Check CA ( College Administrator ) and tools to fire off a particular email. The colouring of the spreadsheet is done automatically to help with navigating and understanding what the overall status of accommodation requests is.

Once agreed, students then receive an email in which an additional form is used to confirm their order. ( shown below ).

Once everything is agreed and confirmed, the data needed is sent to Tim in a format that means he doesn't have to enter it manually into our student database, SITS. A poor man's API if you will and still massively better than entering names, numbers and data by hand!


Interestingly, Tim and the Accommodation can now also for the first time, see the whole process from a bird's eye view by using the charting tools in the spreadsheet to see, from which college the most requests are being received, and when the most requests come in ( the start of term ).

One thing that I really like about Tim's implementation in this project is its lightweight approach. They didn't sit back and design a whole complicated web application that ultimately wouldn't have fit the many edge cases and workarounds in these sorts of scenarios. I like how the system primarily uses email, something College Administrators are comfortable with, and more importantly, remember to do.

Now that Tim has been promoted, the next real challenge for Accommodation ( and Tim ) is to find the best way to ensure that all this work is usable and maintainable by the Accommodation team. I know they're doing all they can to make sure that it is well documented and as collaboration-friendly as possible. They're even considering screencasts to help explain how the pieces fit together.


Tim Saunders, (like Paul Bushell in Estates with his Dashboard system ) has shown that anyone who wants to can take control of both messy processes and code to make life better, not just for themselves ( even though that would be justification enough ) but for students and colleague too. What was a slow, arduous, error prone process is now elegantly handled in a few Google Spreadsheets and Google Forms.