Log a weekly HubSpot revenue forecast in a sheet

from datetime import date
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, ai, notify
SHEET_ID = "1AbCdEfGhIjKlMnOpQrStUvWxYz"
def hs(method, path, **kw):
    r = http.request(method, f"https://api.hubapi.com{path}", headers={"Authorization": f"Bearer {credentials.connection('hubspot', 'work')}"}, **kw)
    return r.raise_for_status() or (r.json() if r.content else {})

@on_trigger
def run(event):
    probs = {s["id"]: float(s["metadata"].get("probability", 0)) for p in hs("GET", "/crm/v3/pipelines/deals")["results"] for s in p["stages"]}
    deals = [d["properties"] for d in hs("POST", "/crm/v3/objects/deals/search", json={"filterGroups": [{"filters": [{"propertyName": "hs_is_closed", "operator": "EQ", "value": "false"}]}],
             "properties": ["amount", "dealstage", "closedate"], "limit": 200})["results"]]
    w = lambda p: float(p.get("amount") or 0) * probs.get(p["dealstage"], 0)
    month = date.today().isoformat()[:7]
    total = sum(float(p.get("amount") or 0) for p in deals)
    weighted = sum(w(p) for p in deals)
    this_month = sum(w(p) for p in deals if (p.get("closedate") or "").startswith(month))
    row = [date.today().isoformat(), len(deals), round(total), round(weighted), round(this_month)]
    build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values().append(
        spreadsheetId=SHEET_ID, range="Forecast!A1", valueInputOption="RAW", body={"values": [row]}).execute()
    text = f"Open deals: {row[1]}\nPipeline: {row[2]:,}\nWeighted forecast: {row[3]:,}\nExpected this month: {row[4]:,}"
    try:
        text += "\n\n" + ai.generate("Two sentences of commentary on this sales forecast:\n" + text, max_output_tokens=120).text
    except ai.AIError:
        pass
    notify.email_me(f"Forecast for week of {row[0]}", text)
    return {"weighted": row[3]}