Easy Google Sheets Tricks for Tracking Student Progress and Participation Points

 

Progress monitoring and feedback are essential components of the learning process. Managing these things can be time consuming and cumbersome. Below, take a look at two, easy ways to use Google Sheets to monitor student progress and track participation points as part of a robust feedback loop.

Participation Points and Feedback

Feedback can come in many forms. Traditionally, it comes in the form of grades and red marks on an assignment. This type of feedback is not often acted upon by students. This type of feedback is more like an autopsy. A robust feedback loop during the learning process, not afterwards, makes the teacher and student partners. 

Part of building this loop is creating a culture where students elicit feedback from the teacher, and not just the other way around. Students don't typically like to ask for feedback during the learning process.  To help create this culture of feedback, I like to offer students participation points for asking for feedback and acting upon it. For example, on a block schedule day, I have students work on collaboratively on a project or assignment. For the period, I set a goal of 8-10 participation points. To reach their goal, they need to ask for feedback and have me check their work. Each time they ask and act upon the feedback, I award points. Developing this system has made it a habit for students to elicit feedback. The end products they turn in are much higher quality as they've received feedback and caught mistakes during the process and not afterwards.

Awarding participation points can be done with paper and putting tally marks next to kids' names, but printing a roster each day is a tedious act I wanted to avoid. With Google Sheets, I developed a system where I can click on a student and click a button to add points to their total. I can also take points away if needed. Click here to make a copy of my template. Embedded within the template is a Google Apps Script that allows you to click the plus and minus buttons to award or takeaway points. The first time you use it, you will be prompted to sign in and allow permission for the script to run. 

This Sheet will work on a Chromebook (better if touchscreen), PC and Mac. On mobile devices, it will not work using the Google Sheets app. The app will not allow the script to run. If on a mobile device, it will work if run on iPad or iPhone through Safari. It will not work through the mobile version of Chrome. 

Take a look at the video below to see a short demonstration.

Track Student Progress Towards a Goal

As an educator, monitoring student progress is something we do daily. Doing so can be tedious. One way I track progress towards a learning goal is to use checkboxes in Google Sheets. I create a sheet for each class and put a row of checkboxes next to each kid's name. You can use this for monitoring steps towards finishing a project, books read, participation points and much more. Built in is a formula that tallies the total number of checkboxes checked for each student. Click here to make a copy of my template. Take a look at the video below to see a short demonstration.

Google Sheets is such a versatile tool for tracking and analyzing student data. You can actually be extremely creative with it. How might you use Google Sheets in your role? 

Instant Translation of Image Text with Yandex

Supporting the unique language needs of English learners is a common necessity amongst all educators. When you don't speak the home language of an English learner, this becomes even more difficult. Oftentimes, teachers in this situation need to find a staff member who can translate text. To help with this process, Yandex Translate can be a valuable tool. Yandex using OCR (Optical Character Recognition) technology to translate text in an image.

One of my favorite reading and text-marking strategies is Booksnaps. In a nutshell, Booksnaps happen when you curate images of text on Slides, PowerPoint or Seesaw and provide students with opportunities to digital mark the text, answer questions and focus their reading to look for themes and more. 

Getting images of text in English is no problem, but translating to another language is another issue. Yandex allows you to upload your English language images of text and it quickly reproduces the same text in almost any language. The translated image of text is downloaded and imported to any activity you are trying to create.

Below is an example of how I use Yandex to create a Spanish translation of Booksnaps. Circled below is an image of text. This image was imported to a slide from my computer. Because it's saved on my computer, I can import it into Yandex.

Start by going to translate.yandex.com/ocr. Click the target language button to choose your language and then select file to upload.

Within seconds, you will see your English language text image translated to the language of your choice.

The translated image can then be imported to a non-English language version of your slides.

Take a look below at a short demonstration of how Yandex Translate works.

 

Support English Learners and SPED Students with CLOZEit Add-on

Supporting the unique learning needs of English learners and SPED students is not the easiest thing in the world. A scaffold I have used over the used to help these students better access content has been Cloze reading and word banks. These strategies are great entry level activities for students. 

Generating such activities can be tedious, or expensive if you purchase some off of Teachers Pay Teachers. Within the Add-ons menu of Google Docs, the CLOZEit Add-on allows you to take any text and very quickly create a Cloze reading activity with word bank. 

Get started by clicking on Add-ons in the Google Docs toolbar and click Get add-ons.

The Google Marketplace will appear. Type CLOZEit in the search bar and click it when it appears in the search results.

Install it when prompted. You will not see "Domain Install" unless you are a Super User or Google Administrator in your school or district's Google Domain. If you don't have permission to install, contact the Google Administrator for your domain (a tech coach or IT personnel) to get access. 

