loopable / api /tool_groups /sheet.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
7f72224 verified
Raw History Blame
22.2 kB
"""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": "<workbook_id>", "tab": "<tab_id>",
"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": "<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": "<returned signed 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": "<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"),
})