Going Deeper with Google Forms: Sample Teacher Observation Form and Dashboard
Google Forms are incredible! There’s so many uses for them, and they make collecting information so easy. But collecting data, and displaying usable data can be two totally different things. Three things can help with this. Google’s built in response summaries are fantastic ways to chart a lot of data. The second option lives in the add-ons through the add-ons manager. Something like Flubaroo can help you manage grading pretty easily. But it’s the third option that really gets in there when you need a more granular approach, and that deals with working through the formulas and sheet setups manually. It’s something I highly suggest getting your feet wet with as it really does make just about anything possible. As an example, I’m linking to a walkthrough instrument that I built for our admins. It utilizes a simple form, and some custom formulas to display that data in a manner that fits what the admins are looking for. Lets dive in and look. I’ve stripped out personal info in the following copies.
Link to the form: https://docs.google.com/forms/d/1sXbuloGfZD0ztvM7stn8bTLTt8ErQVGJVFG6Pea681Y/viewform
Link to sheet: https://docs.google.com/spreadsheets/d/1-soReut26De92DS1SSIv9L6F9kYm0Iq-eAB2ehn6JcQ/edit?usp=sharing
Feel free to enter some dummy data into the form to see how it reflects in the dashboard. Or make a copy to tinker with on your own.
First let’s look at the makeup for this particular Google Sheet. There are 9 tabs.
Form Responses 1 – Where the form responses go. Simple enough.
Teacher, Grade, Subject, and Observer tabs- These are the actual Dashboards. They display the relevant data based on the topic of the tab name. For example, the Teacher tab allows you select a date range and pull in data for a specific teacher(or all teachers if you select the “*” in the drop down menu). The first three are similar in how they view the data. The Observer tab deals more with information relevant to what the admins might need to look at to plan their upcoming walkthroughs(How many they’ve done, How they’re spread over the dat, etc).
Intermediate Tables tabs – There’s four of them, and they work as a means of filtering the data by the criteria that you mark in the top rows of the dashboards themselves. For example, if on the teacher dashboard you select a date range for the entire month(cells A3 and B3) and select teacher 1(cell C3), the intermediate table uses those cells to filter only the relevant data from the Form Responses 1 sheet. Let’s dig a little deeper into these intermediate sheets formulas.
The good news is that there is really only one formula in these intermediate tables. It’s the formula that filters the response data and it looks like this.
=filter(‘Form Responses 1′!A2:P553,’Form Responses 1′!A2:A553>Grade!A3,’Form Responses 1’!A2:A553<Grade!B3 )
=filter is the function and it works by selecting a range to return. In this case it’s all of the columns in the Form Responses 1 tab. The rest of the formula uses criteria to decide which rows to return. In this case it’s looking for rows whose time stamp(in column A) is between the start date and end date marked in cells A3 and B3 on the Grade Tab. Done correctly, this will only display form responses for the specified data range.
The importance of these intermediate table shows when you start looking at the formulas in the dashboard tabs(grade, subject, teacher, observer). Once you’ve selected a date range in one of those dashboards, the formulas in those tabs crunch the numbers by summing the data or counting rows. For Example:
The student engagement numbers in the Teacher tab should show all of the engaged and disengaged students. We need to get the sum of the numbers of only the rows that belong to a particular teacher. The intermediate table has already sorted out our date range. We only need to use a formula like the following to sum only the numbers that belong to rows that contain a certain teacher’s name. To do this, we’ll use =sumif. This function returns a sum of numbers in rows that fit certain criteria.
=sumif(‘Teacher Intermediate Table’!D1:D500,C3,’Teacher Intermediate Table’!H1:H500)
Formula Explanation –
Teacher Intermediate Table’!D1:D500,C3 – Our criteria. We want only rows where the cell in the teacher columns matches the teacher we’ve selected in C3 of the teacher tab.
Teacher Intermediate Table’!H1:H500 – The column that we want to sum.
A similar approach can be taken if we only want to count individual rows(not add the numbers in them). For this we use the countif or countifs functions. Countif if we only want to use one criterion, countifs if we want multiple criteria. This is done for the data in the teacher engagement data on the dashboards.
=countifs(‘Teacher Intermediate Table’!D:D,$C$3,’Teacher Intermediate Table’!N:N,E14)
This counts rows from the intermediate table that 1)has the teacher listed in column D, and 2) has the word “active”(cell E14) in column N.
There’s really only a hand full of different functions used in the entire spreadsheet. Getting used to them, and knowing their pitfalls makes putting something like this together fairly straightforward. Once the data is in there, the charts and graphs make it super simple to display it in a way that is quick and more efficient than just data alone.