Turn new words in your vocabulary sheet into Anki flash cards

import csv, io, json
from datetime import date
from clickruntime import on_trigger, credentials, http, ai

SHEET, FOLDER = "your-spreadsheet-id", "your-flashcards-folder-id"
S = f"https://sheets.googleapis.com/v4/spreadsheets/{SHEET}/values"
SCHEMA = {"type": "object", "properties": {"cards": {"type": "array", "items": {"type": "array", "items": {"type": "string"}}}}, "required": ["cards"]}

@on_trigger
def run(event):
    h = {"Authorization": f"Bearer {credentials.connection('google', 'work')}"}
    rows = http.get(f"{S}/Vocab!A2:D", headers=h).json().get("values", [])
    new = [(i, r[0]) for i, r in enumerate(rows, start=2) if r and len(r) < 2]
    if not new:
        return {"new": 0}
    words = "\n".join(w for _, w in new[:60])
    cards = ai.generate(f"For each Mandarin word give [translation, pinyin, example sentence], same order:\n{words}",
                        json_schema=SCHEMA, max_output_tokens=2000).json["cards"]
    data = [{"range": f"Vocab!B{i}:D{i}", "values": [c[:3]]} for (i, _), c in zip(new, cards)]
    http.post(f"{S}:batchUpdate", headers=h, json={"valueInputOption": "RAW", "data": data})
    buf = io.StringIO()
    csv.writer(buf).writerows([w, f"{c[1]}<br>{c[0]}<br>{c[2]}"] for (_, w), c in zip(new, cards))
    http.post("https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart", headers=h, files={
        "metadata": (None, json.dumps({"name": f"anki-{date.today()}.csv", "parents": [FOLDER]}), "application/json"),
        "file": ("cards.csv", buf.getvalue().encode(), "text/csv")})
    return {"cards": len(cards)}