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

frank55

There is definitely so many

There is definitely so many great apps that they offer here for the students here. So many great features that come a long with them. The options are endless with what you can do with it. Orthodontist Toronto

Steven Gough

Good, We are so interested on

Good, We are so interested on this topic.following time I learn a blog, I hope that it doesn't disappoint me as much as this one. I mean, I know it was my choice to learn. logo design service || seo web design service || banner designs || seo articles

bestech

Gangaur Realtech is a

Gangaur Realtech is a professionally managed organisation specializing in real estate services where integrated services are provided by professionals to its clients seeking increased value by owning, occupying or investing in real estate.
Real estate in gurgaon

callmeser

library addition

Having gotten the basic structure up and running I think there is stone excellent work that you have done. I might suggest looking at moving some of the repeated script sections found in many of the files to a series of libraries that can Simply be added . When I get time to finish a working hack I'll post it. Also there are several well developed libraries by James Feriere And Roman Villiard at https://sites.google.com/site/scriptsexamples. I will also post any hacks I come up with joining the two.
Nice work. Thanks

andrew

Wow

Impressed you went through all of this...this was my first system, and in retrospect a very inefficient one (as you point out);)  I've written several scripts at this point that take some of the steps out of such a system.  See formMule and pushData in the scripts gallery.   In general, I'd like to make better use of libraries.  Thanks for the prod!

callmeser

this solves some problems nicely

I didn't mention in my first post that this code does a nice job solving a problem that I have been putting off.  You're push solution is nice in order to normalize lists for case management.  

We currently in the process of setting up a staff intervention system along the lines of a check and connect.   I plan on using the push data portion joined with some of my own code in addition to the libraries from the Script Examples web site to create a 'quasi' enterprise level case management workflow system that can be used to auto identify students meeting criteria for addition to the intervention program then monitor progress, goals and communication with all invested parties.

 We are also entering into a new level of attendance intervention adding a  judicial level as a final consequence.  That entrance into the court system  will obviously require a strict adherence to a clear  workflow that must  be accomplished in a certain order.  I can see how this could be an important building block in that system as well likely extending the above system to the next level.  

We, as most school districts, use a full fledged behemoth of a SIS for official record keeping but it's very size and complexity make it incredibly inflexible, requiring creative solutions to fill in the important missing functionality.  In the end the systems I see in use are missing a n important level of functionality that the buisness world has been using for years.  If a CRM or customer relationship management program were merged with a SIS, Student Information System, I believe the school systems would find a streamlining of efficiency reducing redundant work, improve communication both internally and externally and truly support the common goal of all in the Education system, staff and families alike.  That is my overall goal in the end.     Of course when I get the extra ten minutes to put that all together....As I take the little steps I will post the results.  (It might take more than ten minutes to be fully truthfully)  The first thing I will post  is my own take and a auto complete system, student finder using fusion table and extensive use of libraries, that will be the core frame work for the rest of the system. 

TODO: Create a clean copy with phony data

            Documentation (lots will be needed for it to be useful)

That will be coming soon...I hope.  

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

23
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 20 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 64 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 19 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 17 educator(s).
Using Camtasia screencasts, Google Forms with the Flubaroo script, and discussion groups, students will be able to get quick feedback, continue discussions outside of class, as review/learn at their own pace.
Recommended by 19 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 14 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 9 educator(s).