"""Sheet actions over the existing workbook permissions and writers.""" from difflib import get_close_matches import json from fastapi import HTTPException from core import external_access, external_tools from . import legacy_action WORKBOOK_RESOURCE_URI = "ui://loopable/workbook.html" WORKBOOK_RESOURCE_MIME = "text/html;profile=mcp-app" WRITE_ABOUT_EXAMPLE = { "title": "Sheet write", "description": "Update the requested workbook cells.", "tags": ["reporting"]} WRITE_INTENT_EXAMPLE = "Update the requested workbook cells" WRITE_CALL_EXAMPLE = { "action": "write_cells", "arguments": { "workbook_id": "", "tab": "", "cells": {"A1": {"kind": "literal", "value": "Weekly review"}}}, "intent": WRITE_INTENT_EXAMPLE, "about": WRITE_ABOUT_EXAMPLE} #: The next call after workbook_missing (S79-T11): list readable workbooks, then read one by its id. WORKBOOK_MISSING_FIX = ( 'Call sheet with {"action": "workbook.list", "arguments": {"limit": 100}} and follow cursor until it is null. ' 'Then call sheet with {"action": "workbook.get", "arguments": {"workbook_id": ""}} using a returned id.') def _readable_workbooks(ctx): """Admit workbook documents before disclosing any indexed identity.""" from core import sheets ctx.session.require("sheets") rows = {} for identity in sheets.list_workbooks(ctx.session.runtime): try: book = sheets.get_workbook(ctx.session.runtime, identity["id"], before_normalize=ctx.require_reader) except HTTPException as exc: if exc.status_code not in (403, 404): raise continue except sheets.SheetRefusal as exc: if exc.code not in {"workbook_missing", "workbook_id_invalid"}: raise continue rows[book["id"]] = {key: book[key] for key in ("id", "name", "rev")} return sorted(rows.values(), key=lambda row: (row["name"], row["id"])) def _released_shape(ctx, verb, args): """Answer a call in the released verb-in-arguments shape (D-9630) through the legacy object door.""" if "action" not in args and "arguments" not in args: return None return external_tools.dispatch(ctx, "workbook_object", {**args, "action": verb}) def workbook_list(ctx, args): """Page every currently readable workbook identity without a total result cap.""" from routes_external import _decode_query_cursor, _encode_query_cursor, _json_digest released = _released_shape(ctx, "list", args) if released is not None: return released rows = _readable_workbooks(ctx) query = _json_digest({"action": "sheet.workbook.list"}) snapshot = _json_digest(rows) offset = (_decode_query_cursor(ctx.session, args["cursor"], query, snapshot) if "cursor" in args else 0) limit = args.get("limit", 100) page = rows[offset:offset + limit] end = offset + len(page) cursor = _encode_query_cursor(ctx.session, query, snapshot, end) if end < len(rows) else None return {"workbooks": page, "count": len(page), "total": len(rows), "cursor": cursor} def workbook_get(ctx, args): """Find one readable workbook by id or exact name, without fuzzy selection.""" from core import sheets from deps import err ctx.session.require("sheets") released = _released_shape(ctx, "get", args) if released is not None: return released if ("workbook_id" in args) == ("name" in args): raise err(400, "tool_arguments_invalid", "Send either workbook_id or an exact name.") if "workbook_id" in args: book = external_tools._book(ctx, args) else: rows = _readable_workbooks(ctx) matches = [row for row in rows if row["name"] == args["name"]] if not matches: # D-9630 kept: an exact name read without its letter case still resolves, never a near name. matches = [row for row in rows if row["name"].casefold() == args["name"].casefold()] if len(matches) > 1: raise err(409, "workbook_name_ambiguous", "More than one readable workbook has this exact name. Use a workbook_id.", {"candidates": matches}) if not matches: names = get_close_matches(args["name"], sorted({row["name"] for row in rows}), n=5) raise err(404, "workbook_missing", "No readable workbook has that exact name. " + WORKBOOK_MISSING_FIX, {"suggestions": names}) book = external_tools._book(ctx, {"workbook_id": matches[0]["id"]}) projected = ctx.project_book(book) return {"workbook": {key: projected[key] for key in ("id", "name", "rev", "sheets")}} def read_guide(): """Describe the grouped workbook read shape and available replacements.""" return ('Workbook discovery example: {"action":"workbook.list","arguments":{"limit":100}}. ' 'Follow cursor until null. Exact lookup: {"action":"workbook.get","arguments":{"name":"Exact workbook name"}}. ' 'Use either name or workbook_id, never both. ' 'Read cells: {"action":"read_cells","arguments":{"workbook_id":"returned-id","tab":"returned-tab-id"}}. ' 'Refresh writes need intent and about beside action and arguments. ' 'A missing or hidden workbook returns workbook_missing. Readable workbooks return their current tabs.') def write_guide(): """Publish the metadata, argument shape and signed confirmation required for Sheet writes.""" return ( "Every Sheet write needs intent and about beside action and arguments. " "If your client sends only action and arguments, put intent and about inside arguments. " "Use intent as 1 to 500 characters explaining the write. " "Use about with only title (1 to 240 characters), description (20 to 240 characters), " "and tags (one or more distinct values). Use no control characters or long dashes in title or description. " "Allowed tags: " + ", ".join(external_tools.BUILD_TAGS) + ". " "Use workbook_id and tab from workbook.get. Call sheet with " + json.dumps(WRITE_CALL_EXAMPLE, separators=(", ", ": ")) + ". " "The write returns preview_ready without changing cells. After the user approves, call confirm_change " 'with {"confirmation_token": "", "intent": "Apply the approved Sheet write"}. ' "Confirmation uses the saved about." ) def _workbook_view(ctx, args): """Project native workbook cells without exposing bindings or private workbook metadata.""" import copy from fastapi import HTTPException from core import sheets ctx.session.require("sheets") book = external_tools._book(ctx, args) reason = "" try: ctx.require_editor(book) except (HTTPException, sheets.SheetRefusal): reason = "You can read this workbook, but you cannot edit it." access, _ = external_access.scope_of({ "access": ctx.session.claims.get("external_access"), "databases": ctx.session.claims.get("external_databases")}) if access != "readwrite": reason = "This connection can read cells, but it cannot edit them." projected = ctx.project_book(book) tabs = [] for tab in projected.get("sheets", []): cells = {} for addr, cell in tab.get("cells", {}).items(): kind = cell.get("kind") blocked = reason if kind not in ("literal", "empty") or cell.get("spillParent"): blocked = blocked or "This cell is calculated. Edit its source in Loopable." # Placed database views are outside this resource's contract. binding = cell.get("binding") or {} hidden = cell.get("state") == "refused" or kind == "bound" and binding.get("mode") == "view" row = {"value": None if hidden else cell.get("value"), "display": "" if hidden else cell.get("display", ""), "editable": not bool(blocked), "reason": blocked} if not blocked: row["edit"] = {key: copy.deepcopy(cell[key]) for key in ("kind", "value", "text", "style", "validation", "link", "note") if key in cell} cells[addr] = row tabs.append({"id": tab["id"], "name": tab["name"], "cells": cells}) return {"workbook": {"id": book["id"], "name": book["name"], "rev": book["rev"], "editable": not bool(reason), "reason": reason, "sheets": tabs}} def _object_available(operation): """Promise only object actions implemented by the compatibility dispatcher.""" from core import sheet_compat return (callable(getattr(sheet_compat, "dispatch_object_operation", None)) and operation in sheet_compat.OBJECT_OPERATIONS) GROUP = { "name": "sheet", "title": "Sheets", "guide": ("Read and change workbooks, tabs, cells, styles and charts. " + external_tools.CELL_FIGURE_RULE + " Read loopable_guide(group='sheet') for actions."), "actions": { name.removeprefix("sheet_"): legacy_action( name, destructive=name in {"sheet_delete_tab", "sheet_write_cells", "sheet_structure"}) for name in external_tools.TOOLS if name.startswith("sheet_") }, } for name, actions in external_tools._SHEET_OBJECT_ACTIONS.items(): for action in actions: GROUP["actions"][name.removesuffix("_object") + "." + action] = legacy_action( name, action=action, read=action in external_tools._OBJECT_READ_ACTIONS[name], destructive=action in {"delete", "clear"}) operation = name.removesuffix("_object") + "." + action GROUP["actions"][operation]["available"] = lambda operation=operation: _object_available(operation) GROUP["actions"]["read_batch"] = legacy_action("validated_batch", read=True) GROUP["actions"]["write_batch"] = legacy_action("validated_batch", read=False, destructive=True) for name, handler, properties in ( ("workbook.list", workbook_list, { "limit": {"type": "integer", "minimum": 1, "maximum": 500}, "cursor": {"type": "string", "minLength": 1}}), ("workbook.get", workbook_get, { "workbook_id": {"type": "string", "minLength": 1}, "name": {"type": "string", "minLength": 1}})): # The released D-9630 shape stays accepted: the verb inside arguments, its options nested. properties = {**properties, "action": {"enum": [name.rsplit(".", 1)[-1]]}, "arguments": {"type": "object"}} GROUP["actions"][name] = { "read": True, "destructive": False, "confirm": False, "grants": ("sheets",), "handler": handler, "schema": {"type": "object", "description": handler.__doc__, "properties": properties, "additionalProperties": False}, "available": lambda operation=name: _object_available(operation), } GROUP["actions"]["workbook.get"]["example"] = {"workbook_id": ""} GROUP["actions"]["workbook.view"] = { "read": True, "destructive": False, "confirm": True, "grants": ("sheets",), "handler": _workbook_view, "_meta": {"ui": {"resourceUri": WORKBOOK_RESOURCE_URI}}, "schema": {"type": "object", "description": "Open one workbook in the workbook view.", "properties": {"workbook_id": {"type": "string", "minLength": 1}}, "required": ["workbook_id"], "additionalProperties": False}, } GROUP["guide"] += " Use workbook.view with workbook_id to open the workbook view." GROUP["guide"] += ( " Use workbook.list with optional limit and cursor until cursor is null to find all readable workbooks. " "Use workbook.get with either workbook_id or an exact name to read projected tabs and cells. " "Duplicate exact names require an id. Near names return at most five readable suggestions. " "Use returned workbook and tab ids for read_cells. " "Actions marked not supported here refuse before writing. Their schemas do not promise support. " "Use create_workbook for workbook.create, add_tab for worksheet.add, and delete_tab for worksheet.delete. " "Use workbook.get for worksheet.list or worksheet.get, and read_cells for range.read. " "Use write_cells for range.write_values, range.write_formulas or range.clear. " "Use set_styles for range.write_styles or range.number_format, and set_dimensions for worksheet.geometry. " "Use worksheet.rename, worksheet.reorder or worksheet.duplicate where available. " "Use refresh_scope for calculation.refresh. Refresh can contact sources and requires confirmation. " "Use write_batch for a checked sequence of supported writes. " "Workbook export and named ranges have no grouped replacement in this build.") GROUP["guide"] += ' Under MCP rule 9, ask "Do you want me to build a Sheet workbook in Loopable?".' GROUP["guide"] += " " + write_guide() _BATCH_GUIDE = ( " A batch has 1 to 50 operations for one workbook. Use name and arguments for each operation. " "Use sheet_add_tab, sheet_write_cells, sheet_set_binding, sheet_set_styles or sheet_set_dimensions " "with their published arguments. A sheet_write_cells preview of 250 or more changes shows counts, ranges and a sample. confirm_change writes every cell. " "Range actions use name='range_object', with action, workbook_id, worksheet and an A1 range. " "Put values inside arguments for write_values, write_formulas, write_styles or number_format. " "For worksheet_object.add, put name inside arguments. For worksheet_object.geometry, " "put column_width or row_height inside arguments and supply a range. " "Later operations can name a newly added tab. After sheet_create_workbook, omit workbook_id " "to use that workbook. Each supplied rev or revision refers to the workbook before the batch. " "All operations are checked before one preview. confirm_change saves one workbook revision. " "If any operation fails, nothing changes and the reply names its position. " "Direct number writes keep the same advice and optional figure_sources as write_cells." ) for action in ("read_batch", "write_batch"): GROUP["actions"][action]["schema"]["description"] = _BATCH_GUIDE.strip() def _capability(name): """Resolve C8 at call time so an absent lane never prevents group discovery.""" from core import sheets return getattr(sheets, name, None) def _write_book(ctx, args): """Admit the current editor before comparing the workbook revision.""" from core import sheets ctx.session.require("sheets") book = external_tools._book(ctx, args, write=True) if args["rev"] != book["rev"]: raise sheets.SheetStaleRefusal( "workbook_revision_stale", "The workbook changed. Read it and preview the change again.", rev_current=book["rev"]) return book def _binding_scope(ctx, book): """Refuse excluded source bindings before a provider can follow workbook dependencies.""" from core import sheets claims = ctx.session.claims _, databases = external_access.scope_of({ "access": claims.get("external_access"), "databases": claims.get("external_databases")}) for tab in book.get("sheets", []): for cell in tab.get("cells", {}).values(): binding = cell.get("binding") if isinstance(cell, dict) else None if isinstance(binding, dict) and not external_access.database_allowed(databases, binding.get("database")): raise sheets.SheetRefusal( "key_database_out_of_scope", "This connection cannot access a database used by this workbook.") def refresh_sources(session, book): """Check stored source dependencies without resolving or changing any cells.""" import routes_sheet from core import sheets from modules import sheet as sheet_mod _, databases = external_access.scope_of({ "access": session.claims.get("external_access"), "databases": session.claims.get("external_databases")}) bindings = [] for tab in book.get("sheets", []): for cell in tab.get("cells", {}).values(): if cell.get("kind") != "bound": continue try: binding = sheet_mod.clean_binding(cell.get("binding")) except sheet_mod.SheetRefusal as exc: raise sheets.SheetRefusal(exc.code, exc.message) from exc database = binding["database"] if not external_access.database_allowed(databases, database): raise sheets.SheetRefusal("key_database_out_of_scope", "This connection cannot access a database used by this workbook.") routes_sheet._require_database(session, database) nodes, _ = routes_sheet._binding_filter_nodes(session, database, binding) for via in sheet_mod.related_vias(nodes): target = routes_sheet._binding_related_entry(session, database, via)["target"] if not external_access.database_allowed(databases, target): raise sheets.SheetRefusal("key_database_out_of_scope", "This connection cannot access a database used by this workbook.") bindings.append((binding, tab["id"])) return bindings def refresh_access(ctx, args): """Admit the workbook and every source before a refresh can stage any changes.""" book = _write_book(ctx, args) for binding, tab_id in refresh_sources(ctx.session, book): # Resolve through the native row and field walls without saving a snapshot. ctx.resolve_binding(binding, book, tab_id) return book def _rename(ctx, args): """Rename through the compatibility contract and native revision guard.""" from core import sheet_compat, sheets book = _write_book(ctx, args) tab = sheets.find_tab(book, args["tab"]) return sheet_compat.execute(ctx.session.runtime, book["id"], "worksheet.rename", { "sheet": tab["id"], "name": args["name"], "expected_revision": args["rev"]}, actor=ctx.session.uname, actor_name=ctx.actor_name) def _foreign(ctx, args, name): """Call the exact C8 signature with the admitted runtime and current actor.""" from core import sheets book = _write_book(ctx, args) function = _capability(name) if not callable(function): reason = {"reorder_tab": "Moving tabs is not available in this build.", "duplicate_tab": "Copying tabs is not available in this build.", "refresh_scope": "Refreshing cells is not available in this build."}[name] raise sheets.SheetRefusal("capability_unavailable", reason) if name == "refresh_scope": if ctx.session.claims.get("external_access") == external_access.ACCESS_REFRESH: refresh_access(ctx, args) else: _binding_scope(ctx, book) return function(ctx.session.runtime, book["id"], args["scope"], ctx.session) tab = sheets.find_tab(book, args["tab"]) if name == "duplicate_tab": _binding_scope(ctx, book) return function(ctx.session.runtime, book, tab["id"], args["name"], ctx.session) return function(ctx.session.runtime, book, tab["id"], args["index"], ctx.session) def _receipt_access(ctx, args): """Read current workbook access without invoking a writer on receipt replay.""" ctx.session.require("sheets") external_tools._book(ctx, args) return True def _write_action(description, properties, required, handler, capability=None): entry = {"read": False, "destructive": False, "confirm": True, "grants": ("sheets",), "handler": handler, "receipt_access": _receipt_access, "schema": {"type": "object", "description": description + " Preview first, then call confirm_change.", "properties": { "workbook_id": {"type": "string", "minLength": 1}, "rev": {"type": "integer", "minimum": 0, "description": "The workbook revision from your latest read."}, **properties}, "required": ["workbook_id", "rev", *required], "additionalProperties": False}} if capability: entry["available"] = lambda: callable(_capability(capability)) return entry _TAB = {"type": "string", "minLength": 1, "description": "The tab id or current name."} _NAME = {"type": "string", "minLength": 1, "maxLength": 120, "pattern": "^[^']+$"} _SCOPE = {"oneOf": [ {"type": "object", "properties": {"cell": { "type": "string", "pattern": "^.{1,80}![A-Z]{1,3}[1-9][0-9]{0,6}$", "description": "A stable tab id and cell address, such as tab-id!A1."}}, "required": ["cell"], "additionalProperties": False}, {"type": "object", "properties": {"range": { "type": "string", "pattern": "^.{1,80}![A-Z]{1,3}[1-9][0-9]{0,6}:[A-Z]{1,3}[1-9][0-9]{0,6}$", "description": "A stable tab id and inclusive range, such as tab-id!A1:B3."}}, "required": ["range"], "additionalProperties": False}, {"type": "object", "properties": {"workbook": {"const": True}}, "required": ["workbook"], "additionalProperties": False}, ]} GROUP["actions"].update({ "worksheet.rename": _write_action("Rename a tab and update formulas that name it.", {"tab": _TAB, "name": _NAME}, ("tab", "name"), _rename), "worksheet.reorder": _write_action("Move a tab to a zero-based final position.", {"tab": _TAB, "index": {"type": "integer", "minimum": 0}}, ("tab", "index"), lambda ctx, args: _foreign(ctx, args, "reorder_tab"), "reorder_tab"), "worksheet.duplicate": _write_action("Copy a tab with its formulas, styles and cell databases.", {"tab": _TAB, "name": _NAME}, ("tab", "name"), lambda ctx, args: _foreign(ctx, args, "duplicate_tab"), "duplicate_tab"), "refresh_scope": _write_action("Refresh one cell, one range or the workbook and its dependent cells.", {"scope": _SCOPE}, ("scope",), lambda ctx, args: _foreign(ctx, args, "refresh_scope"), "refresh_scope"), })