Spreadsheet Formula (Basic).sublime-syntax

%YAML 1.2
---
name: Spreadsheet Formula
# Made by _alco and @michaelblyons. Thanks to acid_form for the first key bug reports we've received and for the updated builtin function list with Google Sheets functions added.
# Repo: https://github.com/axemonk/Excel-formula
# Please send any bug reports and suggestions to _alco via a direct message on Discord.
# You can find me on the official Sublime Text Discord server.
# v2.5.2 1/5/2025
file_extensions: []
scope: source.sheet.basic
hidden: true
version: 2


contexts:

  main:
    # Matches assignment operator '='
    - match: ^=
      scope: keyword.operator.assignment.sheet
    - include: comments-text-marker
    - include: expressions

  comments-text-marker:
    # Pretend that the weird plain-text marker Excel uses is a comment
    - match: ^\'
      scope: punctuation.definition.comment.begin.sheet
      # scope: punctuation.definition.string.begin.sheet
      push:
        - meta_scope: comment.line.single-quote.sheet
        # - meta_scope: string.unquoted.sheet
        - match: $\n?
        # - match: $
          pop: 1

  expressions:
    - include: function-calls
    - include: operators
    - include: references
    - include: constants
    - include: arrays
    - include: groups

    # Check for stray brackets
    - match: '[)}\]]'
      scope: invalid.illegal.stray-bracket-end.sheet
    # Match user-defined names
    - match: '{{name}}'
      scope: variable.other.readwrite.sheet
      push: after-reference

  operators:
    # three or more is wrong
    - match: '[<>=]{3,}'
      scope: invalid.illegal.sheet
    # illegal two-character operator combinations
    - match: (?:=[=<>]|>[><]|<<)
      scope: invalid.illegal.sheet
    # anything other combo
    - match: '[<>=]+'
      scope: keyword.operator.logical.sheet

    # Matches mathematical operators
    - match: '[+\-*/^%]'
      scope: keyword.operator.arithmetic.sheet

    - match: \&
      scope: keyword.operator.concatenation.sheet

