Email a weekly summary of your tracking sheet from Outlook

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, http, credentials, ai, state

api = lambda name, version: build(name, version, credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)

def graph(method, path, **kw):
    r = http.request(method, "https://graph.microsoft.com/v1.0" + path, headers={"Authorization": f"Bearer {credentials.connection('microsoft', 'work')}"}, **kw)
    return r.raise_for_status() or (r.json() if r.content else None)

def send_mail(to, subject, text):
    graph("POST", "/me/sendMail", json={"message": {"subject": subject, "body": {"contentType": "Text", "content": text},
          "toRecipients": [{"emailAddress": {"address": a}} for a in to.split(",")]}})

SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    rows = api("sheets", "v4").spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Summary!A2:B").execute().get("values", [])
    now = {r[0]: float(r[1]) for r in rows if len(r) == 2 and r[1].replace(".", "", 1).isdigit()}
    last = state.get("last") or {}
    lines = [f"{k}: {v:,.0f}" + (f" ({v - last[k]:+,.0f})" if k in last else "") for k, v in now.items()]
    body = "\n".join(lines)
    try:
        body = ai.generate(body, system="In 3 sentences, summarise what changed this week for a leadership team.", max_output_tokens=150).text + "\n\n" + body
    except ai.AIError:
        pass
    send_mail("ceo@example.com,ops@example.com", "Weekly numbers", body)
    state.set("last", now)
    return {"metrics": len(now)}