Email the team a weekly metrics report from a sheet

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

SHEET_ID = "your-spreadsheet-id"
TEAM = ["ops@acme.example", "founders@acme.example"]

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    rows = sheets.spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Weekly!A1:Z").execute()["values"]
    header, prev, last = rows[0], rows[-2], rows[-1]
    if state.get("last_week") == last[0]:
        return {"skipped": "already sent"}
    lines = []
    for name, a, b in zip(header[1:], prev[1:], last[1:]):
        change = (float(b) - float(a)) / float(a) * 100 if float(a) else 0
        lines.append(f"{name}: {b} ({change:+.0f}%)")
    summary = ai.generate("Summarise in 3 sentences what changed most:\n" + "\n".join(lines), max_output_tokens=150).text
    http.post("https://api.sendgrid.com/v3/mail/send", headers={"Authorization": f"Bearer {credentials.secret('SENDGRID_API_KEY')}"},
              json={"from": {"email": "reports@acme.example"}, "subject": f"Weekly metrics, week of {last[0]}",
                    "personalizations": [{"to": [{"email": e} for e in TEAM]}],
                    "content": [{"type": "text/plain", "value": summary + "\n\n" + "\n".join(lines)}]})
    state.set("last_week", last[0])
    return {"metrics": len(lines)}