loopable / web /src /sheet /functionCatalog.ts
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
bdac669 verified
Raw History Blame Contribute Delete
12.6 kB
// The formula function catalog, wave 52 (W52-T05, contract C3, instruction 5).
//
// This file owns ONE job: the local function data the suggestion dropdown, the ScreenTip
// (`formulaAssist.ts`) and the Formulas ribbon's offline fallback draw from. It is PURE data.
// Category names match the server catalogue so fallback never changes the picker's shape. No
// React, no DOM, nothing computed at runtime that a later ticket cannot re-derive from a formula's
// own text.
//
// ⛔ SIGNATURE SPACING IS THE OWNER'S OWN EXAMPLE, NOT MICROSOFT'S. Microsoft's SUM reference page
// prints `SUM(number1,[number2],...)` with no space after the comma. The owner's instruction 5
// spelled it "SUM(number1, [number 2], ...)" -- WITH a space after every comma. `ST-2/ST-3` in
// `AutoWiki/waves/wave52/proto/excel-behaviour-spec.md` says match the owner's spacing exactly.
// `buildSignature` below is the ONLY place that assembles a signature string, so every entry gets
// the same spacing by construction rather than by sixty hand-typed strings agreeing by luck.
//
// ⛔ NO EM DASH (U+2014) OR EN DASH (U+2013) IN ANY STRING HERE. `summary` reaches a screen the
// moment T06 renders the dropdown row (FA-15). Standing rule 2. `verify_formula_engine.py`'s new
// leg asserts this the same way the wave-50 catalogue leg already asserts it for the grid engine.
/** One argument in a function's signature. `optional` renders it in `[brackets]` (ST-3). */
export interface FunctionArg {
name: string;
optional?: boolean;
}
/**
* `SUM(number1, [number2], ...)`.
*
* Bare = required. `[bracketed]` = optional (display only -- the brackets are never typed or
* committed). A trailing `, ...` means the last optional argument repeats (ST-3), up to whatever
* cap the function itself enforces; this catalog does not encode that cap, only the shape.
*/
function buildSignature(name: string, args: FunctionArg[], repeats = false): string {
const parts = args.map((a) => (a.optional ? `[${a.name}]` : a.name));
let body = parts.join(", ");
if (repeats) body += body ? ", ..." : "...";
return `${name}(${body})`;
}
export interface FunctionSpec {
name: string;
category: string;
signature: string;
summary: string;
argNames: string[];
}
/** One row per catalog entry: name, its arg list, whether the last optional argument repeats. */
interface FunctionDef {
name: string;
category: string;
args: FunctionArg[];
repeats?: boolean;
summary: string;
}
// ⚠ ORDERING HERE IS DELIBERATELY NOT ALPHABETICAL FOR EVERY PREFIX GROUP (the M group in
// particular). `suggest()` in formulaAssist.ts is the thing that must sort these into A-Z order
// (FA-8); a catalog that is already alphabetical would let a broken sort pass by accident.
const FUNCTION_DEFS: FunctionDef[] = [
{
name: "SUMPRODUCT", category: "Math",
args: [{ name: "array1" }, { name: "array2", optional: true }], repeats: true,
summary: "Adds the products of corresponding values in arrays.",
},
{
name: "ISTEXT", category: "Information", args: [{ name: "value" }],
summary: "Returns true when a value is text.",
},
{
name: "WEEKNUM", category: "Date",
args: [{ name: "date" }, { name: "return_type", optional: true }],
summary: "Returns the week number with weeks beginning on Sunday or Monday.",
},
{
name: "CONCATENATE", category: "Text",
args: [{ name: "text1" }, { name: "text2", optional: true }], repeats: true,
summary: "Joins text values together.",
},
{
name: "YEAR", category: "Date", args: [{ name: "date" }],
summary: "Returns the year of a date.",
},
{
name: "MONTH", category: "Date", args: [{ name: "date" }],
summary: "Returns the month number of a date.",
},
{
name: "FILTER", category: "Lookup",
args: [{ name: "array" }, { name: "include" }, { name: "if_empty", optional: true }],
summary: "Returns the rows or columns that meet a condition.",
},
{
name: "SORT", category: "Lookup",
args: [{ name: "array" }, { name: "sort_index", optional: true },
{ name: "sort_order", optional: true }, { name: "by_col", optional: true }],
summary: "Returns an array sorted by a row or column.",
},
{
name: "OFFSET", category: "Lookup",
args: [{ name: "reference" }, { name: "rows" }, { name: "cols" },
{ name: "height", optional: true }, { name: "width", optional: true }],
summary: "Returns a reference shifted by a number of rows and columns.",
},
{
name: "INDIRECT", category: "Lookup",
args: [{ name: "ref_text" }, { name: "a1", optional: true }],
summary: "Returns the reference named by a text value.",
},
{
name: "RAND", category: "Math", args: [],
summary: "Returns a random number between zero and one.",
},
{
name: "SUM",
category: "Math",
args: [{ name: "number1" }, { name: "number2", optional: true }],
repeats: true,
summary: "Adds all the numbers in a range of cells.",
},
{
name: "SUMIF",
category: "Math",
args: [
{ name: "range" },
{ name: "criteria" },
{ name: "sum_range", optional: true },
],
summary: "Adds the cells specified by a given condition.",
},
{
name: "MID",
category: "Text",
args: [{ name: "text" }, { name: "start_num" }, { name: "num_chars" }],
summary: "Returns characters from the middle of a text string.",
},
{
name: "MATCH",
category: "Lookup",
args: [
{ name: "lookup_value" },
{ name: "lookup_array" },
{ name: "match_type", optional: true },
],
summary: "Returns the relative position of an item in a range.",
},
{
name: "MIN",
category: "Statistical",
args: [{ name: "number1" }, { name: "number2", optional: true }],
repeats: true,
summary: "Returns the smallest number in a set of values.",
},
{
name: "MAX",
category: "Statistical",
args: [{ name: "number1" }, { name: "number2", optional: true }],
repeats: true,
summary: "Returns the largest number in a set of values.",
},
{
name: "AVERAGE",
category: "Statistical",
args: [{ name: "number1" }, { name: "number2", optional: true }],
repeats: true,
summary: "Returns the average of a set of numbers.",
},
{
name: "AND",
category: "Logical",
args: [{ name: "logical1" }, { name: "logical2", optional: true }],
repeats: true,
summary: "Returns TRUE when every argument is true.",
},
{
name: "OR",
category: "Logical",
args: [{ name: "logical1" }, { name: "logical2", optional: true }],
repeats: true,
summary: "Returns TRUE when any argument is true.",
},
{
name: "NOT",
category: "Logical",
args: [{ name: "logical" }],
summary: "Reverses the logic of its one argument.",
},
{
name: "COUNT",
category: "Statistical",
args: [{ name: "value1" }, { name: "value2", optional: true }],
repeats: true,
summary: "Counts the numbers in a list of values.",
},
{
name: "COUNTA",
category: "Statistical",
args: [{ name: "value1" }, { name: "value2", optional: true }],
repeats: true,
summary: "Counts the non blank values in a list.",
},
{
name: "COUNTIF",
category: "Statistical",
args: [{ name: "range" }, { name: "criteria" }],
summary: "Counts the cells that meet a given condition.",
},
{
name: "IF",
category: "Logical",
args: [
{ name: "logical_test" },
{ name: "value_if_true" },
{ name: "value_if_false", optional: true },
],
summary: "Returns one value if a condition is true and another if false.",
},
{
name: "IFERROR",
category: "Logical",
args: [{ name: "value" }, { name: "value_if_error" }],
summary: "Returns a fallback value when the first argument is an error.",
},
{
name: "XLOOKUP",
category: "Lookup",
args: [
{ name: "lookup_value" },
{ name: "lookup_array" },
{ name: "return_array" },
{ name: "if_not_found", optional: true },
{ name: "match_mode", optional: true },
],
summary: "Finds a value in one range and returns the matching value from another, with an answer of your own when nothing matches.",
},
{
name: "VLOOKUP",
category: "Lookup",
args: [
{ name: "lookup_value" },
{ name: "table_array" },
{ name: "col_index_num" },
{ name: "range_lookup", optional: true },
],
summary: "Looks up a value in the first column and returns a value in the same row.",
},
{
name: "INDEX",
category: "Lookup",
args: [
{ name: "array" },
{ name: "row_num" },
{ name: "column_num", optional: true },
],
summary: "Returns a value at a given position in a range.",
},
{
name: "CHOOSE",
category: "Lookup",
args: [{ name: "index_num" }, { name: "value1" }, { name: "value2", optional: true }],
repeats: true,
summary: "Returns the value at the position you name in a list.",
},
{
name: "CONCATENATE",
category: "Text",
args: [{ name: "text1" }, { name: "text2", optional: true }],
repeats: true,
summary: "Joins several text values into one text value.",
},
// W57-T07: offer only server-backed text functions, with hints built by buildSignature.
{
name: "TEXTJOIN",
category: "Text",
args: [
{ name: "delimiter" },
{ name: "ignore_empty" },
{ name: "text1" },
{ name: "text2", optional: true },
],
repeats: true,
summary: "Joins text values with a delimiter between them, optionally skipping the empty ones.",
},
{
name: "SUBSTITUTE",
category: "Text",
args: [
{ name: "text" },
{ name: "old_text" },
{ name: "new_text" },
{ name: "instance_num", optional: true },
],
summary: "Replaces old text with new text in a text value.",
},
{
name: "ROUND",
category: "Math",
args: [{ name: "number" }, { name: "num_digits" }],
summary: "Rounds a number to a given number of digits.",
},
{
name: "ABS",
category: "Math",
args: [{ name: "number" }],
summary: "Returns the absolute value of a number.",
},
{
name: "TRIM",
category: "Text",
args: [{ name: "text" }],
summary: "Removes extra spaces from a text value.",
},
{
name: "LEN",
category: "Text",
args: [{ name: "text" }],
summary: "Returns the number of characters in a text value.",
},
{
name: "LEFT",
category: "Text",
args: [{ name: "text" }, { name: "num_chars", optional: true }],
summary: "Returns the leftmost characters from a text value.",
},
{
name: "RIGHT",
category: "Text",
args: [{ name: "text" }, { name: "num_chars", optional: true }],
summary: "Returns the rightmost characters from a text value.",
},
// W57-T07: these date functions return ISO dates or counts on both sides of Refresh.
{
name: "EOMONTH",
category: "Date",
args: [{ name: "date" }, { name: "months" }],
summary: "Returns the last day of the month a number of months away from a date.",
},
{
name: "WORKDAY",
category: "Date",
args: [{ name: "start_date" }, { name: "days" }, { name: "holidays", optional: true }],
summary: "Returns the date a number of working days away from a date, skipping weekends and any holidays you name.",
},
{
name: "NETWORKDAYS",
category: "Date",
args: [{ name: "start_date" }, { name: "end_date" }, { name: "holidays", optional: true }],
summary: "Counts the working days between two dates, skipping weekends and any holidays you name.",
},
{
name: "TODAY",
category: "Date",
args: [],
summary: "Returns the current date.",
},
{
name: "NOW",
category: "Date",
args: [],
summary: "Returns the current date and time.",
},
];
export const FUNCTIONS: FunctionSpec[] = FUNCTION_DEFS.map((def) => ({
name: def.name,
category: def.category,
signature: buildSignature(def.name, def.args, def.repeats),
summary: def.summary,
argNames: def.args.map((a) => a.name),
}));
/** True when `signature`'s last optional argument repeats (a trailing `, ...` or `...`). ST-3/ST-6. */
export function signatureRepeats(signature: string): boolean {
return signature.trimEnd().endsWith("...)");
}
/** Look up one catalog entry by name, case insensitive. Returns undefined when unknown. */
export function findFunction(name: string): FunctionSpec | undefined {
const upper = String(name || "").toUpperCase();
return FUNCTIONS.find((f) => f.name === upper);
}