Custom Formulas

Custom formulas provide a powerful mechanism to extend Jspreadsheet functionality beyond standard built-in functions. With custom formulas, you can fetch data from external APIs, manipulate complex data structures, render custom UI elements, and execute JavaScript logic directly within spreadsheet cells.

Important: All custom formula method names must be declared in UPPERCASE letters.

Documentation

Methods

The following methods and properties are available when working with custom formulas.

Method	Description
`setTracking`	Adds a cell to the tracking system that automatically updates when worksheet structure changes (row/column insertion). `setTracking(scope: object)`
`getCellObject`	Retrieves the cell object at specified coordinates. Creates the object if it doesn't exist. `getCellObject(x: number, y: number)`

Formula Scope Properties

Within a custom formula, you have access to the current cell's coordinates through this.x and this.y properties, as well as the worksheet instance via this.worksheet. This context enables the creation of sophisticated formulas that can interact with external APIs, access other cells, and perform complex operations within the spreadsheet environment.

let COORDS = function() {
    // Return the coordinates to the cell
    return this.x + ',' + this.y;
}

Declare New Methods

formula.setFormula({ COORDS });

Examples

Using DOM elements

This example demonstrates how to create a custom formula that returns a DOM element to be rendered within a cell.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NTNhYzU0YzcxYzM1ZjJiYWRiYzkwY2EyNTZhNGM5ZmZhOThkZmUzYjNhMjZjMjcwYjE0YzVjZWRlYjVkMDU2YzY4MmQ5MTVjMWVmZWY4YTM5ZmYzMjczNDIxOGU3Mjc1ZWQyYmUyZGJjNmVhYzJlNzhlMjE1MWEzMmM1MTY2NmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9EVTVPVFl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the formula pro extension
jspreadsheet.setExtensions({ formula });

// Create a custom JavaScript method (uppercase)
const COLORIZE = function(v) {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ],
        columns: [
            { type: 'text', width:'300' },
            { type: 'text', width:'200' },
        ]
    }]
});
</script>
</html>

import React, {useRef} from "react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NTNhYzU0YzcxYzM1ZjJiYWRiYzkwY2EyNTZhNGM5ZmZhOThkZmUzYjNhMjZjMjcwYjE0YzVjZWRlYjVkMDU2YzY4MmQ5MTVjMWVmZWY4YTM5ZmYzMjczNDIxOGU3Mjc1ZWQyYmUyZGJjNmVhYzJlNzhlMjE1MWEzMmM1MTY2NmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9EVTVPVFl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create a custom JavaScript method (uppercase)
const COLORIZE = function (v) {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Send custom formula to the correct scope
formula.setFormula({COLORIZE})

// Extensions
const extensions = {formula};

// Create the component
export default function App() {
    // Array with all the data grids
    const spreadsheet = useRef();
    // Data
    const data = [
        ['red', '=COLORIZE(A1)'],
        ['green', '=COLORIZE(A2)'],
        ['blue', '=COLORIZE(A3)'],
    ]
    const columns = [
        {type: 'text', width: '300px'},
        {type: 'text', width: '200px'},
    ]

    // Render data grid component
    return (
        <Spreadsheet ref={spreadsheet} extensions={extensions}>
            <Worksheet data={data} columns={columns}/>
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :extensions="extensions">
        <Worksheet :data="data" :columns="columns" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NTNhYzU0YzcxYzM1ZjJiYWRiYzkwY2EyNTZhNGM5ZmZhOThkZmUzYjNhMjZjMjcwYjE0YzVjZWRlYjVkMDU2YzY4MmQ5MTVjMWVmZWY4YTM5ZmYzMjczNDIxOGU3Mjc1ZWQyYmUyZGJjNmVhYzJlNzhlMjE1MWEzMmM1MTY2NmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9EVTVPVFl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create a custom JavaScript method (uppercase)
const COLORIZE = function(v) {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })

// Extensions
const extensions = { formula };

// Create data grid component
export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data
        const data = [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ]
        const columns = [
            { type: 'text', width:'300px' },
            { type: 'text', width:'200px' },
        ]

        return {
            extensions,
            data,
            columns
        };
    }
}
</script>

