Creating a Student Lookup in Google Sheets

The idea of a student lookup system has been rattling around in my head for a few years. I started this in Google Data Studio, but Data Studio started changing and I couldn't keep up. Data Studio became Looker and now four years later, it looks completely different than it did.

I have been playing with Google Sheets for a few years, and when I came to our Sheet with PYP student data, I could see that some of the data, like reading levels, was something that was outdated for most students on one sheet, but on another was kept up-to-date (EAL).

The original sheet

I started with the reading data and found that I could use VLOOKUP to connect the above sheet to the EAL sheet. The formula I used pulled the reading data from the EAL sheet and keeping our sheet updated. I also started looking for other pieces of information I could keep updated with a formula. 

This got the ideas flowing and I put my homeroom teacher hat on. I wondered what I would want to see as a homeroom teacher for a student. 

I started with a student name that would bring up strategies and goals for a student.

This was good, but then I wanted to expand it to filter between PYP, MYP, and DP. Then I wanted to know other information about the student like the number of submissions they had, reading level, language phase for MYP/DP, and a link to their support plan. I came up with this:

The updated sheet

I can see that I am not done with this - I would like to add the current number of absences, EAL goals and strategies, and something else that slipped my mind, but it is a start. 

Visualizing Student Support Data with Google Sheets

 In the beginning

I joined the [formerly-called] SEN team last year. The team was using anecdotal records for their data collection. The issue was that it was kind of a black box -immediately I wondered how I would know if the strategies I was using with students in my care were helping them reach their goals.

Last year, I helped the team reimagine some ways to collect data on student behavior through the use of rubrics, checklists, and goal trackers. We also had teachers write anecdotal notes about students while observing their behavior.

This year, the school created a new position, inclusion support coordinator. The first thing she did when she came in was to ask how we could collect data from teachers more easily. I was elated to hear this and suggested we use a Google Form that could be easily bookmarked to the browser, so that's what we did. She pushed teachers to use it when they suspected academic or behavioral issues with students. We also did away with the former ways of collecting data to try to save teachers' time.

The form responses before visualization.

With the data collected from the Form, we started by filtering the responses into the different programs; PYP, MYP, and DP. This was good, but after a month, we could see that it was hard to read. I also started asking my coordinator and principals what kind of data they look at when viewing it, and I started thinking about how to take their feedback, what I knew about what I would want to see as a homeroom teacher, and I started thinking of how. I could make this all visual. 

In my head, we would see the current weeks' totals along with the top five students. I also had this idea of a student lookup in my head.

The idea I had brewing in my head was a Google Form that could be easily bookmarked. In my head, we would collate the responses, and these would become the updated form question responses.

I had this other idea that we would have a lookup of student with the goals and the support plan. 

My demo of a student lookup 

Over winter break, as I spent the days at my in-laws house, I got to work making a dashboard. 

For this dashboard, I wanted a few things, data visualization, simple, more data, and better visualizations - right now, there's a lot of text.

Going forward, this exercise should show that data needs to be consolidated and accessible. 

Here's what it looks like.

Then I broke it. I committed the cardinal sin of editing the response sheet. After editing the response sheet, it no longer connected with the dashboard I made. 

The good thing is that I have a better idea of what I want to do next year.

NotebookLM Answers Question About Classroom Management

Photo by CDC on Unsplash

I started playing with NotebookLM last school year (2023). One prompt I tried was: 

How can effective classroom management foster student motivation and engagement?

Effective classroom management is essential for fostering student motivation and engagement. It involves creating a supportive and well-organized learning environment where students feel safe, respected, and actively involved. When teachers effectively manage their classrooms, they minimize disruptions and maximize the time students spend engaged in academic tasks.

Here are some key ways that effective classroom management fosters student motivation and engagement:

•

Clear Expectations: When teachers clearly communicate their expectations for student behavior, students know what is expected of them and can be more successful. This includes establishing rules and procedures that are easy to understand and consistently followed. Involving students in creating these agreements can foster a sense of ownership and increase compliance.

•

Positive Relationships: A positive student-teacher relationship is a key element of effective classroom management. When students feel that their teacher cares about them, they are more likely to be motivated and engaged in learning. This can be achieved through empathy, investment in student development, and modeling enthusiasm.

•

Engaging Instruction: Effective teaching practices are closely linked to effective classroom management. When teachers deliver engaging and relevant lessons, students are more likely to be motivated and less likely to misbehave. This includes using a variety of instructional strategies, providing opportunities for active participation, and making connections to students' interests and backgrounds.

•

Student Agency and Self-Regulation: Classroom management should focus on promoting self-regulation, where students take responsibility for their own learning and behavior. This can be supported through activities such as goal setting, self-assessment, and peer feedback. By encouraging student agency, teachers can help students develop autonomy and ownership over their learning.

•

Effective Feedback: Providing timely, specific, and constructive feedback is essential for student motivation and engagement. Feedback should be aligned with learning goals and criteria for performance and should help students understand their progress.

•

Managing Disruptions: Effective classroom management involves the ability to manage disruptive behavior and minimize its impact on learning. This can be done by using a variety of proactive strategies, such as nonverbal cues, proximity control, and clear communication, and by addressing the underlying causes of the behavior. Teachers should respond to misbehavior in a way that is firm but respectful.

•

Positive Environment: A positive classroom environment includes safety and order, but also must provide challenges for students, be personally relevant and involve the students directly in the learning process. Students need to feel like they belong and are a valued part of the learning community.

By implementing these strategies, teachers can create a classroom environment that fosters student motivation, engagement, and ultimately, academic success.

Make Your Own Kanji

 Have you seen this contest site where students create their own kanji? This is an interesting idea and a really creative way to learn kanji.

My wife teaches high school Japanese, and we were just looking at how to use this. I suggested she start by giving them three kanji they probably know (八、心、色）, and talk about them including the radicals. 

Then show them the corresponding kanji from this page that include those three kanji from above. 

sousaku-kanji.com

Pushing Through

My uncle was diagnosed with kidney cancer.

I found out an aunt and uncle cannot drive anymore.

I found out my friend with MS has digressed.

I found out another friend was diagnosed with Parkinson's.

My father has declined more than I thought.