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.

  • 1
    Welcome to Web Applications. Please bear in mind that questions on this site should be specific, so please edit your question to make it specific. Also it's a good idea to include a brief description of your search/research efforts as is suggested in How to Ask – Rubén Oct 12 '20 at 1:09
  • Welcome. I would love to find a way to connect my spreadsheet to ProgressBook That is something that you should ask ProgressBook or their support community. – Tedinoz Oct 12 '20 at 11:50

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:

  • the form responses are on sheet = "Form Responses 1"
  • create a sheet called "class_times" to build valid login times that are used in the query criteria.
  • create another sheet (named whatever you wish), and enter the query formula in cell A1.

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

Form Responses

Class Times

Class Times

Query Output

Query Output

  • THANK YOU for the responses I have received so far! I will be using your input to test this over the weekend. Getting ready for my 3pm 8th grade class (I actually have 2 7th and 2 8th grade classes). Blessings! – Faster Pastor Oct 13 '20 at 18:42

You mentioned

...I want to at least run a script (or a query?) to create a new sheet ... Basically, I need to find time stamps for the current day and between 11:00am-Noon or 3-4:00pm...

Assuming you give the formula link to your students the same day as the lesson, you can try the following formula

=QUERY('Form Responses'!A1:D,"WHERE day(A)="&DAY('Form Responses'!A2)&" 
                               AND hour(A)=11 OR hour(A)=15 ",1)

enter image description here

(Please adjust ranges to your needs)

  • Being a teacher myself I know how important your question is. However. Please remember that you can also contribute to this community that helps you. As per site guidelines when the answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Oct 13 '20 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.