Answer Telegram questions from a Google Sheet

from datetime import datetime, timezone
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_event, credentials, http, state, ai

SHEET_ID = "your-spreadsheet-id"

@on_event
def handle(event):
    msg = (event.json or {}).get("message") or {}
    chat, text = msg.get("chat", {}).get("id"), msg.get("text")
    if not chat or not text:
        return {"skipped": True}
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    rows = sheets.values().get(spreadsheetId=SHEET_ID, range="Events!A1:F200").execute().get("values", [])
    history = state.get(f"chat:{chat}") or []
    data = "\n".join(" | ".join(r) for r in rows)
    try:
        answer = ai.generate(f"Data:\n{data}\n\nRecent chat:\n" + "\n".join(history[-6:]) + f"\n\nQuestion: {text}",
                             system="Answer briefly using only the data. If it is not there, say so.", max_output_tokens=300).text.strip()
    except ai.AIError:
        answer = "Sorry, I can't answer right now. Please try again later."
    token = credentials.secret("TELEGRAM_BOT_TOKEN")
    http.post(f"https://api.telegram.org/bot{token}/sendMessage", json={"chat_id": chat, "text": answer})
    state.set(f"chat:{chat}", (history + [f"Q: {text}", f"A: {answer}"])[-10:])
    sheets.values().append(spreadsheetId=SHEET_ID, range="Log!A:D", valueInputOption="USER_ENTERED",
                           body={"values": [[datetime.now(timezone.utc).isoformat(timespec="minutes"), str(chat), text, answer]]}).execute()
    return {"answered": True}