Get Telegram reminders for due dates in an Excel sheet

from datetime import date, datetime, timedelta
from clickruntime import on_trigger, credentials, http

GRAPH = "https://graph.microsoft.com/v1.0"
TG = "https://api.telegram.org"
SHEET = "/me/drive/root:/Team/tracker.xlsx:/workbook/worksheets/Tasks/usedRange"
CHAT_ID = 123456789

def as_date(v):
    if isinstance(v, (int, float)) and v > 0:
        return date(1899, 12, 30) + timedelta(days=int(v))  # Excel serial date
    try:
        return datetime.fromisoformat(str(v)[:10]).date()
    except ValueError:
        return None

@on_trigger
def run(event):
    h = {"Authorization": f"Bearer {credentials.connection('microsoft', 'work')}"}
    rows = http.get(f"{GRAPH}{SHEET}", headers=h, params={"$select": "values"}).json()["values"]
    head = [str(c).strip().lower() for c in rows[0]]
    col = {k: head.index(k) for k in ("task", "owner", "due", "status") if k in head}
    today, items = date.today(), []
    for r in rows[1:]:
        due = as_date(r[col["due"]])
        if not due or due > today or str(r[col.get("status", 0)]).strip().lower() == "done":
            continue
        late = (today - due).days
        owner = r[col["owner"]] if "owner" in col and r[col["owner"]] else "no owner"
        items.append((late, f"{'🔴' if late else '🟡'} {r[col['task']]} ({owner}): " + (f"{late} day{'s' if late > 1 else ''} late" if late else "due today")))
    if not items:
        return {"due": 0}
    text = f"📅 Due list for {today:%a %d %b}\n" + "\n".join(t for _, t in sorted(items, key=lambda x: -x[0]))
    http.post(f"{TG}/bot{credentials.secret('TELEGRAM_BOT_TOKEN')}/sendMessage", json={"chat_id": CHAT_ID, "text": text[:4000]})
    return {"due": len(items)}