Rows n’ Columns Docs
Visit HomepagePricing
  • Introduction
  • License
  • Demos
  • Getting started
    • Installation
    • Spreadsheet state
    • Headless UI
    • Imperative Spreadsheet API
    • Examples
  • ⚙️Configuration
    • Features
      • Data validation
      • Formula evaluation
      • Real-time data
      • Cell editors
      • Cell renderer
      • Structured Cell Renderer
      • Theming
      • Styling
      • Context menu
      • Localisation
      • Undo/Redo
      • Conditional formatting
      • Named ranges
      • Structured references
        • Schema based tables and columns
        • Calculated columns
      • Basic filter or Excel AutoFilter
      • Charts
      • Embedded content
      • Calculate on-demand
      • Drag and Drop
      • Pivoting and Grouping (Coming soon)
      • Tokenizer
      • Lazy loading/Infinite scrolling
      • OpenAI/Chat GPT Integration
      • Search
      • Formula protection
      • Autofill
      • Export canvas as image
    • Components
      • Canvas Grid
      • Toolbar
      • Sheet Tabs
      • Sheet Switcher
      • Sheet Status
      • Range Selector
      • Formula Input
      • Selection Input
      • SheetSearch
      • NamedRangeEditor
      • DeleteSheetConfirmation
      • TableEditor
      • Cell Format Editor
      • Conditional Format Editor
      • Data Validation Editor
      • Insert Link Editor
      • Insert Image Editor
    • API
      • Cell Data
      • Sheets
      • SpreadsheetProvider
      • useSpreadsheet
      • Modules
      • SheetCell
    • Using Spreadsheet with NextJS
    • Keyboard shortcuts
  • Collaboration
    • Real time collaboration
    • Yjs Collaboration
    • Supabase realtime Collaboration
  • Charts
    • Charts
    • Custom charts
  • Excel and Google sheets
    • CSV
    • Excel
    • Google sheets (Coming soon)
  • Functions
    • Named functions
    • Array formulas
  • Data persistence
    • Server side data persistence
    • React Query integration
  • Specifications
    • Browser support
    • Third party licenses
  • Support
    • Contact support
    • Report bugs
    • Feature requests
Powered by GitBook
On this page
  • Formula parser
  • Custom functions
  • 1. Create your named function
  • 2. Create a function description
  • 3. Pass the function and description to useSpreadsheetState
  • Using your own formula evaluation
  • 1. enqueueCalculation in useSpreadsheetState
  • 2. Sending results to back-end
  • Web Worker support

Was this helpful?

  1. Configuration
  2. Features

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/calculator-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

PreviousData validationNextReal-time data

Last updated 2 years ago

Was this helpful?

⚙️