Answer your standard questions about each new financial report in a sheet

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

SHEET_ID, Q = "your-spreadsheet-id", "'your-folder-id' in parents and mimeType='application/vnd.google-apps.document'"
HOST, NS = "https://kb-abc123.svc.aped-1234.pinecone.io", "reports"  # your index host and namespace
def upsert(records):
    http.post(f"{HOST}/records/namespaces/{NS}/upsert", data="\n".join(json.dumps(r) for r in records[:96]), headers={
        "Api-Key": credentials.secret("PINECONE_API_KEY"), "X-Pinecone-API-Version": "2025-04", "Content-Type": "application/x-ndjson"}).raise_for_status()

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    drive, sheets = build("drive", "v3", credentials=creds, cache_discovery=False).files(), build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values()
    new = drive.list(q=f"{Q} and createdTime > '{state.get('since') or '2026-01-01T00:00:00Z'}'", fields="files(id,name,createdTime)").execute()["files"]
    questions = [r[0] for r in sheets.get(spreadsheetId=SHEET_ID, range="Questions!A2:A").execute().get("values", []) if r]
    rows, pc = [], {"Api-Key": credentials.secret("PINECONE_API_KEY"), "X-Pinecone-API-Version": "2025-04"}
    for f in new:
        text = drive.export(fileId=f["id"], mimeType="text/plain").execute().decode("utf-8", "ignore")
        upsert([{"_id": f"{f['id']}#{n}", "chunk_text": text[i:i + 1500], "file_id": f["id"]} for n, i in enumerate(range(0, len(text), 1500))])
        for q in questions:
            hits = http.post(f"{HOST}/records/namespaces/{NS}/search", headers=pc, json={"query": {"inputs": {"text": q}, "top_k": 4,
                             "filter": {"file_id": {"$eq": f["id"]}}}}).json()["result"]["hits"]
            answer = ai.generate(f"Answer briefly from these passages only.\n{[h['fields']['chunk_text'] for h in hits]}\nQ: {q}",
                                 max_output_tokens=200).text.strip()
            rows.append([f["name"], q, answer])
    if rows:
        sheets.append(spreadsheetId=SHEET_ID, range="Answers!A:C", valueInputOption="RAW", body={"values": rows}).execute()
        state.set("since", max(f["createdTime"] for f in new))