Draft a proposal Google Doc from each new client row

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, ai, notify, report_fetched

SHEET_ID, TEMPLATE_ID = "your-spreadsheet-id", "your-template-doc-id"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    sheets = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values()
    rows = sheets.get(spreadsheetId=SHEET_ID, range="Proposals!A2:D").execute().get("values", [])
    new = [(i + 2, r) for i, r in enumerate(rows) if len(r) >= 3 and (len(r) < 4 or not r[3])]
    report_fetched(len(new))
    if not new:
        return {"summary": "Nothing new"}
    drive = build("drive", "v3", credentials=creds, cache_discovery=False)
    docs = build("docs", "v1", credentials=creds, cache_discovery=False)
    for row_num, (client, scope, budget, *_) in new:
        doc = drive.files().copy(fileId=TEMPLATE_ID, body={"name": f"Proposal - {client}"}).execute()
        approach = ai.generate(f"Write a 2-paragraph project approach for {client}. Scope: {scope}", max_output_tokens=500).text
        reqs = [{"replaceAllText": {"containsText": {"text": "{{" + k + "}}", "matchCase": True}, "replaceText": v}}
                for k, v in {"client": client, "scope": scope, "budget": budget, "approach": approach}.items()]
        docs.documents().batchUpdate(documentId=doc["id"], body={"requests": reqs}).execute()
        link = f"https://docs.google.com/document/d/{doc['id']}/edit"
        sheets.update(spreadsheetId=SHEET_ID, range=f"Proposals!D{row_num}", valueInputOption="RAW", body={"values": [[link]]}).execute()
        notify.email_me(f"Proposal draft ready: {client}", link)
    return {"created": len(new)}