Easy to Use Google Sheet for Interpreting Your Roll Call Attendance Report

[kblack]

This document is meant to provide an easier way for faculty to see their Roll Call Attendance results with a minimal amount of effort. It's in many ways a supplement to my longer document in the Community, Using the Roll Call Report: A Guide for People Who Do Not Use Excel, where I tried to explain how to use pivot tables in Excel to produce the most likely things you would like to extract from the Roll Call Attendance report that you can generate from Canvas.

This document could have been titled Using the Roll Call Attendance Report in 5 Easy Steps (or Thereabouts). What I have tried to accomplish here is provide you with a shareable Google Sheet that you can save to your own Drive account. It has ready-made pivot tables for you to use to get results similar to those described in my other document, but with next to no effort involved on your end. So you not only do not have to go anywhere near Excel; you really don't even have to know Google Sheets, other than how to save and open one.  More importantly, for me, there are no "Mac vs. PC" issues, since it uses Google Sheets on the web.

Little did I realize that the previously linked document or this post would even be relevant at this point in time!  Like many of us, I had hoped that another solution to Roll Call Attendance would have been introduced by now, much less an easier to use report. In the meantime, I hope that those of you do not wish to delve into spreadsheets find this a useful shortcut to getting attendance reports.

What You Need to Know

The only requirement for the solution outlined below is a Google Drive account and the ability to copy/paste. Period.  Specifically, you will be using Google Sheets within Google Drive, but even if you do not know Google Sheets, it will make little difference! If you can:

• Open a file
• Copy and then paste

You are basically set.

I am sharing a Google Sheet that takes advantage of pivot tables. When I wrote my original document linked above, I admit I had no idea Google Sheets had pivot table capability, since I am (still) an Excel guy more than a Google Sheets guy.  But once I discovered this capability—and I am by no means an expert in Google Sheets pivot tables—I jumped right in.

What to Do

For people who prefer to watch a demonstration first, I created the following video overview of what to do.  It is closed-captioned (by me…not by YouTube!) if you need captions:

These steps are also outlined on the first sheet of the shared Google Sheet that I made available, but you to have to get there first, so here's what to do:

• Login to Google Drive and head to this shared sheet.
• Save your own COPY of this sheet to your Drive account, since this not an editable sheet.  You will need editing capabilities to copy and paste your roll call attendance report into it.

After you have done that, run your Roll Call Attendance report as outlined here: How do I run Roll Call Attendance reports in a course?

Next, here are the promised five easy steps:

1. Head to your copy of the Google Sheet you just retrieved and click on File>>Open and then click Upload on the ensuing dialog box to upload your saved attendance report that Canvas sent to you. This will open your report in a separate Google Sheets tab. The attendance report from Canvas is in the .csv file format, and that will open up just fine in Google Sheets in a separate browser tab.
2. Select the entire sheet you just opened (the .csv file of the attendance report from Canvas) by clicking the white box between A and 1.  (There is a screen capture of this on the Steps to do First! sheet.)
3. Click Edit>>Copy.
4. Click on into the copy of the sheet that you just copied from mine, but make sure you are in the sheet called OriginalCSVData. Make sure you are in cell A1. (You should be automatically.)
5. Click Edit>>Paste.

You're done! Look at the next three tabs for the pivot tables created. The first sheet (Steps to do First!) of your copied sheet explains what the three tabs will do, as does the video posted above and the text below.

What the Shared Sheet Has

The other tabs are pivot tables based on the data that you just copied into the OriginalCSVData tab.

The first tab, NumTimesPresentAbsent gives you, for every student, the total number of times you marked them Present, Absent, or Late in Attendance in Canvas.  This is likely the most important data for you.

 
The second tab, ListofDaysPresentAbsent gives you, for every student, a listing of every day you recorded their attendance and their attendance status that day. This is good for those times you wanted to check on a student's attendance for a specific date.

The final tab, RecordedDaysbyStudentName, is handy to verify if you missed a date altogether or if you missed taking attendance for a specific student. Admittedly, if you missed a date altogether you will have to scan through all of the dates to see for yourself. Only you know (I hope!) the dates that you had class, so if you missed a date altogether this sheet will not help much unless you realize--scanning the columns--that you missed a date.  If, however, you missed taking attendance for some students but not others, the sheet uses conditional formatting to color the cells green. The number 1 in those cells does not, in itself, signify much of anything other than the fact that you took attendance. It does not mean that that a student was present or absent; it simply means you took attendance. Therefore, if you see any white-colored, blank cells for a date, it means you forgot to mark a student that day while you marked some others. This is good for double-checking yourself and to ensure that the Total number that you see in the first sheet, NumTimesPresentAbsent, is the same for all students.  (The video may provide some additional information if my explanation here is confusing!)

This should work with all attendance reports, given that, according to the documentation, all generated reports have the same eleven columns of fields. The only time a report may not work is if you use badges and the report includes a field for badges, in which case you must remove the badges field.  But I tried this with several attendance reports that I ran at my own institution, and this Google Sheet should work!

I hope this is useful for those of you who need a relatively quick-and-dirty way to see the results of your roll call attendance reports.