###[ FUNCTIONS ]###############################################################

  function-calls:

    # Update: Give logical comparator functions an extra scope.
    # NOTE:   Should the scope be specific to each of these?
    #         conditional.ifs, conditional.switch etc.
    #         or just conditional.switch and conditional.if for everything else?
    - match: \b((?i:IF|IFS|IFERROR|IFNA|SWITCH))(\()
      captures:
        1: meta.function-call.sheet support.function.sheet keyword.control.conditional.if.sheet
        2: meta.function-call.arguments.sheet punctuation.section.arguments.begin.sheet
      push: function-call-body

    # Update: Give logical comparator functions an extra scope.
    # NOTE:   What about ISERROR ISNUMBER ISTEXT? (there are like a dozen IS... functions)
    #         My gut tells me to leave them as functions. They are very different in behavior.
    #         AND/OR/NOT/XOR convert one or more TRUE/FALSE values to a single TRUE/FALSE value
    #         IS... functions convert one or more values into one or more TRUE/FALSE values
    - match: \b((?i:AND|OR|NOT|XOR))(\()
      captures:
        1: meta.function-call.sheet support.function.sheet keyword.operator.logical.sheet
        2: meta.function-call.arguments.sheet punctuation.section.arguments.begin.sheet
      push: function-call-body

    # # TODO: LAMBDA's *last* argument is a calc. Previous args are parameters.
    # #   I don't imagine there's a good way to identify that without branching.
    # # https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67

    # # TODO: LET's *last* argument is a calc. Previous args are pairs of variable/assignment.
    # #   I don't imagine there's a good way to identify that without branching.
    # # https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999
    # - match: \b((?:LET))(\()
    #   captures:
    #     1: meta.function-call.sheet keyword.declaration.sheet
    #     2: meta.function-call.arguments.sheet punctuation.section.arguments.begin.sheet
    #   push: [let-declarations-body, function-call-body]

    # Builtins
    - match: \b({{function_builtins}})(\()
      captures:
        1: meta.function-call.sheet support.function.sheet
        2: meta.function-call.arguments.sheet punctuation.section.arguments.begin.sheet
      push: function-call-body

    - include: function-builtins-extra

    - match: '({{function}})(\()'
      captures:
        1: meta.function-call.sheet variable.function.sheet
        2: meta.function-call.arguments.sheet punctuation.section.arguments.begin.sheet
      push: function-call-body

  function-call-body:
    - meta_content_scope: meta.function-call.arguments.sheet
    - match: \)
      scope: meta.function-call.arguments.sheet punctuation.section.arguments.end.sheet
      pop: 1
    - match: '{{argument_separator}}'
      scope: punctuation.separator.sequence.sheet
    - include: expressions

  # let-declarations-body:
  #   # If you see a function call, pop to normal function call from declaration
  #   # BUG: That's not how LET works.
  #   - match: (?={{function_name}}\()
  #     pop: 1
  #   - include: references
  #   - match: \w+
  #     scope: variable.other.readwrite.sheet

  function-builtins-extra: []

###[ REFERENCES ]##############################################################

  sheet-reference:
    - match: (?={{sheet}}!)
      push: sheet-reference-body

  sheet-reference-body:
    - meta_scope: meta.reference.sheet
    - match: \!
      scope: punctuation.separator.sequence.sheet
      pop: 1
    - match: \'
      scope: punctuation.definition.annotation.begin.sheet
      push:
        - meta_content_scope: entity.name.struct.sheet
        - match: \'\'
          scope: constant.character.escape.sheet
        - match: \'(?=!)
          scope: punctuation.definition.annotation.end.sheet
          pop: 1
    - match: '{{sheet}}'
      scope: entity.name.struct.sheet
    - include: immediately-pop

  cell-or-range-reference:

    # Cell range
    # A1:B2
    - match: ({{cell}})(:)({{cell}})(?!#)
      scope: meta.reference.range.cell.sheet
      captures:
        1: storage.type.sheet
        2: punctuation.separator.sequence.sheet
        3: storage.type.sheet
      push: after-reference

    # Naked cell reference
    # A1
    - match: ({{cell}})(?![:#])
      scope: meta.reference.cell.sheet storage.type.sheet
      push: after-reference

    # Column to column
    # A:B
    - match: ({{column}})(:)({{column}})(?![0-9])
      scope: meta.reference.range.column.sheet
      captures:
        1: storage.type.sheet
        2: punctuation.separator.sequence.sheet
        3: storage.type.sheet
      push: after-reference

    # Row to row
    # 1:2
    - match: ({{row}})(:)({{row}})
      scope: meta.reference.range.row.sheet
      captures:
        1: storage.type.sheet
        2: punctuation.separator.sequence.sheet
        3: storage.type.sheet
      push: after-reference

  after-reference:
    # Bail immediately
    - include: immediately-pop

  table-name:
    # Matches table name references
    - match: ([\w.?\\]+)?(\[)
      captures:
        1: entity.name.struct.sheet
        2: punctuation.section.brackets.begin.sheet
      push: table-name-body

  table-name-body:
    - meta_scope: meta.reference.sheet

    # The second group ensures commas and colons can still be used in column names
    # without mismatching them elsewhere
    # TODO: Do any of these need to include {{argument_separators}}?
    - match: (\])([,:])?
      captures:
        1: punctuation.section.brackets.end.sheet
        2: punctuation.separator.sequence.sheet
      pop: 1

    # TODO: Do any of these need to include {{argument_separators}}?
    - match: (?:[,:]*)('[\[\]@#])(?:[,:]*)
      scope: storage.type.sheet
      captures:
        1: constant.character.escape.sheet

    - match: \[
      scope: punctuation.section.brackets.begin.sheet
      push: table-name-body

    # Anything that shouldn't match but could otherwise be within the body
    # Technically, we could do `.`, but that slows down tokenization
    - match: '[^#@\[\]]+'
      scope: storage.type.sheet

  references:
    # # These four were tricky when reworking the scopes to be in line with convention.
    # #
    # # I tried to balance actually having them colored differently in the default sublime color scheme,
    # # as is helpful to have in a tightly-packed Excel formula, while at the same time
    # # having the scope names align with what these various references represent from a code perspective.
    # #
    # # This proved to be *very* difficult for cell and column references (see cop-out: storage.type.excel),
    # # but for sheet and table name references, .struct. seems to fit well under the definition:
    # #
    # # # struct: "a composite data type (or record) declaration that defines a physically grouped list of variables
    # # #          under one name in a block of memory"

    # Cell must come before table for R1C1 references to match
    - include: cell-or-range-reference
    - include: table-name
    - include: sheet-reference

###[ GROUPS ]##################################################################

  groups:
    - match: \(
      scope: punctuation.section.group.begin.sheet
      push: group-body

  group-body:
    - meta_scope: meta.group.sheet
    - match: \)
      scope: punctuation.section.group.end.sheet
      pop: 1
    - include: expressions

###[ ARRAYS ]##################################################################

  arrays:
    - match: \{
      scope: punctuation.section.braces.begin.sheet
      push: array-body

  array-body:
    - meta_scope: meta.array.sheet
    - match: \}
      scope: punctuation.section.braces.end.sheet
      pop: 1
    - match: '{{array_column_separator}}'
      scope: punctuation.separator.sequence.column.sheet
    - match: '{{array_row_separator}}'
      scope: punctuation.separator.sequence.row.sheet
    - include: expressions

###[ LITERALS ]################################################################

  constants:
    - match: '#NULL!'
      scope: constant.language.null.sheet

    - match: (?:#NUM!|#DIV/0!)
      scope: constant.language.nan.sheet

    - match: (?:#VALUE!|#REF!|#NAME\?|#GETTING_DATA|#N/A)
      scope: constant.language.sheet

    - match: \b(?i)TRUE\b
      scope: constant.language.boolean.true.sheet

    - match: \b(?i)FALSE\b
      scope: constant.language.boolean.false.sheet

    - include: numbers

    # TODO: only include wildcards where appropriate
    - include: strings-with-wildcards

    # Here is a list of the most common functions that allow wildcards:
    # AVERAGEIF, AVERAGEIFS
    # COUNTIF, COUNTIFS
    # MAXIFS, MINIFS
    # SUMIF, SUMIFS
    # VLOOKUP, HLOOKUP
    # MATCH
    # SEARCH


  # Update: Revert previous change regarding commas as decimal separators. Makes unit tests too confusing.
  numbers:
    - match: -?\d+({{number_radix}})\d*|-?({{number_radix}})\d+
      scope: meta.number.float.decimal.sheet constant.numeric.value.sheet
      captures:
        1: punctuation.separator.decimal.sheet
        2: punctuation.separator.decimal.sheet
    - match: -?\d+
      scope: meta.number.integer.decimal.sheet constant.numeric.value.sheet

  strings:
    - match: '"'
      scope: punctuation.definition.string.begin.sheet
      push: string-body

  string-body:
    - meta_include_prototype: false
    - meta_scope: string.quoted.double.sheet

    - match: '""'
      scope: constant.character.escape.sheet
    - match: '"'
      scope: punctuation.definition.string.end.sheet
      pop: 1

  strings-with-wildcards:
    - match: '"'
      scope: punctuation.definition.string.begin.sheet
      push: string-body-with-wildcards

  string-body-with-wildcards:
    - meta_include_prototype: false
    - meta_scope: string.quoted.double.sheet

    # Tilde escapes wildcards and itself, but can also be used by itself and
    # functions as a literal
    - match: ~[~?*]
      scope: constant.character.escape.sheet
    - match: \*
      scope: constant.other.wildcard.asterisk.sheet
    - match: \?
      scope: constant.other.wildcard.questionmark.sheet
    - include: string-body

###[ PROTOTYPES ]##############################################################

  immediately-pop:
    - match: ''
      pop: 1

###############################################################################

variables:
  array_column_separator: ;
  array_row_separator: \,
  number_radix: \.
  argument_separator: \,

  # Max col index:
  # # Excel & Libre: XFD (16,384)
  # # Google Sheets: ZZZ (18,278)
  column: (?i:\$?[a-z]{1,3})
  # Row numbers do not include international numbers
  # Max row index:
  # # Excel & Libre:  1,048,576
  # # Google Sheets: 10,000,000
  row: (?:\$?[1-9][0-9]{0,7})
  # Negative lookahead prevents matching names with numbers in them.
  cell: (?:{{column}}{{row}}(?!{{name}}))

  # Note to self: Underscore and 0-9 is included in \w
  # TODO:         Check exceptions to these in the VBA-like systems of Sheets and Libre, then check exceptions in VBA.
  name: (?i:[a-z_][a-z0-9_]*)
  function: (?:{{name}})
  table: (?:{{name}})
  sheet: (?:{{name}})

  # Update:
  # MS Excel:
  # - https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
  # Google Sheets:
  # - https://support.google.com/docs/table/25273
  # As of 12/27/2024
  #
  # LibreOffice Calc:
  # - https://wiki.documentfoundation.org/Documentation/Calc_Functions/List_of_Functions
  # As of 1/14/2025

  function_builtins: |-
    \b(?xi:
      ABS | ACCRINT | ACCRINTM | ACOS | ACOSH | ACOT | ACOTH | ADDRESS | AMORLINC | AND | ARABIC | ASC | ASIN | ASINH | ATAN | ATAN2 | ATANH | AVEDEV | AVERAGE | AVERAGEA | AVERAGEIF | AVERAGEIFS |
      BASE | BETA\.DIST | BETA\.INV | BETADIST | BETAINV | BIN2DEC | BIN2HEX | BIN2OCT | BINOM\.DIST | BINOM\.INV | BINOMDIST | BITAND | BITLSHIFT | BITOR | BITRSHIFT | BITXOR | BYCOL | BYROW |
      CEILING | CEILING\.MATH | CEILING\.PRECISE | CELL | CHAR | CHIDIST | CHIINV | CHISQ\.DIST | CHISQ\.DIST\.RT | CHISQ\.INV | CHISQ\.INV\.RT | CHISQ\.TEST | CHITEST | CHOOSE | CHOOSECOLS | CHOOSEROWS |
      CLEAN | CODE | COLUMN | COLUMNS | COMBIN | COMBINA | COMPLEX | CONCAT | CONCATENATE | CONFIDENCE | CONFIDENCE\.NORM | CONFIDENCE\.T | CONVERT | CORREL | COS | COSH | COT | COTH | COUNT | COUNTA |
      COUNTBLANK | COUNTIF | COUNTIFS | COUPDAYBS | COUPDAYS | COUPDAYSNC | COUPNCD | COUPNUM | COUPPCD | COVAR | COVARIANCE\.P | COVARIANCE\.S | CRITBINOM | CSC | CSCH | CUMIPMT | CUMPRINC |
      DATE | DATEDIF | DATEVALUE | DAVERAGE | DAY | DAYS | DAYS360 | DB | DCOUNT | DCOUNTA | DDB | DEC2BIN | DEC2HEX | DEC2OCT | DECIMAL | DEGREES | DELTA | DEVSQ | DGET | DISC | DMAX | DMIN |
      DOLLAR | DOLLARDE | DOLLARFR | DPRODUCT | DSTDEV | DSTDEVP | DSUM | DURATION | DVAR | DVARP |
      EDATE | EFFECT | ENCODEURL | EOMONTH | ERF | ERF\.PRECISE | ERFC | ERFC\.PRECISE | ERROR\.TYPE | EVEN | EXACT | EXP | EXPON\.DIST | EXPONDIST |
      F\.DIST | F\.DIST\.RT | F\.INV | F\.INV\.RT | F\.TEST | FACT | FACTDOUBLE | FALSE | FDIST | FILTER | FIND | FINDB | FINV | FISHER | FISHERINV | FIXED | FLOOR | FLOOR\.MATH | FLOOR\.PRECISE |
      FORECAST | FORECAST\.LINEAR | FORMULATEXT | FREQUENCY | FTEST | FV | FVSCHEDULE |
      GAMMA | GAMMA\.DIST | GAMMA\.INV | GAMMADIST | GAMMAINV | GAMMALN | GAMMALN\.PRECISE | GAUSS | GCD | GEOMEAN | GESTEP | GETPIVOTDATA | GROWTH |
      HARMEAN | HEX2BIN | HEX2DEC | HEX2OCT | HLOOKUP | HOUR | HSTACK | HYPERLINK | HYPGEOM\.DIST | HYPGEOMDIST |
      IF | IFERROR | IFNA | IFS | IMABS | IMAGE | IMAGINARY | IMARGUMENT | IMCONJUGATE | IMCOS | IMCOSH | IMCOT | IMCSC | IMCSCH | IMDIV | IMEXP | IMLN | IMLOG10 | IMLOG2 | IMPOWER | IMPRODUCT | IMREAL |
      IMSEC | IMSECH | IMSIN | IMSINH | IMSQRT | IMSUB | IMSUM | IMTAN | INDEX | INDIRECT | INT | INTERCEPT | INTRATE | IPMT | IRR | ISBLANK | ISERR | ISERROR | ISEVEN | ISFORMULA | ISLOGICAL | ISNA |
      ISNONTEXT | ISNUMBER | ISO\.CEILING | ISODD | ISOWEEKNUM | ISPMT | ISREF | ISTEXT |
      KURT |
      LAMBDA | LARGE | LCM | LEFT | LEFTB | LEN | LENB | LET | LINEST | LN | LOG | LOG10 | LOGEST | LOGINV | LOGNORM\.DIST | LOGNORM\.INV | LOGNORMDIST | LOOKUP | LOWER |
      MAKEARRAY | MAP | MATCH | MAX | MAXA | MAXIFS | MDETERM | MDURATION | MEDIAN | MID | MIDB | MIN | MINA | MINIFS | MINUTE | MINVERSE | MIRR | MMULT | MOD | MODE | MODE\.MULT | MODE\.SNGL |
      MONTH | MROUND | MULTINOMIAL | MUNIT |
      N | NA | NEGBINOM\.DIST | NEGBINOMDIST | NETWORKDAYS | NETWORKDAYS\.INTL | NOMINAL | NORM\.DIST | NORM\.INV | NORM\.S\.DIST | NORM\.S\.INV | NORMDIST | NORMINV | NORMSDIST | NORMSINV | NOT | NOW | NPER | NPV |
      OCT2BIN | OCT2DEC | OCT2HEX | ODD | OFFSET | OR |
      PDURATION | PEARSON | PERCENTILE | PERCENTILE\.EXC | PERCENTILE\.INC | PERCENTRANK | PERCENTRANK\.EXC | PERCENTRANK\.INC | PERMUT | PERMUTATIONA | PHI | PI | PMT | POISSON | POISSON\.DIST | POWER | PPMT |
      PRICE | PRICEDISC | PRICEMAT | PROB | PRODUCT | PROPER | PV |
      QUARTILE | QUARTILE\.EXC | QUARTILE\.INC | QUOTIENT |
      RADIANS | RAND | RANDARRAY | RANDBETWEEN | RANK | RANK\.AVG | RANK\.EQ | RATE | RECEIVED | REDUCE | REPLACE | REPLACEB | REPT | RIGHT | RIGHTB | ROMAN | ROUND | ROUNDDOWN | ROUNDUP | ROW | ROWS | RRI | RSQ |
      SCAN | SEARCH | SEARCHB | SEC | SECH | SECOND | SEQUENCE | SERIESSUM | SIGN | SIN | SINH | SKEW | SKEW\.P | SLN | SLOPE | SMALL | SORT | SQRT | SQRTPI |
      STANDARDIZE | STDEV | STDEV\.P | STDEV\.S | STDEVA | STDEVP | STDEVPA | STEYX | SUBSTITUTE | SUBTOTAL | SUM | SUMIF | SUMIFS | SUMPRODUCT | SUMSQ | SUMX2MY2 | SUMX2PY2 | SUMXMY2 | SWITCH | SYD |
      T | T\.DIST | T\.DIST\.2T | T\.DIST\.RT | T\.INV | T\.INV\.2T | T\.TEST | TAN | TANH | TBILLEQ | TBILLPRICE | TBILLYIELD | TDIST | TEXT | TEXTJOIN | TIME | TIMEVALUE | TINV | TRANSPOSE | TREND | TRIM | TRIMMEAN |
      TRUE | TRUNC | TTEST | TYPE |
      UNICHAR | UNICODE | UNIQUE | UPPER |
      VALUE | VAR | VAR\.P | VAR\.S | VARA | VARP | VARPA | VDB | VLOOKUP | VSTACK |
      WEEKDAY | WEEKNUM | WEIBULL | WEIBULL\.DIST | WORKDAY | WORKDAY\.INTL | WRAPCOLS | WRAPROWS |
      XIRR | XLOOKUP | XNPV | XOR |
      YEAR | YEARFRAC | YIELD | YIELDDISC | YIELDMAT |
      Z\.TEST | ZTEST
    )\b