Track SLA risks and stuck tickets in a sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from datetime import datetime, timezone, timedelta
from clickruntime import on_trigger, http, credentials, state, notify

ZD, AGENT = "https://yourcompany.zendesk.com/api/v2", "you@yourcompany.com"

def zd(method, path, **kw):
    r = http.request(method, ZD + path, auth=(f"{AGENT}/token", credentials.secret("ZENDESK_API_TOKEN")), **kw)
    return r.raise_for_status() or (r.json() if r.content else None)


sheets = lambda: build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
SHEET_ID = "your-spreadsheet-id"
TARGET = timedelta(hours=4)

@on_trigger
def run(event):
    now, warned, rows, soon = datetime.now(timezone.utc), set(state.get("warned") or []), [], []
    for t in zd("GET", "/search.json", params={"query": "type:ticket status:open status:pending"})["results"]:
        age = now - datetime.fromisoformat(t["created_at"].replace("Z", "+00:00"))
        left = round((TARGET - age).total_seconds() / 3600, 1)
        rows.append([t["id"], t["subject"], t["status"], left])
        if t["status"] == "open" and 0 < left <= 1 and t["id"] not in warned:
            soon.append(f"• #{t['id']} {t['subject']} ({left}h left)")
            warned.add(t["id"])
        if t["status"] == "pending" and age > timedelta(days=3) and f"chased:{t['id']}" not in warned:
            zd("PUT", f"/tickets/{t['id']}", json={"ticket": {"comment": {"body": "Pending 3+ days: please follow up with the customer.", "public": False}}})
            warned.add(f"chased:{t['id']}")
    sheets().values().update(spreadsheetId=SHEET_ID, range="SLA!A2", valueInputOption="RAW", body={"values": sorted(rows, key=lambda r: r[3])}).execute()
    if soon:
        notify.slack("Due within the hour:\n" + "\n".join(soon), channel="#support-leads")
    state.set("warned", sorted(map(str, warned))[-3000:])
    return {"tickets": len(rows), "warned": len(soon)}