I am a virtual math teacher with almost 200 middle school students (I love my "mute all" button!). We were just told we need to start keeping attendance in the school's attendance program, which is called ProgressBook. I use a Google Form each day to collect attendance data in a spreadsheet. I would love to find a way to connect my spreadsheet to Progressbook but if I can't do that I want to at least run a script (or a query?) to create a new sheet with all the students in the same order as in the attendance software and a column containing "present" or "absent."
Here is a line from my spreadsheet that collects the attendance data:
10/2/2020 11:20:05 jserr963@******** 1/1 julianny 11am
I want to filter out students each day and eliminate the ones who filled out the form after class was over (many try to do it at night after skipping class). I teach this class at 11am and 3pm. Basically I need to find time stamps for the current day and between 11:00am-Noon or 3-4:00pm. If they are on the list and meet the time/day criteria then they are present. I have a master list with all students and the key is student email. Any help would be appreciated! I have a little programming background and know enough to be dangerous.
Best Answer
You have two classes per day (11am, and 3pm). Students submit their own attendance via a Google Form and you have a Google that displays the responses. If a student submits their attendedance between 11:00:00 and 12:00:00 or between 15:00:00 and 16:00:00, then the student is deemed to have attended. You want to query the responses to display for any given day to display the names of those students who were in attendance.
Try this formula:
=query('Form Responses 1'!A1:E,"Select A,B,C,D,E where (A>= datetime '"&TEXT('class times'!B10,"yyyy-mm-dd HH:mm:ss")&"' and A<= datetime '"&TEXT('class times'!B11,"yyyy-mm-dd HH:mm:ss")&"') OR (A>= datetime '"&TEXT('class times'!B12,"yyyy-mm-dd HH:mm:ss")&"' and A<= datetime '"&TEXT('class times'!B13,"yyyy-mm-dd HH:mm:ss")&"')",1)This assumes that:
Query Logic
The query criteria uses two datetime/timestamp criteria: one that identifies valid "11am" attendance, and the other for valid "3pm" attendance. In each case, the criteria refers to the Form Response timestamp and tests for a value >= the class start time AND <= the class end time. The two class criteria are separated by "OR". The dates and times calculated on sheet = "class_times" are referenced for the respctive start and end dates/times.
Sample data
Class Times
Query Output