Log interviews from your calendar to a hiring sheet with reminders

from datetime import datetime, timedelta, timezone
from clickruntime import on_trigger, credentials, http, state, notify

S = "https://sheets.googleapis.com/v4/spreadsheets/your-spreadsheet-id/values"

@on_trigger
def run(event):
    h = {"Authorization": f"Bearer {credentials.connection('google', 'work')}"}
    now = datetime.now(timezone.utc)
    items = http.get("https://www.googleapis.com/calendar/v3/calendars/primary/events", headers=h, params={
        "q": "Interview", "timeMin": now.isoformat(), "timeMax": (now + timedelta(days=14)).isoformat(), "singleEvents": True}).json().get("items", [])
    ids = [r[0] for r in http.get(f"{S}/Hiring!A2:A", headers=h).json().get("values", []) if r]
    reminded = set(state.get("reminded") or [])
    for e in items:
        start = e["start"].get("dateTime", e["start"].get("date"))
        guest = next((a["email"] for a in e.get("attendees", []) if not a.get("self")), "")
        row = [e["id"], start, e.get("summary", ""), guest, e.get("htmlLink", "")]
        if e["id"] in ids:
            http.put(f"{S}/Hiring!A{ids.index(e['id']) + 2}:E", headers=h, params={"valueInputOption": "RAW"}, json={"values": [row]})
        else:
            http.post(f"{S}/Hiring!A:E:append", headers=h, params={"valueInputOption": "RAW"}, json={"values": [row]})
        if start[:10] == (now + timedelta(days=1)).date().isoformat() and e["id"] not in reminded:
            notify.email_me(f"Tomorrow: {e.get('summary')}", f"{start} with {guest}\n{e.get('htmlLink')}")
            reminded.add(e["id"])
    state.set("reminded", list(reminded)[-500:])
    return {"interviews": len(items)}