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.