Download web/src/sheet/functionCatalog.ts from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 12.6 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/ffcfecbfac39d8ec1306594485fb9b341e952086/web/src/sheet/functionCatalog.ts
- Command line
-
hf download hf://spaces/fsanyoto/loopable@ffcfecbfac39d8ec1306594485fb9b341e952086/web/src/sheet/functionCatalog.ts
-
curl -L -o functionCatalog.ts https://huggingface.co/spaces/fsanyoto/loopable/resolve/ffcfecbfac39d8ec1306594485fb9b341e952086/web/src/sheet/functionCatalog.ts
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); | |
| } | |