Weekday Blackboard reminders for work due in the next 48 hours

Every weekday at 7am, email my Blackboard students a personal reminder about the work they still owe in the next 48 hours. Send one message per student covering all of their upcoming items across every course, never one email per assignment. Use a cron trigger, since Blackboard does not send outgoing webhooks for an approaching due date and the window has to be computed inside the run.

Start by reading the Google Sheets reminder ledger with Get Values, before anything else. Each row records a student, an assignment, the course, and the date the reminder was sent. Treat the combination of student and assignment as the deduplication key: if a student has already been reminded about a given gradebook column, do not remind them about it again in this run.

Next, use the Blackboard Get Courses operation to list courses, and scope this to the courses running in the current active term rather than sweeping every course on the instance. The default Blackboard site quota is 10,000 requests per 24 hours shared across the whole institution, so keep the working set deliberately small. For each course in scope, call Get Grade Columns, which lives on the v2 gradebook path, and keep only the columns whose grading due date falls between now and 48 hours from now.

For every course that has at least one column due inside that window, call Get Course Memberships to list the enrolled students, filtering to the student course role so instructors and teaching assistants are excluded. Then call Get Column Attempts for each of those columns to work out which students have no attempt recorded yet. A student with a submitted attempt has already done the work and must not be reminded about it.

All of these Blackboard list endpoints page with offset and limit and return a paging.nextPage link. Follow nextPage until it is absent, so students or columns sitting on later pages are not silently missed.

Group the outstanding items by student, not by assignment. For each student with at least one item still owing, draft a single short and encouraging Gmail message and send it with Send a Message. Name each assignment, the course it belongs to, and when it is due. Use judgement about emphasis: a major project due tomorrow morning reads very differently from an optional practice quiz, so lead with what actually matters, keep the whole message to a few sentences, and keep the tone supportive rather than scolding. Do not send anything to a student who owes nothing.

Resolve each student's email address from the membership record by expanding the related user object, or by looking the user up with Get Users when the address is not already inlined.

After each message sends successfully, append one row per reminded assignment to the Google Sheets ledger with Append Values, recording the student, the assignment, the course, and the date sent. Only log items that were genuinely included in a sent email, so that a failed send gets retried on the next run instead of being silently marked as done.