Formula Suggestions

Overview

Jspreadsheet can show a formula autocomplete popup while the user is editing a cell, similar to Excel or Google Sheets. As soon as the cell content starts with = and the user types a function name, a list of matching functions appears with their syntax and a short description. Selecting an entry inserts the function name followed by an opening parenthesis, so users can write formulas faster and with fewer mistakes.

The suggestion list is data driven. Jspreadsheet ships with a ready-to-use list of all supported functions in jspreadsheet/dist/formulas.json, but you can provide your own array to limit, extend, or fully customize the suggestions.

Documentation

Initial Settings

The following properties are available during spreadsheet initialization.

Property	Description
`suggestions: array`	An array of function definitions used to build the formula autocomplete popup. Example: `[{ name: 'SUM', syntax: 'SUM(value1, ...)', description: 'Adds its arguments.' }]`
`toolbar: boolean`	Show the default toolbar. Often enabled together with `suggestions` to provide a complete formula editing experience.

Suggestion Object

Each item in the suggestions array describes one function. Only name is required for the popup to work; the other properties improve what the user sees.

Property	Description
`name: string`	The function name. This is what the popup matches against as the user types, and what gets inserted on selection.
`syntax: string`	The full call signature shown as the suggestion title. Falls back to `name` when omitted.
`description: string`	A short explanation shown below the title.
`params: array`	Optional parameter details (`{ type, comment }`), used by the function helper. Not required by the popup.
`examples: string`	Optional usage example for the function.
`category: string`	Optional grouping such as `Statistical`, `Math`, or `Text`.

The popup matches the typed token against name (case-insensitive, prefix match), lists up to 20 results, and inserts NAME( when an item is chosen.

Examples

A Basic Data Grid with Formula Suggestions

This example enables the formula autocomplete with a small, custom list of functions. Start typing =SU in any cell to see the suggestions popup.

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

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

<script>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('MzlkYWZhNWI2NWY0ZDY4OThhMzM2YTJmNzk4ZDA3MTc2ZDY3Zjk0MDU3N2ZmMmI2NWZhZjI5MDQ3YzRiZjZkMzViOWVjY2UzMThmMWE4M2Y1NGNkYzM4OGYwNjVmODU1ODI4ZTEzOTYzYzhiZDRhYzM0ZmMyYTUzZDRlZjkyNjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpneE16STJNemd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');

// A custom list of formula suggestions
const suggestions = [
    { name: 'CUSTOM', syntax: 'CUSTOM(value1, [value2, ...])', description: 'Custom adding all the numbers in a range of cells.' },
];

fetch('https://jspreadsheet.com/jspreadsheet/formulas/g')
    .then(function(result) {
        return result.json();   // also returns a Promise
    })
    .then(function(data) {
        suggestions.push(...data);
    });

// Create the spreadsheet
const worksheets = jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: true,
    suggestions: suggestions,
    worksheets: [{
        data: [[]],
        minDimensions: [6, 6]
    }]
});
</script>
</html>

import React from "content/docs/react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import suggestions from "jspreadsheet/dist/formulas.json";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('MzlkYWZhNWI2NWY0ZDY4OThhMzM2YTJmNzk4ZDA3MTc2ZDY3Zjk0MDU3N2ZmMmI2NWZhZjI5MDQ3YzRiZjZkMzViOWVjY2UzMThmMWE4M2Y1NGNkYzM4OGYwNjVmODU1ODI4ZTEzOTYzYzhiZDRhYzM0ZmMyYTUzZDRlZjkyNjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpneE16STJNemd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');

export default function App() {
    // Render component
    return (
        <Spreadsheet toolbar suggestions={suggestions}>
            <Worksheet data={[[]]} minDimensions={[6, 6]}/>
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet toolbar :suggestions="suggestions">
        <Worksheet :data="[[]]" :minDimensions="[6, 6]" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import suggestions from "jspreadsheet/dist/formulas.json";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('MzlkYWZhNWI2NWY0ZDY4OThhMzM2YTJmNzk4ZDA3MTc2ZDY3Zjk0MDU3N2ZmMmI2NWZhZjI5MDQ3YzRiZjZkMzViOWVjY2UzMThmMWE4M2Y1NGNkYzM4OGYwNjVmODU1ODI4ZTEzOTYzYzhiZDRhYzM0ZmMyYTUzZDRlZjkyNjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpneE16STJNemd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        return {
            suggestions,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import suggestions from "jspreadsheet/dist/formulas.json";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('MzlkYWZhNWI2NWY0ZDY4OThhMzM2YTJmNzk4ZDA3MTc2ZDY3Zjk0MDU3N2ZmMmI2NWZhZjI5MDQ3YzRiZjZkMzViOWVjY2UzMThmMWE4M2Y1NGNkYzM4OGYwNjVmODU1ODI4ZTEzOTYzYzhiZDRhYzM0ZmMyYTUzZDRlZjkyNjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpneE16STJNemd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');

@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, {
            toolbar: true,
            suggestions: suggestions,
            worksheets: [{
                data: [[]],
                minDimensions: [6, 6]
            }]
        });
    }
}

Using the Built-in Function List

Jspreadsheet bundles the complete list of supported functions in jspreadsheet/dist/formulas.json. Import it to enable suggestions for every available function without maintaining your own list.

import React from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import suggestions from "jspreadsheet/dist/formulas.json";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('MzlkYWZhNWI2NWY0ZDY4OThhMzM2YTJmNzk4ZDA3MTc2ZDY3Zjk0MDU3N2ZmMmI2NWZhZjI5MDQ3YzRiZjZkMzViOWVjY2UzMThmMWE4M2Y1NGNkYzM4OGYwNjVmODU1ODI4ZTEzOTYzYzhiZDRhYzM0ZmMyYTUzZDRlZjkyNjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpneE16STJNemd4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');

export default function App() {
    // Render component
    return (
        <Spreadsheet toolbar suggestions={suggestions}>
            <Worksheet
                data={[['Sales', 100], ['Costs', 40], ['Profit', '']]}
                minDimensions={[6, 6]}
            />
        </Spreadsheet>
    );
}

You can also filter the bundled list before passing it in — for example, to expose only a single category:

import allFormulas from "jspreadsheet/dist/formulas.json";

// Only suggest statistical functions
const suggestions = allFormulas.filter(formula => formula.category === 'Statistical');