Google Suite

With six teachers teaching more than six hundred students across fourteen ensembles, we needed a way to quickly collect, share, and analyze data regarding their performance. Because our district uses Google Suite for Education, we originally decided to use Google Drive and Google Classroom for our data collection process. Due to a push by our district to make secondary grade books standards-referenced, we have transitioned to using Infinite Campus for data collection. Read about it in the Grading section.

Google Forms and Google Sheets

The vast majority of our work occurred in a Google Form linked to a Google Sheet for responses. I have created a separate copy of these for you to utilize:

When creating a Google Form, there are several different options for the type of information you can collect. Our form works like this:

Student's Name - Short answer question. Required. Showing Description. This allows sorting in the Google Sheet in the same way Infinite Campus sorts students.
Grade and Band - Multiple choice. Required. Showing Description. This allows sorting in the Google Sheet in the same way Infinite Campus sorts students.
Rubric - Multiple choice grid. Required. Showing Description. A grid allows us to visually deal with several aspects of the rubric very quickly, rather than several different multiple choice questions. In editing mode, this will not appear the same way as it does in view mode.
Lesson Comments - Paragraph. Not required. Allows teachers to make any additional comments they would like.

To add a description or change other options for the questions, there is an icon of three vertical dots that appears below the question when in editing mode.

Data then gets dumped into a Google Sheet:

Test Rubric (Responses)

The first tab (Form Responses 1) is the master dumping ground for all of the submissions from the rubric forms. The first row represents the responses from the form submissions. The second row are labels for Awesome Table, which is detailed in our Responding section.

In previous years, we used the QUERY function in Google Sheets to create separate tabs for each band. Awesome Table has much more intuitive features for filtering data, and have transitioned to using that. If you liked the QUERY function, that information is available on our Data-Driven website. If you want more information about Awesome Table, check out the Responding section of this website.

We also applied some Conditional Formatting in the Google Sheet to provide some better visual representation of the data. If a student receives a 2 in any category, the cell is highlighted in orange. If a student receives a 1 in any category, the cell is highlighted in red. This is done by

In the Format menu select Conditional Formatting.
Click Add new rule.
Choose Single Color or Color Scale. We didn't like the subtlety of the Color Scale, so made separate rules for each color. If you use a color scale, you can use a single rule to cover the range of scores.
In Apply to range, either type the range of cells, or click the icon in the right-side of the box to allow you to highlight the cells you want.
Format cells if determines the rule for formatting the cell. In our case, it is Value is equal to and 1 or 2 appear in the box below. Similarly, we have a rule for Text is exactly "I" to catch incomplete work.
Formatting Style allows you to choose how you want the cell to be formatted if it fits the condition from Step 5.
Click Done.

Report abuse