Dynamically Apply Cell Format to Custom Function Output

In some scenarios, you may have a custom function and want its output to always use a specific cell format, such as a currency. Generally, the best way to achieve this is by using an event handler, such as CellChanged.

Example:

spread.bind(GC.Spread.Sheets.Events.CellChanged, function (e, args) {
    const sheet = args.sheet;
    const row = args.row;
    const col = args.col;

    const formula = sheet.getFormula(row, col);

    if (formula && formula.toUpperCase().includes("MY_FUNCTION")) {
        const cell = sheet.getCell(row, col);

        // Apply currency format and avoid reapplying repeatedly
        if (cell.formatter() !== "$#,##0.00") {
            cell.formatter("$#,##0.00");
        }
    }
});

In the example above, when the CellChanged event is triggered, the affected cell(s) are checked to determine whether any contain a formula that matches the name of the custom function — we would be looking for "MY_FUNCTION" in this case. If found, each relevant cell is then retrieved individually using getCell. This ensures that formatting is applied only to the cells containing the custom function, rather than unintentionally affecting the entire range.

The desired format is then applied using the formatter method of the CellRange class.

To prevent recursion, it is important to include a guard condition. By using a strict inequality check, you can ensure that the format is only applied when necessary, avoiding repeated updates and unnecessary event triggers.