Custom formula evaluation

Formula parser and calculation is plug n play

Spreadsheet is completely headless, you can use your own formula parser and evaluator. You can choose to use client-side or server side evaluation.

Formula parser

The default formula parser is based on open source fast-formula-parser . All formula calculations are done on the client-side.

Custom functions

If you are using useSpreadsheetState hook to render the data of the Spreadsheet, its easy to add custom functions.

1. Create your named function

import type FormulaParser from "@rowsncolumns/fast-formula-parser";
import type { FunctionArgument } from "@rowsncolumns/calculator";

const SAY_WORLD = (parser: FormulaParser, arg: FunctionArgument) => {
  if (arg.value === 'hello') {
    return 'world'
  }
}

2. Create a function description

Function description appears in the dropdown when users enters the formula

const functionDescriptions = [{
  datatype: "Text",
  title: "SAY_WORLD",
  syntax: "SAY_WORLD(value)",
  description: "Returns world if user says hello.",
  example: "SAY_WORLD('hello')",
  usage: ["SAY_WORLD('oops')"],
  parameters: [
    {
      title: "value",
      description: "The text that user enters.",
    },
  ],
}]

3. Pass the function and description to useSpreadsheetState

const MySpreadsheet = () => {
  const {} = useSpreadsheetState({
    functions: {
      SAY_WORLD
    }
  })
  
  return (
    <CanvasGrid
      functionDescriptions={functionDescriptions}
    />
  )
}

Your new custom formula should be ready to use

Using your own formula evaluation

There are 2 ways to use a custom formula evaluation engine.

1. enqueueCalculation in useSpreadsheetState

import type { CellCoordinate } from "@rowsncolumns/dag";
const MySpreadsheet = () => {
  const {} = useSpreadsheetState({
    enqueueCalculation: (type: 'add' | 'remove' | 'dirty', position: CellCoordinate) => {
      // Process calculations and update sheet
      const value = getCellValue (position)
      if (value.str(0) === '=') {
        const result = myCustomEvaluationQueue(value)
        // Call setState to update result
        onChangeSheetData()
      }  
    }
  })
  
  return (
    <CanvasGrid
    />
  )
}

If you are using a custom calculator, assuming that you are maintaining the cell dependency graph, you will also have to provide getDependents and getPrecendents API to useSpreadsheetState

2. Sending results to back-end

You can either use above method to send the calculation to the back-end to evaluate formulas or you can use the headless-ui, without useSpreadsheetState hook

import type { CellCoordinate } from "@rowsncolumns/dag";
const MySpreadsheet = () => {
  
  return (
    <CanvasGrid
      onChange={(sheetId, rowIndex, columnIndex, value: string) => {
        if (value.str(0) === '=') {
          const result = await fetch("post", { value })
          onChangeSheetData({ result })
        }
      })
    />
  )
}

Web Worker support

@rowsncolumns/calculation-worker package supports calculation via a web worker. The cell dependency graphs is still on the main UI thread, but formula parsing and calculation happens on a web worker