the BEST way to take attendance using Google Forms and Google Sheets

TIPS: It's best to do this when your courses are pretty set so there are no additional add/drops-- saves you from having to change the values in the Google Form.

After a million different methods-- apps, spreadsheets, by hand-- I've finally hit upon the best way to take attendance! I use Google Forms and Google Sheets!

The Problem: attendance apps undoubtedly look the best and are probably the most polished. But it's not always easy to get info out of them, or they're hard to sync unless you're using desktop versions of them (and desktop versions aren't always available). And of course, spreadsheets alone usually required my taking attendance by hand and then entering them into the spreadsheets (double work for me).

The Solution: I always suspected that there had to be a way to combine Google Forms and Google Sheets to make this process easier. I just needed time to think things through a little and I decided to pay the upfront cost of doing this thinking to make it easier forever and ever. So here it is!

I create a single Google Form for all my classes. 

The first field will be date. 

The second field is a Multiple Choice field with a list of the classes I'm teaching and then make sure that "Go to section based on answer" is selected.

Create different sections for each class. This means that when I'm taking attendance for Sec 3, this question will ensure that I go to the list of students in Sec 3, etc. 

Then, use "Multiple choice grid" as the question. This was the Eureka moment! Each student is listed and it's a breeze to indicate whether they're present, late, or absent, etc.

For the columns, enter what you want to use-- e.g. "Present", "Late", "Absent", "Excused", etc. In my case, I chose P for present, L for late, and A for absent. 

Each row should be student names.

Now I used the Google Form webpage to take attendance for each class!

Also make sure you allow the respondent to edit their answers-- this allows me to change Absent to Present or Late when a student walks in late. 

But we're not done, the magic is the next section working with the Google Sheet linked to the form so read on!

The Google Sheet will have the names of students arrayed at top.

And that's absolutely fine. In the above sample, you can see the date, the class section, and the attendance I took for some students. Again, this is fine, but I prefer a transposed view, so I created a new sheet that I named "Transposed" (how creative, right?!). Put your mouse in the first cell where you want to start transposing the data and use the formula  =TRANSPOSE('Form Responses 1'!B1:110).

Notice that my first column keeps tracks of number of absences. I didn't want there to be a zero if the value wasn't found so I used a slightly modified countif formula: =if(Countif(C3:Z3,"A")=0,"",Countif(C3:Z3,"A"))

Drag this formula down the entire column and you're set.

Hope you find this helpful!