Use Cell References

SpreadJS allows you to perform various operations by using cell references.

Convert Expression to Formula

You can convert an expression to a formula using the expressionToFormula method.

var spread =
new GC.Spread.Sheets.Workbook(document.getElementById('ss'),
{ sheetCount: 1 });
sheet = spread.getSheet(0);
sheet.setValue(0, 0, 1);
sheet.setValue(0, 1, 2);
sheet.setValue(0, 2, 3);
sheet.addCustomName("customName1", "=12", 0, 0);
sheet.addCustomName("customName2", "Average(20,45)", 0, 0);
sheet.addCustomName("customName3", "=$A$1:$C$1");
sheet.setFormula(1, 0, "customName1");
sheet.setFormula(1, 1, "customName2");
sheet.setFormula(1, 2, "sum(customName3)");

var cname = sheet.getCustomName("customName2");
if (cname instanceof GC.Spread.Sheets.NameInfo)
{
    // Get CustomName
    var name = cname.getName();
  
    // Get Expression
    var expression = cname.getExpression();
  
    // Get Expression String
    var expStr = GC.Spread.Sheets.CalcEngine.expressionToFormula(sheet, expression, 0, 0);
    console.log("Name: " + name + " ; Expression: " + expStr);
}

Convert Formula to Expression

You can convert a formula to an expression using the formulaToExpression method.

var spread =
new GC.Spread.Sheets.Workbook(document.getElementById('ss'),
{ sheetCount: 1 });
sheet = spread.getSheet(0);
sheet.setValue(0, 0, 1);
sheet.setValue(0, 1, 2);
sheet.setValue(0, 2, 3);
sheet.getCell(4, 4).formula("=SUM(A1:C1)");

var formula = sheet.getFormula(4, 4);
var expression = GC.Spread.Sheets.CalcEngine.formulaToExpression(sheet, formula, 0, 0);
console.log("Function Name is: " + expression.functionName);

Convert Formula to Ranges

You can convert a formula string to the specified cell range using the formulaToRanges method.

var spread =
new GC.Spread.Sheets.Workbook(document.getElementById('ss'),
{ sheetCount: 1 });
sheet = spread.getSheet(0);

// Creating cell range using formulaToRanges() method
cellRanges = GC.Spread.Sheets.CalcEngine.formulaToRanges(sheet, 'B3:D5', 0, 0);

// Getting row/column indexes and rowCount/colCount of range in formula
console.log("Starting Row Index of cell range 'B3:D5' is " + cellRanges[0].ranges[0].row);
console.log("Starting Column Index of cell range 'B3:D5' is " + cellRanges[0].ranges[0].col);
console.log("RowCount of cell range 'B3:D5' is " + cellRanges[0].ranges[0].rowCount);
console.log("ColCount of cell range 'B3:D5' is " + cellRanges[0].ranges[0].colCount);

Evaluate Formula

You can evaluate a formula using the evaluateFormula method.

var spread =
new GC.Spread.Sheets.Workbook(document.getElementById('ss'),
{ sheetCount: 1 });
sheet = spread.getSheet(0);
sheet.setValue(0, 0, 1);
sheet.setValue(1, 0, 2);

// Using EvaluateFormula() method to evaluate formula without setting formula in sheet's cell
var result =
GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, "SUM(A1:A2)", 0, 0);
console.log("SUM(A1:A2) = " + result);

You can evaluate a formula asynchronously using the evaluateFormulaAsync method.

First, define a simple custom asynchronous function called ASUM (Asynchronous Sum) that simulates a delay using setTimeout. Then, use the evaluateFormulaAsync() method to evaluate the formula asynchronously without setting the formula in the sheet's cell.

// Define the async function ASUM
var asyncSum = function () {};
asyncSum.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("ASUM", 1, 255);

// Default value displayed while calculation is pending
asyncSum.prototype.defaultValue = function () {
    return "Loading...";
};

// The asynchronous evaluation logic
asyncSum.prototype.evaluateAsync = function (context) {
    // Use setTimeout to simulate a 2-second server-side evaluation delay
    var args = arguments;
    setTimeout(function () {
        var result = 0;
        // Arguments start from index 1 (index 0 is the context)
        for (var i = 1; i < args.length; i++) {
            result += args[i];
        }
        // Set the final result using the context provided
        context.setAsyncResult(result * 2); // Example: double the sum
    }, 2000); // Simulate 2-second delay
};

// Register the custom function globally
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction("ASUM", new asyncSum());

// Basic usage with async function:
var spread =
new GC.Spread.Sheets.Workbook(document.getElementById('ss'),
{ sheetCount: 1 });
sheet = spread.getSheet(0);
sheet.setValue(0, 0, 1);
sheet.setValue(1, 0, 2);

// Use evaluateFormulaAsync() method
GC.Spread.Sheets.CalcEngine.evaluateFormulaAsync(sheet, "ASUM(2, 2)", 0, 0)
    .then(result => {
        console.log("ASUM(2, 2) result:", result); // Expected output after ~2s: ASUM(2, 2) result: 8
    })
    .catch(error => {
        console.error("Evaluation error:", error);
    });
//Result: Promise{8}

Goal Seek Method

You can use the goalSeek method to find a value for one cell that produces the desired formula result in another cell.

/* This sample shows how to use the goal seek.
Loan amount is 10000, term is 18 months and pay 600 each month,
evaluate what interest rate you will need to secure in order to
meet your loan goal */

sheet.setValue(0, 1, 10000); // Loan Amount
sheet.setValue(1, 1, 18); // Term in Months
sheet.setFormatter(2, 1, "0%"); // Interest Rate
sheet.setFormatter(3, 1, "0.00");
sheet.setFormula(3, 1, "PMT(B3/12,B2,B1)"); // Payment
GC.Spread.Sheets.CalcEngine.goalSeek(sheet, 2, 1, sheet, 3, 1, -600); // Result in B3 is 10%