Get a daily vocabulary quiz from your sheet on Telegram

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

SHEET_ID = "your-spreadsheet-id"
CHAT_ID = "your-telegram-chat-id"

def tg(text):
    http.post(f"https://api.telegram.org/bot{credentials.secret('TELEGRAM_BOT_TOKEN')}/sendMessage", json={"chat_id": CHAT_ID, "text": text})

def sheet():
    return build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()

@on_trigger
def quiz(event):
    rows = sheet().get(spreadsheetId=SHEET_ID, range="Vocab!A2:D").execute().get("values", [])
    rows = [(r + ["", "", "0", "0"])[:4] for r in rows]
    ranked = sorted(range(len(rows)), key=lambda i: int(rows[i][2] or 0) - int(rows[i][3] or 0))[:5]
    state.set("quiz", [[i] + rows[i] for i in ranked])
    tg("Tonight's words:\n" + "\n".join(f"{n + 1}. {rows[i][0]}" for n, i in enumerate(ranked)))
    return {"asked": len(ranked)}

@on_event
def answer(event):
    quiz = state.get("quiz")
    reply = ((event.json or {}).get("message") or {}).get("text", "")
    if not quiz or not reply:
        return {"skipped": True}
    try:
        res = ai.generate(f"Words and meanings: {[q[1:3] for q in quiz]}\nAnswers: {reply}\nFor each word, is the answer right (allow typos, synonyms)?",
                          json_schema={"type": "array", "items": {"type": "boolean"}}, max_output_tokens=60).json
    except ai.AIError:
        tg("Could not check your answers right now, try again later.")
        return {"checked": False}
    for (i, word, meaning, right, wrong), ok in zip(quiz, res):
        counts = [int(right or 0) + ok, int(wrong or 0) + (not ok)]
        sheet().update(spreadsheetId=SHEET_ID, range=f"Vocab!C{i + 2}:D{i + 2}", valueInputOption="RAW", body={"values": [counts]}).execute()
    tg(f"{sum(res)}/{len(quiz)} correct. " + ", ".join(f"{q[1]} = {q[2]}" for q, ok in zip(quiz, res) if not ok))
    state.delete("quiz")
    return {"correct": sum(res)}