Create a Two-Variable data table with SpreadJS in JavaScript

Background:

Developers can implement a two-variable data table with SpreadJS, our Javascript excel spreadsheet. A two-variable data table is best suited in situations when users want to see how the final result changes when two of the input variables change simultaneously.

To accomplish this developers must created a custom dataTable function that can be used to create this two-variable data table:

function dataTable(sheet, columnRange, rowRange, colInputCell, rowInputCell, formulaCell),

Steps to Complete:

1. Create construct of the data table

2. Create custom dataTable function

3. Apply the dataTable function

Step 1: Create construct of the data table

Create a data table construct that is in line with the two-variable being used in the data table. For this example we are trying to identify the combination of monthly payments and loan amounts so we will programmatically add a column range of numbers of months and a row range of load amounts like so:

// 1.) Create the construct for the 2 input variables: monthly payments (B2) & Loan Amount (B1)
            for (var i=0; i<5; i++) {
                // Loan amount 
                sheet.setValue(0, i+6, 10000 + 10000*i); 
            }
            for (var j=0; j<5; j++) {
                // number of months
                sheet.setValue(1+j, 5, 12 + 12*j); 
            }

For this sample we have added the following sample data:

            // Sample data
            sheet.setColumnWidth(0, 150);
            sheet.setValue(0, 0, 'loan amount'); // Loan Amount
            sheet.setValue(1, 0, 'Term in Months'); // Term in Months
            sheet.setValue(2, 0, "Interest Rate"); // Interest Rate
            sheet.setValue(3, 0, "Payment");

            sheet.setValue(0, 1, 10000); // Loan Amount
            sheet.setValue(1, 1, 18); // Term in Months
            sheet.setValue(2, 1, 0.06); // Interest Rate
            sheet.setFormatter(2, 1, "0%"); // Interest Rate
            sheet.setFormatter(3, 1, "0.00");
            sheet.setFormula(3, 1, "PMT(B3/12,B2,B1)"); // Payment

Results:


Step 2: Create custom dataTable function

Next, we will create a custom dataTable function that takes the column and row range, col/row input cells, and formula cell to make the two-variable data table.

The col/row range includes the construct that was created in step 1.

The col/row input cells for the col will be where the loan amount is entered and the row input cell is where the number of months is entered.

The formula cell will be the cell of the SpreadJS instance that contains the formula that creates the monthly payments using the built-in PMT function.

// 2.) Create dataTable custom function
        function dataTable(sheet, columnRange, rowRange, colInputCell, rowInputCell, formulaCell) {
            // Convert the formula string to specified cell ranges and store into variables
            var colRg = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, columnRange);
            var rowRg = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, rowRange);
            var colCell = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, colInputCell);
            var rowCell = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, rowInputCell);
            var fCell = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, formulaCell);
            
     for (var i=colRg.col; i<colRg.col + colRg.colCount; i++) {
      sheet.setValue(colCell.row, colCell.col, sheet.getValue(colRg.row, i));
         for (j=rowRg.row; j<rowRg.row + rowRg.rowCount; j++) {
      sheet.setValue(rowCell.row, rowCell.col, sheet.getValue(j, rowRg.col));
                    // get the value from the Formulas cell
                    var v = sheet.getValue(fCell.row, fCell.col);
                 // set the value from the formula cells to the range of items in the column and row range
                    sheet.setValue(j, i, v);
                }
            }
        }

Step 3: Apply the dataTable function

Finally, for this example we have added a button to the webpage that onclick the the createDataTable() function triggered and it uses the custom dataTable to set and display the two-variable data table.

        function createDataTable() {
            var ss = GC.Spread.Sheets.findControl('ss');
            var sheet = ss.getActiveSheet();
            ss.suspendPaint();
            dataTable(sheet, 'G1:K1', 'F2:F6', 'B1', 'B2', 'B4');
            ss.resumePaint();
        }

Outcome:

With the construct and custom dataTable function applied we are able to create and see the two-variable data table displayed in the JS spreadsheet:

Tags:

Mackenzie Albitz