Create Jira onboarding tickets from a new-hire sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify, report_fetched

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, TASKS = "your-spreadsheet-id", ["Create accounts", "Prepare laptop"]

@on_trigger
def run(event):
    values = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()
    rows = values.get(spreadsheetId=SHEET_ID, range="New hires!A2:D").execute().get("values", [])
    todo = [(i, r) for i, r in enumerate(rows, start=2) if len(r) >= 3 and (len(r) < 4 or not r[3])]
    report_fetched(len(todo))
    if not todo:
        return {"hires": 0}
    done = []
    for i, (name, start, team, *_) in todo:
        keys = [jira("POST", "/rest/api/3/issue", json={"fields": {"project": {"key": "IT"}, "issuetype": {"name": "Task"},
                "summary": f"{task} for {name} ({team})", "duedate": start, "description": adf(f"New hire starting {start} in {team}")}})["key"] for task in TASKS]
        values.update(spreadsheetId=SHEET_ID, range=f"New hires!D{i}", valueInputOption="RAW", body={"values": [[", ".join(keys)]]}).execute()
        done.append(f"{name} ({start}): {', '.join(keys)}")
    notify.email_me(f"Onboarding tickets for {len(done)} new hires", "\n".join(done))
    return {"hires": len(done)}