Answer chat questions from your sheet data and documents together

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

SHEET_ID, REPLY_URL = "your-spreadsheet-id", "https://chat.example.com/api/reply"
HOST, NS = "https://kb-abc123.svc.aped-1234.pinecone.io", "guides"  # your index host and namespace
def search(text, k=5):
    r = http.post(f"{HOST}/records/namespaces/{NS}/search", json={"query": {"inputs": {"text": text}, "top_k": k}}, headers={
        "Api-Key": credentials.secret("PINECONE_API_KEY"), "X-Pinecone-API-Version": "2025-04"}).json()
    return [h["fields"] for h in r["result"]["hits"]]

@on_event
def handle(event):
    body = event.json or {}
    q = str(body.get("message", ""))[:500]
    rows = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False) \
        .spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Products!A1:F").execute().get("values", [])
    words = {w.lower() for w in q.split() if len(w) > 3}
    matches = [rows[0]] + [r for r in rows[1:] if words & {c.lower() for cell in r for c in cell.split()}][:20]
    docs = "\n\n".join(h["chunk_text"] for h in search(q, 4))
    answer = ai.generate(f"Answer using only this data and these documents. Say if they don't cover it.\nSHEET ROWS: {matches}"
                         f"\nDOCUMENTS:\n{docs}\n\nQuestion: {q}", max_output_tokens=400).text
    http.post(REPLY_URL, json={"session_id": body.get("session_id"), "text": answer})
    return {"rows": len(matches) - 1}