You are missing some Flash content that should appear here! Perhaps your browser cannot display it, or maybe it did not initialize correctly.

Notes

Shorthand of setup steps...
  1. Using an Apps for EDU account, open and generate your own copies of the 5 templates below.
  2. Authorize the PushData script on "Master Student List," "Master Advisory List," and "Lateness Log" spreadsheets
  3. Authorize SendEmails script on "Lateness Log"
  4. Connect "Master Student List" to "Link Generator" using PushData script
  5. In Master Student List set trigger on PushData script to "On Edit"
  6. Connect Master Student List to Lateness Log - ReceivedStudentData sheet
  7. Connect Master Advisor List to Lateness Log using PushData script
  8. Connect Master Student List and Lateness Log to Data Warehouse using PushData script
  9. Set FormKey and Domain on Link Generator (note that FormKeys is a hidden sheet)
  10. Set Additional Script Triggers: Lateness Log ->SendEmail trigger on form submit,  Lateness Log -> PushData trigger once an hour, Master Advisor List -> PushData trigger on edit
  11. Testing:  Substitute a test email you can check for an advisor in Master Advisor List substitute a test email for a student with that same advisor in Master Student List Use the link generator to submit a lateness form on that same student.  If emails arrive, you know that spreadsheet scripts are running fine!
  12. Update your bell schedule in Link Generator (hidden sheet) and Lateness Log (Unless you know how to modify the spreadsheet formulas, do not change period numbers, just times)
  13. Create or use existing Google Site (or page, if using page-level permissions) that is visible only to school faculty. Embed the Link Generator in a page (hide all unneeded sheets). Create another page and embed all relevant lateness charts.
  14. Now clear out and replace fake data from 
    Master Student List (leave headers)
    Master Advisor List  (leave headers)
    Check Link Generator
    and Lateness Log to ensure changes propagated correctly.
    Test and tweak!
     
    In some cases, you may need to drag tally formulas down to accomodate longer student or advisory lists.
     
    Do not abuse your new powers...
     

Credits:

Much credit due to ACTVF Principal Mark Dunetz for pioneering some of these techniques as a deep and integral part of running his unmistakeably effective school.

The PushData script was authored primarily by math teacher and self-taught coder David Wees. 

Geeky fun excerpts of YouTube clips and music credited to Tesla coil rockers, ArcAttack!

Comments

jbranzburg

screenshots

Andrew - do you have this in a non-video format, say a sequence of screenshots?

Jeff

andrew

Implementing this in classrooms

Got a great question via email from Dave Rothauser:

"How do teachers interact with the system? When do they use it? At the beginning of class? Is this connected to a period attendance system? Do schools have any trouble with teachers not using one of these systems?"

In my experience, morning arrival is the most important time to record lateness, so I'd probably limit it to that for starters and perhaps use hall-sweeps to handle it if you have trouble with kids dangling in hallways in later periods.  I've left the date and arrival time editable in the form so teachers can use paper-based logs and transfer the data later, if they want.

A system I've seen work really well, at a minimum of disruption to class:  Late arriving students must stop in the main office for a timestamped hallway pass, where office staff record latenesses in the database and dole out lunch detention for recent repeat offenders.   Teachers simply do not admit late students without a pass.

One thought I had was whether to add "Excused" or "Unexcused" as a form field.    I was assuming the lateness would only be logged if unexcused, but others may differ here.

In general, I find teachers tend to readily cooperate with data entry tasks that have a clear value-add and allow them to be more effective, and where transparency encourages peer-based accountability.   When these systems really work, it's usually inside of an advisory "caseload" model, where faculty advisors and grade-level team leaders are accountable for school-home outreach on a specific group of students, and is able to easily scan important analytics to catch at-risk kids early.   That said, building this into an existing school culture would obviously take a great deal of intentionality.  Start building from where the teachers currently experience the most inefficiency and percieved ineffectiveness.

Here are some other important live analytics I've seen as part of a system like this:

  • Conduct / Intervention / Communication
  • Current Grades (use an online gradebook that exports in CSV...update weekly)
  • Attendance Logs (export from your district's attendance-keeping SIS, import daily)
  • Cumulative Attendance
  • Cumulative Lateness
  • Class Removals
  • Cumulative Class Removals
  • Lunch and After-School Tutoring Attendance
  • Cumulative Tutoring Attendance
  • Early Dismissals
  • Cumulative Early Dismissals
  • Extra-curricular participation
  • Suspensions
  • Outstanding Credits / State Exams
  • PSAT Scores

I've also heard of principals creating friendly competitions amongst teachers and students to encourage use of these systems.   Feeding live charts of group analytics to a large flatpanel in the school's entryway could provide for all kinds of fun!

 

jspevack

Turn Google Forms and Spreadsheets into a database

Thanks Andrew - this is really top notch.

DIY Student Information System: Module 1, "The Lateness Zapper"

Problem of practice

Many schools are rife with inefficient operational processes, poor internal communications systems, and relative data-blindness. These persistent issues prevent many otherwise talented teams from truly taking flight. How can school-level staff create clean, cheap, and easy-to-use data capture, reporting, and operations systems that are flexible enough to evolve with the dynamic needs of an innovative school?

Solution

This introductory module provides what you need to start building the modular and lightweight data system of your dreams. The first installment is a "Lateness Zapper" that automates logging, communications, and reporting on student lateness.

Cast your vote

6
Recommend

Tweet this

Browse other items with similar tags

This hack shows how to install a Google Apps Script in your Google Site to look up the grades of the currently logged-in student or parent and generate a live bar of progress from your Google spreadsheet!
Recommended by 1 educator(s).
You can embed this script as a gadget into your Google Site, and then use it to create the same event on multiple Google calendars at the same time. See the link below this video to copy and paste the script.
Recommended by 1 educator(s).
This is an attempt to create a simple tool that a team of teachers could use to track indicator-level data on shared assessments. It's low-stakes, hopefully approachable and can be customized to fit the team's needs.
Recommended by 1 educator(s).
The Reportlet Script, written in Google Apps Script, serves up easily-templated tabular reports that can include custom data, images, and charts keyed to the logged in user. Power users get a dropdown to allow viewing of any report.
Recommended by 1 educator(s).
Using a script stolen from another project and a student template, this platform will allow a teacher to enter in their classroom roster, hit a button and automatically create goal setting sheets for their class and monitor their students' progress!
Recommended by 1 educator(s).
The formMule (aka "The Mule") is a Google Apps script that is useful for automating lookups, emails and calendar events created dynamically from Google Forms.
Recommended by 1 educator(s).
This is a simple script housed in a google sheet that allows teachers to create handouts for their classroom, shares the student appropriately and organizes the handouts in a collection. It can be used to create sheets as well as docs.
Recommended by 1 educator(s).