import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NTNhYzU0YzcxYzM1ZjJiYWRiYzkwY2EyNTZhNGM5ZmZhOThkZmUzYjNhMjZjMjcwYjE0YzVjZWRlYjVkMDU2YzY4MmQ5MTVjMWVmZWY4YTM5ZmYzMjczNDIxOGU3Mjc1ZWQyYmUyZGJjNmVhYzJlNzhlMjE1MWEzMmM1MTY2NmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9EVTVPVFl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

jspreadsheet.setExtensions({ formula })

// Create a custom JavaScript method (uppercase)
const COLORIZE = (v: string) => {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })

// Create the data grid component
@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`,
})
export class AppComponent implements AfterViewInit {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
                    [ 'red', '=COLORIZE(A1)' ],
                    [ 'green', '=COLORIZE(A2)' ],
                    [ 'blue', '=COLORIZE(A3)' ],
                ],
                columns: [
                    { type: 'text', width: 300  },
                    { type: 'text', width: 200  },
                ]
            }]
        });
    }
}

Formula Chain

This example demonstrates a custom formula that calculates the sum of all values in the same column above the current cell. Since the formula doesn't explicitly reference other cells, it manually creates a dependency chain to ensure automatic updates when column values change.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NTNhYzU0YzcxYzM1ZjJiYWRiYzkwY2EyNTZhNGM5ZmZhOThkZmUzYjNhMjZjMjcwYjE0YzVjZWRlYjVkMDU2YzY4MmQ5MTVjMWVmZWY4YTM5ZmYzMjczNDIxOGU3Mjc1ZWQyYmUyZGJjNmVhYzJlNzhlMjE1MWEzMmM1MTY2NmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9EVTVPVFl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the formula pro extension
jspreadsheet.setExtensions({ formula });

// Create a custom JavaScript method (uppercase)
const SUMCOL = function () {
    console.log(this.worksheet)
    // Update a formula chain when worksheet structure changes
    this.worksheet.setTracking.call(this, true);
    // Parent
    let parent = this.worksheet.getCellObject(this.x, this.y);
    // Get the column data
    let data = this.worksheet.getColumnData(this.x);
    // Total
    let total = 0;
    // Sum all values in the column from zero to the row number
    for (let y = 0; y < this.y; y++) {
        // Get the cell object
        let cell = this.worksheet.getCellObject(this.x, y);
        // Total
        total += parseInt(data[y]);
        // Formula chain
        if (typeof(cell.chain) === 'undefined') {
            cell.chain = new Map;
        }
        // Keep reference in the formula
        cell.chain.set(parent, null);
    }

    return total;
}

// Send custom formula to the correct scope
formula.setFormula({ SUMCOL })

// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'Apple', 931 ],
            [ 'Google', 431 ],
            [ 'Amazon', 534 ],
            [ 'Total', '=SUMCOL()' ],
        ],
        minDimensions: [6,4],
    }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NTNhYzU0YzcxYzM1ZjJiYWRiYzkwY2EyNTZhNGM5ZmZhOThkZmUzYjNhMjZjMjcwYjE0YzVjZWRlYjVkMDU2YzY4MmQ5MTVjMWVmZWY4YTM5ZmYzMjczNDIxOGU3Mjc1ZWQyYmUyZGJjNmVhYzJlNzhlMjE1MWEzMmM1MTY2NmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9EVTVPVFl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Extensions
jspreadsheet.setExtensions({ formula });

// Create a custom JavaScript method (uppercase)
const SUMCOL = function () {
    // Update formula chain when worksheet structure changes
    this.worksheet.setTracking.call(this, true);
    // Get the column data
    let data = this.worksheet.getColumnData(this.x);
    // Total
    let total = 0;
    // Sum all values in the column from zero to the row number
    for (let y = 0; y < this.y; y++) {
        // Get the cell object
        let cell = this.worksheet.getCellObject(this.x, y);
        // Total
        total += parseInt(data[y]);
        // Formula chain
        if (typeof(cell.chain) === 'undefined') {
            cell.chain = new Map;
        }
        // Keep reference in the formula
        cell.chain.set(this.worksheet.records[this.y][this.x], this.worksheet);
    }

    return total;
}

// Send custom formula to the correct scope
formula.setFormula({ SUMCOL })

// Create the component
export default function App() {
    // Array with all the data grids
    const spreadsheet = useRef();
    // Data
    const data = [
        [ 'Apple', 931 ],
        [ 'Google', 431 ],
        [ 'Amazon', 534 ],
        [ 'Total', '=SUMCOL()' ],
    ]

    // Render data grid component
    return (
        <Spreadsheet ref={spreadsheet} extensions={{ formula }}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
  <Spreadsheet ref="spreadsheet">
    <Worksheet :data="data" />
  </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from '@jspreadsheet/vue';
import formula from '@jspreadsheet/formula-pro';
import 'jsuites/dist/jsuites.css';
import 'jspreadsheet/dist/jspreadsheet.css';

// Add here your data grid license
jspreadsheet.setLicense('NTNhYzU0YzcxYzM1ZjJiYWRiYzkwY2EyNTZhNGM5ZmZhOThkZmUzYjNhMjZjMjcwYjE0YzVjZWRlYjVkMDU2YzY4MmQ5MTVjMWVmZWY4YTM5ZmYzMjczNDIxOGU3Mjc1ZWQyYmUyZGJjNmVhYzJlNzhlMjE1MWEzMmM1MTY2NmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9EVTVPVFl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Extensions
jspreadsheet.setExtensions({ formula });

// Create a custom JavaScript method (uppercase)
const SUMCOL = function () {
  // Update a formula chain when a new row is added
  this.worksheet.setTracking.call(this, true);
  // Get the column data
  let data = this.worksheet.getColumnData(this.x);
  // Total
  let total = 0;
  // Sum all values in the column from zero to the row number
  for (let y = 0; y < this.y; y++) {
    // Get the cell object
    let cell = this.worksheet.getCellObject(this.x, y);
    // Total
    total += parseInt(data[y]);
    // Formula chain
    if (typeof cell.chain === 'undefined') {
      cell.chain = new Map();
    }
    // Keep reference in the formula
    cell.chain.set(this.worksheet.records[this.y][this.x], this.worksheet);
  }

  return total;
};

// Send custom formula to the correct scope
formula.setFormula({ SUMCOL });

export default {
  components: {
    Spreadsheet,
    Worksheet,
  },
  data() {
    // Data
    const data = [
      ['Apple', 931],
      ['Google', 431],
      ['Amazon', 534],
      ['Total', '=SUMCOL()'],
    ];

    return {
      data,
    };
  },
};
</script>

import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
import 'jsuites/dist/jsuites.css';
import 'jspreadsheet/dist/jspreadsheet.css';

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NTNhYzU0YzcxYzM1ZjJiYWRiYzkwY2EyNTZhNGM5ZmZhOThkZmUzYjNhMjZjMjcwYjE0YzVjZWRlYjVkMDU2YzY4MmQ5MTVjMWVmZWY4YTM5ZmYzMjczNDIxOGU3Mjc1ZWQyYmUyZGJjNmVhYzJlNzhlMjE1MWEzMmM1MTY2NmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9EVTVPVFl3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set extensions
jspreadsheet.setExtensions({ formula });

// Create a custom JavaScript method (uppercase)
const SUMCOL = function (this: any) {
    // Update formula chain when worksheet structure changes
    this.worksheet.setTracking.call(this, true);
    // Get the column data
    let data = this.worksheet.getColumnData(this.x);
    // Total
    let total = 0;
    // Sum all values in the column from zero to the row number
    for (let y = 0; y < this.y; y++) {
        // Get the cell object
        let cell = this.worksheet.getCellObject(this.x, y);
        // Total
        total += parseInt(data[y]);
        // Formula chain
        if (typeof(cell.chain) === 'undefined') {
            cell.chain = new Map;
        }
        // Keep reference in the formula
        cell.chain.set(this.worksheet.records[this.y][this.x], this.worksheet);
    }

    return total;
}

// Send custom formula to the correct scope
formula.setFormula({ SUMCOL })

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`,
})
export class AppComponent implements AfterViewInit {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [
                {
                    data: [
                        [ 'Apple', 931 ],
                        [ 'Google', 431 ],
                        [ 'Amazon', 534 ],
                        [ 'Total', '=SUMCOL()' ],
                    ],
                    minDimensions: [6,4],
                }
            ]
        });
    }
}

More resources

More Custom Formulas Examples

Edit this page on GitHub