Publish a weekly analysis of your sheet data in Notion

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

SHEET_ID, REPORTS_DB = "your-spreadsheet-id", "your-notion-database-id"

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    rows = sheets.values().get(spreadsheetId=SHEET_ID, range="Exceptions!A2:E").execute().get("values", [])
    since = (date.today() - timedelta(days=7)).isoformat()
    week = [r for r in rows if len(r) >= 3 and r[0] >= since]
    if not week:
        return {"rows": 0}
    stats = f"{len(week)} exceptions. By supplier: {Counter(r[1] for r in week).most_common(5)}. By type: {Counter(r[2] for r in week).most_common(5)}."
    sample = "\n".join(" | ".join(r) for r in week[:80])
    try:
        report = ai.generate(f"{stats}\n\nRows:\n{sample}", system="Write a short root-cause report: key findings, likely causes, suggested fixes.", max_output_tokens=1000).text
    except ai.AIError:
        report = stats
    paras = [{"object": "block", "type": "paragraph", "paragraph": {"rich_text": [{"text": {"content": p[:1900]}}]}} for p in report.split("\n\n") if p.strip()]
    page = http.post("https://api.notion.com/v1/pages", headers={"Authorization": f"Bearer {credentials.connection('notion', 'work')}", "Notion-Version": "2022-06-28"}, json={
        "parent": {"database_id": REPORTS_DB}, "properties": {"Name": {"title": [{"text": {"content": f"Exceptions report, week from {since}"}}]}},
        "children": paras}).json()
    notify.email_me("Weekly exceptions report published", f"{stats}\n\n{page['url']}")
    return {"rows": len(week)}