Products

Documentation keyboard_arrow_right Formulas keyboard_arrow_right Custom

Custom formulas

Create your own custom formulas in Jspreadsheet. Unlike Excel formulas that only return text or numbers, your custom functions can return actual DOM elements - buttons, images, progress bars, or any HTML you want. Your formula functions get access to the cell coordinates and worksheet instance, so you can build interactive spreadsheets that respond to data changes.

Requirements This feature is available with the Formula Pro extension.

Special properties

When defining a custom Excel-like formula in Jspreadsheet, the this object provides access to three special properties containing information about the cell where the formula was invoked and the worksheet instance that contains the cell.

this.instance: worksheet instance this.x: coordinates this.y: coordinates

Examples

API Call

To ensure the security of the API private keys, this function should be implemented and invoked from the backend. Exposing these keys in the frontend could compromise security.


const query = function(prompt, done) {
    // OpenAI query
}

OPENAI.PROMPT = function() {
    // Worksheet
    let w = this.instance;
    let x = this.x;
    let y = this.y;
    // Cell
    let cell = w.getCellObject(x, y);

    // Execute method
    try {
        if (! isFront()) {
            // Prompt
            let prompt = Array.from(arguments);
            // Query API
            query.call(w, prompt, async function(result) {
                // cell name
                let cellName = w.helpers.getCellNameFromCoords(x, y);
                // Save cache
                w.setCache(cellName, result);
                // Add result to the cell
                cell.v = result;
                // Add result to the <td>
                if (cell.element) {
                    cell.element.textContent = result;
                }
            })
        }
    } catch (err) {
        console.log(err)
    }

    return '...'
}

DOM Elements

The cells accept a DOM element returned from a formula, as below:

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

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

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

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

// Create a custom javascript method (capital case)
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('MjI5NDg4ZTEzNDljNzgzMzkwZDM4ZjdjNTQ3OTBmNzY3OGE4NTJiMDU1NDEyNDgyMzQwYzZmZmY4MGMxMWU3MDc5ODljNGFmZjg1YjJjMDc1MjYyNDJkYjc0MjRiN2Q1MmZlNTI3YWZlOTRmMWZjMjk0MTA5ZGVhOGYzM2ZjYzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU9EQTFNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create a custom javascript method (capital case)
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('MjI5NDg4ZTEzNDljNzgzMzkwZDM4ZjdjNTQ3OTBmNzY3OGE4NTJiMDU1NDEyNDgyMzQwYzZmZmY4MGMxMWU3MDc5ODljNGFmZjg1YjJjMDc1MjYyNDJkYjc0MjRiN2Q1MmZlNTI3YWZlOTRmMWZjMjk0MTA5ZGVhOGYzM2ZjYzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU9EQTFNRGN5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create a custom javascript method (capital case)
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 {
            data,
            columns,
            extensions,
        }
    }
}
</script>

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

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

// Create a custom javascript method (capital case)
const COLORIZE = (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 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' },
                ]
            }]
        });
    }
}

QR Code Generator

Create QR codes directly within your spreadsheet cells using a custom formula:

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

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

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

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

// Create QR Code custom formula
const QRCODE = function(text, size = 150) {
    if (!text) return '';

    let img = document.createElement('img');
    img.src = `https://api.qrserver.com/v1/create-qr-code/?size=${size}x${size}&data=${encodeURIComponent(text)}`;
    img.style.width = '80px';
    img.style.height = '80px';
    img.style.border = '1px solid #ddd';
    img.alt = `QR Code: ${text}`;
    return img;
}

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

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'https://jspreadsheet.com', '=QRCODE(A1)' ],
            [ 'mailto:[email protected]', '=QRCODE(A2)' ],
            [ 'tel:+1234567890', '=QRCODE(A3)' ],
            [ 'Contact: John Doe, Phone: +1234567890', '=QRCODE(A4, 200)' ],
        ],
        columns: [
            { type: 'text', width:'300px', title: 'Data' },
            { type: 'text', width:'120px', title: 'QR Code' },
        ],
        rows: {
            0: { height: '90px' },
            1: { height: '90px' },
            2: { height: '90px' },
            3: { height: '90px' }
        }
    }]
});
</script>
</html>

Stock Price Tracker

Integrate real-time stock data using free APIs (Note: This example uses mock data for demonstration):

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

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

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

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

// Stock price custom formula (using mock data for demo)
const STOCKPRICE = function(symbol) {
    if (!symbol) return 'No symbol';

    // Mock data for demonstration - replace with real API call
    const mockPrices = {
        'AAPL': 175.25,
        'GOOGL': 2845.32,
        'MSFT': 338.47,
        'TSLA': 248.91,
        'AMZN': 3127.78
    };

    const price = mockPrices[symbol.toUpperCase()];

    if (price) {
        let span = document.createElement('span');
        span.textContent = `$${price.toFixed(2)}`;
        span.style.fontWeight = 'bold';
        span.style.color = '#2e8b57';
        span.style.padding = '4px 8px';
        span.style.backgroundColor = '#f0f8f0';
        span.style.borderRadius = '4px';
        return span;
    }

    return 'Symbol not found';
}

// Real API example using Yahoo Finance (no API key required)
const STOCKPRICE_REAL = async function(symbol) {
    try {
        const response = await fetch(`https://query1.finance.yahoo.com/v8/finance/chart/${symbol}?interval=1d&range=1d`);
        const data = await response.json();
        const price = data.chart.result[0].meta.regularMarketPrice;

        let span = document.createElement('span');
        span.textContent = `$${price.toFixed(2)}`;
        span.style.fontWeight = 'bold';
        span.style.color = '#2e8b57';
        span.style.padding = '4px 8px';
        span.style.backgroundColor = '#f0f8f0';
        span.style.borderRadius = '4px';
        return span;
    } catch (error) {
        return 'Error loading';
    }
}

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

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'AAPL', '=STOCKPRICE(A1)', 'Apple Inc.' ],
            [ 'GOOGL', '=STOCKPRICE(A2)', 'Alphabet Inc.' ],
            [ 'MSFT', '=STOCKPRICE(A3)', 'Microsoft Corp.' ],
            [ 'TSLA', '=STOCKPRICE(A4)', 'Tesla Inc.' ],
            [ 'AMZN', '=STOCKPRICE(A5)', 'Amazon.com Inc.' ],
        ],
        columns: [
            { type: 'text', width:'80px', title: 'Symbol' },
            { type: 'text', width:'120px', title: 'Price' },
            { type: 'text', width:'200px', title: 'Company' },
        ]
    }]
});
</script>
</html>

Note: The example above uses Yahoo Finance's unofficial API which requires no API key. For production use, implement proper error handling, caching, and consider using server-side proxies to avoid CORS issues.

Edit this page on GitHub