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);

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%