Once installed, you'll find it in your Add-ons menu. Hover over it and click Start.

Once launched, what you see below will appear on the right side of your screen. On your Doc, you can either highlight or underline text you want to take out to create for a Cloze reading activity. Either choice works the same. Once you've selected highlighted or underlined, you have two options. One is to create worksheet with word bank. The word bank will appear at the bottom of the page below wherever the text ends. The second option is the same except it adds a hint (the first letter of the missing word).

 

Below is what it looks like before choosing Options 1 or 2. In this case, I chose to highlight the words I wanted to remove. Underlining would have done the same thing.

Choosing Option 1, CLOZEit immediately removed the highlighted words and replaced them with blanks. Below the text, you can see the word bank containing all of the words I wanted removed. 

For years, I have read IEP's recommending Cloze reading with word banks as an accommodation. If you are looking for a quick activity for a sub, enrichment, extra credit or intervention, this Add-on will come in handy. For what might you use the CLOZEit Add-on?

Keep Track of Google Forms with Notification Rules

  

The popularity of Google Forms has exploded over the years. It's so versatile. You can create surveys to collect a wide range of data and information. You can create self paced lessons, Create Your Own Adventure Stories, assessments and more. 

Keeping track of who fills out a form and when they complete it can be a tedious task. This requires you to go searching through your Drive to find the Google Sheet that stores the Form's data. Hiding in plain sight, built into Google Sheets, is a Notification rules function. This allows you to be emailed when someone fills out your Form. 

Get started by opening the Sheet that accompanies your Google Form. Click Tools on the top menu and select Notification rules.

In the menu that appears, you have some options for setting your rules. The first option is when you'll receive the notification email. If you are tracking student responses, I recommend selecting the choice titled "A user submits a form". This will email you within minutes of a someone submitting the Form. The other option is how you'll receive the notification. You can choose from a daily digest, which is one email showing all the submissions that day. You can also choose right away which means every submission will generate an email.

You can set multiple rules by clicking Add another notification rule.

The email you receive will look like what you see below. Embedded is link that takes you directly to the responses Sheet.

There are many applications for notification rules. I've used it to track blog subscribers and unsubscribes. As a classroom teacher, I've used it to get notifications when intervention students complete lessons outside of class. The notification reminds me to check their work and update their grades. Working with parents, you can get notifications to track which parents have yet to respond to a survey. Administrators can do something similar to track which staff members have yet to respond to input surveys. The possibilities are endless. How might you use Notification rules?

Simple Tools For Communicating with Parents Who Don't Speak English

 

Communicating with parents about student progress has always been an important part of being a teacher. For parents who do not speak English, this can be a difficult thing with which to deal. The language barrier often makes parents shy about reaching out to their children's teachers. On the flip side, the language barrier can be a difficult thing if the teacher cannot speak the home language of the student. A common practice is to contact another staff member or office personnel who can translate to send a message. But there is something more powerful when the teacher is able to go directly to the parent. That personal touch can be just what is needed to reach the student. 

Take a look at some simple tools I use weekly to communicate with parents in Spanish. These tools can be used for almost any language. For "blanket statements" regarding failing grades, I created a template on a Google Keep note that I can reuse over and over. If you look closely, I put a place to type the student's name and enter my subject depending on which class I am teaching. This message, saved in Keep, is easily copied and pasted into any messaging app and translator.

There are many translation Chrome extensions available in the Chrome Web Store. My favorite is Translator by Milly Dosia. When installed, it provides a convenient drop down in Chrome for me to copy and paste text to be translated. 

The messaging app I use is Google Voice. I have used Remind and others in the past, but I like Google Voice because it is free and I am able to get a phone number that has a local area code and prefix. Other apps use out of town area codes and prefixes and parents are less likely to respond to such numbers. I am able to use my schools SIS to access parent phone numbers. I paste those numbers into Google Voice and save the contact with the student's name. 

With the student name saved with the parent's number, I copy and paste the blanket statement from Google Keep. From there, I quickly enter the student name and subject. 

Once the message is typed in English, I copy it and click the Translator extension in Chrome. In the dropdown, I paste the English message into the first box and click Translate.

Instantly, the Spanish translation is generated. It's is generally very accurate. Click the Copy button in the box containing the Spanish translation.

With the Spanish translation copied, go back to Voice, delete the English text and paste the Spanish text before hitting send. 

Doing this requires a bunch of copying and pasting, and to make that process even easier, I use another Chrome Extension called Clipboard History Pro. This creates a dropdown in Chrome that shows me all of the recent things I have copied and allows me to quickly re-copy them. 

When sending message to dozens of students each week, these tools expedite the process of breaking down the language barriers with parents who don't speak English. If you can copy and paste, you can break down the language barriers between you and parents. What other tools do you like to use to accomplish this?