Work your Canvas advising caseload without double-contacting

Build me an app that academic advisors open every day to actually work their student caseload. This is deliberately not an alerting tool that tells me who is at risk. It is the case management surface where outreach gets composed, sent, logged and closed out, so a team of advisors can divide a caseload and never unknowingly contact the same student twice. It uses Canvas for student data and messaging, and one shared Google Sheet as the team's outreach log.

The main view is a ranked caseload table across every course I advise. Build it from Canvas List Courses for a User for the signed-in advisor, so each advisor sees only the courses they are enrolled in or assigned to. For each of those courses, use List Students in a Course to get the roster, List Enrollments in a Course to read each student's current score, List Missing Submissions to find past-due unsubmitted work, and Get Course-Level Student Summaries for participation counts and last activity date.

Every row shows the student name, the course or courses they appear in, their current score, how many assignments they are missing, days since their last activity, a risk band of high, medium or low, and a short plain-language reason that names the actual trigger. Reasons should read like "score 58 percent and 4 missing assignments" or "no activity in 14 days", never a generic risk label with no explanation. Default the risk banding to high when the score is under 60 percent, or three or more assignments are missing, or there has been no activity for 10 days, and make those thresholds easy to change. A student who is high risk in more than one course should appear once with the worst band and all contributing courses listed.

Two more columns matter as much as the risk data: outreach status and outcome. Outreach status shows who last contacted this student and when, including when it was a different advisor, read from the shared log. Outcome shows the current state I have set on the student, one of contacted, resolved or escalate, or blank if untouched. Sort the list by risk band first, then by longest time since anyone reached out, so the students who are slipping and being ignored float to the top.

Give me filters across the top for course, risk band, outcome, and outreach status, where outreach status can be filtered to nobody has reached out yet, contacted by me, or contacted by a teammate. The nobody-has-reached-out filter is the most important one in the app, because it is how an advisor picks up unclaimed work without stepping on a colleague.

Clicking a student opens their profile. Show their grade in each course I advise them in, the exact list of assignments they are missing with due dates and how overdue each one is, and their participation trend over time built from Get Student-in-Course Assignment Analytics so I can see whether they are fading or recovering. Below that, show the full outreach history for this student read from the shared Google Sheet with Get Values, listed newest first, showing the date, which advisor made contact, what outcome was set, and the note that was left.

From the profile I can send the student a Canvas inbox message using Create a Conversation. Open a compose box pre-filled from an editable template that already includes the student's name and their specific missing assignments, and let me edit the subject and body freely before anything sends. If a teammate has already messaged this student within the last few days, show a clear warning banner above the compose box naming the advisor and date, so I can decide whether to still send. Nothing goes out until I press send.

Every send appends a row to the shared outreach log with Google Sheets Append Values, capturing the date and time, student name and Canvas id, course, the advisor who sent it, the outcome, and a note that includes what was sent. Setting or changing a student's outcome to contacted, resolved or escalate also appends a row, so the log is a complete history of every touchpoint rather than only the messages.

Treat that shared spreadsheet as the single source of truth for outreach and outcomes. Read the whole log with Get Values when the app loads, and derive each student's current outcome and last-contacted state from their most recent row. Because every advisor reads and writes the same sheet, the history is shared across the team by default, while the caseload itself stays personal to whoever is signed in. Let me set the spreadsheet id, the tab name and the default message template in a settings screen, and if the log is empty or missing its header row, create the headers on first use.