Skip to main content

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.


  1. The stuff written in the blogs have allured me!!! nottingham accommodation

  2. I am very much pleased with the contents you have mentioned. I wanted to thank you for this great article.
    Newcastle student accommodation

  3. there have very important Google Spreadsheets To Improve Student Accommodation. usually i would be try for this and make my skills too useful accommodation. thanks for bring with us.

    Luxury Cheapest Student Accommodation In Chester | Student Accommodation Chester

  4. It is something that we have to use now to find a student residence in Madrid to do it easyly because now we will have more problems leaving EU so... thanks for share

  5. Exciting to study this writing piece! You put up an amazing type research and cooperate with your readers. Thanks much database design services

  6. Strategies on how to write a medical school personal statement that will make you stand out from the crowded field of applicants and get you admitted to medical school. A preview: Focusing on your passions and motivations, rather than your "me-too" activities, is the best way to create a persuasive personal statement. physics problem solver

  7. So, what's an honest program spec? something that eliminates the approximation for the engineer. contemplate this: if the up-front system style work was done right, programming ought to be but V-J Day of the whole development method. scheme programming help

  8. Writing a graduate school personal statement can be a daunting and difficult task. You are selling yourself and trying to let the admissions representatives know WHY you should be chosen over other qualified applicants. thermochemistry homework help.

  9. Great article! I loved the insight and advice given. Further, your blogging style is very fun to read. If you have enough time please explored my brand new blog and let me know what you think. To know more

  10. Finding an apartment is often more complex than for student residences. Personally, I was not at all interested in looking for an apartment, and focused on the student residences directly. But, for you, I went to ask some friends who chose to rent an apartment.

    Best student accommodation in Sheffield


Post a Comment

Popular posts from this blog

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems. A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it. The QR app looks like this. The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this. The Web Application The web application, which only checks items in or out and should be used on a phone in conjunctio

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

A Working Booking System In Google Sheets

Working with Andras Sztrokay we had another go at a booking system. This time it was to enable staff to book out a number of iPads over a number of days. You select the days you want, then select the Booking menu. Andras did an amazing job. It even creates a daily bookings sheet so you can see who has which iPads. To see this in action, go  here  and  File > Make a Copy (I won't be able to support you this is just provided to maybe give someone else a leg up, good luck!)