How to keep track of attendance and student-submitted excuses for absences?

There is a sort of a solution using Google Apps, but it's ugly and it's still very much imperfect (more on that at the bottom).

You could create a master spreadsheet (that for the sake of example has the internal ID of "masterSheet") that looks like this:

      A          B         C         D         E         F
1| Name       | 9/1 | 9/1 Comment | 9/2 | 9/2 Comment | etc.
2| Student A  |     |             |     |             |
3| Student B  |     |             |     |             |
4| Student C  |     |             |     |             |

Then you'll create another spreadsheet for Student A:

    A            B                                C
1| Date | Attendance Grade                 | Comment/Excuse
2| 9/1  | =IMPORTRANGE("masterSheet","B2") |
3| 9/2  | =IMPORTRANGE("masterSheet","D2") |
3| etc. | =IMPORTRANGE("masterSheet","X2") |

You'll then make columns A and B (as well as cell C1) uneditable except by you. You don't necessarily need to type in the IMPORTRANGE() stuff manually, and you do it formulaicly much easier if you have both the master and student spreadsheets go in the same order (although it's possible either way).

Initially, the student will just see

    A            B                                C
1| Date | Attendance Grade                 | Comment/Excuse
2| 9/1  |                                  |
3| 9/2  |                                  |
3| etc. |                                  |

As you put in their attendance grades for each day, it will show up automatically in theirs, but they won't be able to edit it if you properly protect the range. Now, for their comments. You'll do the exact same thing, but in reverse, and you'll get:

      A          B         C                           D         E                           F
1| Name       | 9/1 | 9/1 Comment                   | 9/2 | 9/2 Comment                   | etc.
2| Student A  |     | =IMPORTRANGE("studentA","C2") |     | =IMPORTRANGE("studentA","C3") |
3| Student B  |     | =IMPORTRANGE("studentB","C2") |     | =IMPORTRANGE("studentB","C3") |
4| Student C  |     | =IMPORTRANGE("studentC","C2") |     | =IMPORTRANGE("studentC","C3") |

If a student enters in a comment in their spreadsheet, it will show up on yours.

Now, a few comments about this:

It's ugly. It's awful. But it basically does what you want. But there's also a security consideration that will greatly reduce its efficacy to be effectively useless. Besides the fact that a student could get access to your original spreadsheet by using IMPORTRANGE() on their comment field, Google Apps requires a user to have viewing or editing privileges to the document referenced by IMPORTRANGE(). There are many ways ways to obfuscate, but it will only ever be obfuscation, and not real security.

What you should do from my experience

Just use Blackboard. It does NOT take that long to take roll with it. It is a spreadsheet. On day one of the semester, I take five minutes and create a column for each day of class, and then don't have to worry about it again (and if you're teaching the same classes, ideally they're on the same schedule and you can either merge the course or clone the course so you only do it once for all three). Then each day I just hit in 5,enter,0,enter,5,enter,5,enter,5,enter,0,enter (5 is fully present in my personal system, 0 is absent).

All the attendance columns get pushed to the bottom of the student's view (the right of the instructor's view) by using Grade Center -> Manage -> Column Organization.

For students to contact you, make it clear they must use a standard subject line that can be caught and filtered in your e-mail program into a specific e-mail excuse box. If I accidentally marked them as absent and they weren't, or if they had an excuse, they are instructed to e-mail me using a subject line CC'd to themselves that begins "Attendance:" and that gets filtered into an attendance folder for that semester (you may want them to also add in a section, if you don't have merged courses) where I can then deal with it. At the end of the semester, I have students verify that I properly tended to any necessary changes in attendance grades and then the one or two mistakes (if any) can be fixed then.

I regularly deal with 120 students a semester (4 sections x 30 students) and have had no problems with this set up.

Unless you have an attendance-specific module in Blackboard that provides the functionality you want, you are going to have problems finding anything that is FERPA-compliant or that is not going to be a giant PITA to use.

For a multi-section course with this many students, you really should make use of a Learning Management System (Blackboard in your case) for an online gradebook and attendance tracking system that allows students to see their grades and attendance records. My experience is with Canvas and Moodle rather than Blackboard, so I won't attempt to answer this question in detail, but I'm sure that Blackboard should be able to do this.

At another level, you'll need to record who actually shows up each day. You can enter the information by hand, or you might consider using an audience response system ("clicker") and have the students check themselves in. For large lecture courses this works very well.