A simple sign-out system using Google Forms

Google forms have a wide range of applications in schools as documented previously in my blog, but in a wide range of other places. Much of the use of Google forms is very powerful, particularly when used with scripts such as formMule, Autocrat etc.

However, I was looking at creating a sign-out system for boarding students recently, and stumbled on a very simple way to record student sign-outs using Google forms.

What you need

All students need to have a Google account and have access to a mobile device (either their own or school provided) and wifi or cellular data. There are ways around them not having a device, but it works best if we use their Google account details.

You will also need a spreadsheet list of usernames matched to the student name.

The form

All the form needs is a submit button - no questions at all. It also needs to ensure that they are signed in to your domain and their sign-in details are recorded.

The student must be signed in and their username must be collected

The completed form will look like this:

The spreadsheet

The spreadsheet that pulls the form data will need an additional sheet to analyse the responses. The only thing initially needed on this sheet is a list of students and their usernames.

The function

Next to the student name column is the formula that calculates the number of times the username appears and registers whether a student is in or out. This formula can appear on a different sheet if needed. The formula is relatively simple. 

It is a formula that uses the IF function,  isodd function in combination with countif to find the number of times the username appears in the list. The function looks like this:

To clarify, if there are:

• No entries (i.e. the form has never been filled in), the value will be even and display "In"
• 1 entry - odd - displays "Out"
• 2 entries - hopefully you get it by now!

The system in action with added timestamp

For those interested in the system, a copy of the spreadsheet is here (view only), including the timestamp function/formula shown in the example above.

Extra stuff

Once this is running, you can add extra features such as:

1. Use conditional formatting to colour code in/out
2. Format the sheet for display on an LCD display etc.
3. Using a function to timestamp the sign-in / sign-out time of the student (this data can be pulled from the Form responses sheet) - see the link above
4. Add location information (an additional layer of complexity that will require questions on the form) - i.e. allow user to choose a place that they are going from a dropdown.