Log daily Jira metrics to a sheet

from collections import Counter
from datetime import date
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify, ai

JIRA, SITE = "https://api.atlassian.com/ex/jira/your-cloud-id", "https://acme.atlassian.net/browse/"
adf = lambda text: {"type": "doc", "version": 1, "content": [{"type": "paragraph", "content": [{"type": "text", "text": text}]}]}

def jira(method, path, **kw):
    resp = http.request(method, JIRA + path, headers={"Authorization": f"Bearer {credentials.connection('jira', 'work')}", "Accept": "application/json"}, **kw)
    return resp.raise_for_status() or (resp.json() if resp.content else None)

SHEET_ID, P = "your-spreadsheet-id", "project = ENG"

def search(jql, fields=("assignee",)):
    return jira("POST", "/rest/api/3/search/jql", json={"jql": jql, "fields": list(fields), "maxResults": 500})["issues"]

@on_trigger
def run(event):
    created = len(search(f"{P} AND created >= startOfDay()"))
    resolved = len(search(f"{P} AND resolved >= startOfDay()"))
    open_issues = search(f"{P} AND statusCategory != Done")
    stale = len(search(f"{P} AND statusCategory != Done AND updated <= -14d"))
    load = Counter((i["fields"]["assignee"] or {}).get("displayName", "Unassigned") for i in open_issues)
    row = [str(date.today()), created, resolved, len(open_issues), stale, ", ".join(f"{n}: {c}" for n, c in load.most_common())]
    values = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()
    values.append(spreadsheetId=SHEET_ID, range="Jira metrics!A:F", valueInputOption="USER_ENTERED", body={"values": [row]}).execute()
    try:
        notify.email_me(f"Jira {date.today()}: {created} created, {resolved} resolved",
                        ai.generate(str(row), system="Summarise these Jira metrics in 2 plain sentences for a manager.", max_output_tokens=100).text)
    except (ai.AINotIncluded, ai.AILimitReached):
        pass
    return {"row": row}