LibreOffice Calc Formula.sublime-syntax

%YAML 1.2
---
name: LibreOffice Calc Formula
scope: source.sheet.libre
version: 2
extends: Packages/Spreadsheet Formula/Spreadsheet Formula (Basic).sublime-syntax
file_extensions:
  - localc.txt

contexts:
  function-builtins-extra:
  - meta_append: true
  - match: \b({{function_builtins_libre}})(\()
    captures:
      1: meta.function-call.libre support.function.libre
      2: meta.function-call.arguments.libre punctuation.section.arguments.begin.libre
    push: function-call-body

  sheet-reference:
    - match: \$(?={{sheet}}\.)
      scope: punctuation.definition.annotation.begin.libre
      push: sheet-reference-body-dollar
    - match: (?={{sheet}}!)
      push: sheet-reference-body

  sheet-reference-body-dollar:
    - meta_scope: meta.reference.sheet.libre
    - match: \.
      scope: punctuation.separator.sequence.libre
      pop: 1
    - include: sheet-reference-quoted
    - match: '{{sheet}}'
      scope: entity.name.struct.sheet.libre
    - include: immediately-pop

  sheet-reference-body:
    - meta_scope: meta.reference.sheet.libre
    - match: '!'
      scope: punctuation.separator.sequence.libre
      pop: 1
    - include: sheet-reference-quoted
    - match: '{{sheet}}'
      scope: entity.name.struct.sheet.libre
    - include: immediately-pop

  sheet-reference-quoted:
    - match: \'
      scope: punctuation.definition.annotation.begin.libre
      push:
        - meta_content_scope: entity.name.struct.sheet.libre
        - match: \'\'
          scope: constant.character.escape.libre
        - match: \'(?=[!\.])
          scope: punctuation.definition.annotation.end.libre
          pop: 1

  cell-or-range-reference:
    - meta_append: true
    - match: ({{rc}})(:)({{rc}})
      scope: meta.reference.range.cell.libre
      captures:
        1: storage.type.libre
        2: punctuation.separator.sequence.libre
        3: storage.type.libre
      push: after-reference

    - match: ({{rc}})
      scope: meta.reference.range.cell.libre
      captures:
        1: storage.type.libre
        2: punctuation.separator.sequence.libre
        3: storage.type.libre
      push: after-reference

variables:
  function_builtins_libre: |-
    \b(?xi:
      AGGREGATE | AMORDEGRC | AREAS |
      B | BAHTTEXT | BESSELI | BESSELJ | BESSELK | BESSELY |
      CEILING.XCL | CHISQDIST | CHISQINV | COLOR | CONVERT_OOO | CUMIPMT_ADD | CUMPRINC_ADD | CURRENT |
      DAYSINMONTH | DAYSINYEAR | DDE |
      EASTERSUNDAY | EFFECT_ADD | ERRORTYPE | EUROCONVERT |
      FILTERXML | FLOOR.XCL | FORECAST.ETS.ADD | FORECAST.ETS.MULT | FORECAST.ETS.PI.ADD | FORECAST.ETS.PI.MULT | FORECAST.ETS.SEASONALITY | FORECAST.ETS.STAT.ADD | FORECAST.ETS.STAT.MULT | FORMULA | FOURIER |
      GCD_EXCEL2003 |
      INFO | ISEVEN_ADD | ISLEAPYEAR | ISODD_ADD |
      JIS |
      LCM_EXCEL2003 |
      MULTIPLE.OPERATIONS | MONTHS |
      NETWORKDAYS_EXCEL2003 | NOMINAL_ADD | NUMBERVALUE |
      ODDFPRICE | ODDFYIELD | ODDLPRICE | ODDLYIELD | OPT_BARRIER | OPT_PROB_HIT | OPT_PROB_INMONEY | OPT_TOUCH |
      RAND.NV | RANDBETWEEN.NV | RAWSUBTRACT | REGEX | ROT13 | ROUNDSIG |
      SHEET | SHEETS | SKEWP | STYLE |
      TODAY |
      WEBSERVICE | WEEKNUM_EXCEL2003 | WEEKS | WEEKSINYEAR |
      YEARS
    )\b
  # NOTE: Negative lookahead prevents this pattern from chaining matches like in '@name' (i.e., only a single '@' is a valid name).
  # TODO: Is there a better way to do this?
  name: |-
    (?x:
      [_.?@`\\]
      (?![\w?$]+[\w.$]*) |
      [\w?$]+
      [\w.$]*
    )
  # LibreOffice does not have LAMBDA() and cannot define functions in LET().
  # These rules stem from testing in LO's VBA analog: LibreOffice Basic
  function: (?i:[a-z]\w*)
  # LibreOffice does not support tables
  table: (?!.)

  # TODO: Is there a cleaner more compact (and probably more efficient) way to
  #(dupe) do this? `'` are only legal in excel sheet names when padded on
  #       either end with a legal character matched by [^:*/?\[\]\\],
  #       and said `'` must come in pairs.
  # TODO: Update with LibreOffice alternate-format sheet references in addition.
  sheet_delimited: |-
                    (?x:
                      (?:'[^:*/?\[\]\\]+('')*[^:*/?\[\]\\]+')|
                      (?:'[^:*/?\[\]\\]+')|
                      (?:\$'[^:*/?\[\]\\]+('')*[^:*/?\[\]\\]+')
                    )
  sheet_normal: |-
                    (?x:
                      (?:[\w&&[^0-9]]\w*)|
                      (?:\$[^\.:*/?\[\]\\]+)
                    )
  sheet: (?:{{sheet_delimited}}|{{sheet_normal}})

  # RC row/column offset format
  r_: |-
    (?xi:
      R
      (?:
        \[[-\+]?[1-9][0-9]{0,6}\] |
                [1-9][0-9]{0,6}
      )
      (?:
          (?={{c_}})
        | (?=C?(?!{{name}}))
      )
    )
  c_: (?i:C(?:\[[-\+]?[1-9][0-9]{0,4}\]|[1-9][0-9]{0,4})(?!{{name}}))
  rc: |-
    (?xi:
      {{r_}}{{c_}}|
      {{r_}}C|
      {{r_}}|
      R{{c_}}|
      {{c_}}
    )