Use Google Forms and Sheets to Track Intervention Students and More

 

A common pain point among educators is having to transpose grades, data, etc. from paper or a program to another program. One thing we have started doing is electronically submitting records of student intervention. We have the autonomy to track and record student intervention interactions with our own preferred methods. For the most part, I have seen teachers write down their intervention on a paper chart. When we were turning in hard copies, this method is fine, but now we're submitting electronically. 

One solution would be to scan intervention forms, but that can be tedious and time consuming. Using Google Forms in concert with CONCATENATE in Google Sheets or Microsoft Excel, you can track intervention data, and much more, digitally and efficiently. I am by no means an expert with spreadsheets. My skill level is rather rudimentary, but this trick has made my workflow much better and saved me hours of tedious work.

In the GIF below, you see a sample of a form similar to what I use to track intervention with students. Setting the form is not difficult. The tedious, difficult part is getting all the student names into the form.

Using your school's student data system (We currently use Illuminate), you can download spreadsheets with your class rosters. Below is a sample of what these spreadsheets can look like. Using CONCATENATE in Google Sheets or Microsoft Excel, you can combine student last names, first names, and a comma in between, into a list that is EASILY copied and pasted into a Google Form.

According to edu.gcfglobal.org, the CONCATENATE function allows you to combine text from different cells into one cell. In our example, we can use it to combine the text in one column and another column  to create a combined name in a new column. 

Get started using CONCATENATE to combine last name and first name with a comma and space in between. In the screenshot below, I used an empty cell in the first row of data. Start by typing the "equal" sign. From there, enter the first cell you want to combine. In this case, it was "B2" for the last name Banner. After typing B2, with no spaces, type an ampersand. 

To add a comma after the last name (Banner), put a quotation mark, comma and another quotation mark. Remember, no spaces. To add a space after the comma, type an ampersand, quotation mark, hit the spacebar and put another quotation mark. To add the first name (Bruce), type an ampersand and the cell for Bruce (C2). Push the enter button when done. Typing the cells is not case sensitive. The formula used to combine the cells creating Banner, Bruce was =B2&","&" "&C2.

The beauty of this is that you only have to type the formula once. Once the last and first name is combined, click in that cell and you'll see a blue box around it. In the bottom left corner of the cell, you will see a blue square. Click and drag the square down to "fill down" and apply the formula to all of the rows below. This will combine last and first names for all students in the spreadsheet.

The GIF below shows CONCATENATE in action.

Select and copy all of the combined last and first names. 

With all of the students' names copied, create a Google Form. Set up a question for student names. Paste the names as the options for either Multiple Choice or Checkboxes. If you teach multiple classes, you will want to create a question for each class. Paste the student list into "Option 1" and it will add all student names to the question.

With my student rosters easily put into Google Form, without typing one by one, there are many ways to use them. I use them for tracking intervention, phone calls to absent students, rubrics and more. Click here to view a sample of Form I use when teaching multiple classes. How might you use a Form like this?

If you have any questions and would like a follow up, contact me via Gmail or Hangouts at ajuarez@techcoachjuarez.com. 

My book, The Complete EdTech Coach: An Organic Approach to Digital Learning, co-authored with my wife Katherine Goyette is now available on Amazon. Click here to purchase. It is published by Dave Burgess Publishing. Be sure to follow the hashtag #OrganicEdTech and #CVTechTalk for updates.