Data Analysis and Extraction with Dynamic Arrays in a JavaScript Spreadsheet Component

Microsoft Excel added a powerful new calculation feature known as dynamic arrays, and we are happy to announce that SpreadJS now supports these as well. Previously you used one formula to return one result. The new dynamic array support allows you to return multiple results from one formula. These results can also spill into adjacent cells, known as spilled array formulas.

In this article, I will show you how to implement a few of the different dynamic array functions in the SpreadJS Designer and the built-in SpreadJS API, including FILTER, SORT, and UNIQUE, spilled arrays, and the spilled operator.

Setting up the JavaScript Spreadsheet

A basic SSJSON file has been created that has the table and data from this project in it. We will use this as a basis for loading the rest of the project. Also included is a finished SSJSON file that has the same dynamic arrays implemented in the designer itself. Load the unfinished JSON with JavaScript in an HTML file like so:

<script type="text/javascript" src="dynamicArrays.js"></script>

<script>
    window.onload = function() {
       // Initializing Spread         
      var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });         

      // Load JSON template saved as JS         
      spread.fromJSON(dynamicArrays);     
};
</script>

Getting Started: Dynamic Array Functions

Now that we have some data loaded, we can use the different dynamic array functions to analyze the data with just a few functions. The first thing to do in code to enable dynamic arrays is to set the allowDynamicArray option to true

spread.options.allowDynamicArray = true;

As some formulas calculate differently when dynamic arrays are enabled, this option is required. With this enabled, if you do want to return a single value from a function that can return multiple results, you would have to use the SINGLE function or the '@' operator because there is no more implicit intersection.

The first function to try out is FILTER. Which in this case, we can use to filter out all of the orders that were worth more than $10,000. To do so, we can select a cell and provide three parameters to the FILTER function:

• The entire range to filter
• The range of cells and the comparison
• The value to return if all the values are empty

To set this in code, we set that formula in the cell:

sheet.setFormula(23, 2, 'FILTER(SalesData[[#Data], [Sales Rep]:[Amount ($)]],SalesData[[#Data], [Amount ($)]]>10000,"")');

Other important dynamic array functions include SORT, SORTBY, and UNIQUE. We can combine the UNIQUE and SORT functions into one to:

1. Find all of the sales representatives in the table, ignoring duplicates,

and

2. Sort those representatives alphabetically:

We can go even further and sort the entire order by the dollar amount using the SORTBY function:

The code to set these formulas is as simple as the FILTER function:

//Set SORT and UNIQUE functions sheet.setFormula(23, 6, 'FILTER(SalesData[[#Data], [Sales Rep]:[Amount ($)]],SalesData[[#Data], [Amount ($)]]>10000,"")');

//Set SORTBY function sheet.setFormula(33, 2, 'SORTBY(SalesData[#Data],SalesData[[#Data], [Amount ($)]],-1)');

Getting Started: Spilled Arrays

All of the formulas that you have seen in this blog so far are returning multiple results, and these results are successfully "spilling" into other cells, which are known as "spilled arrays." Spilled arrays indicate that, as long as there is space, the arrays will auto-expand to fit the data. In this case, we can add another order, and the spilled array automatically increases in size to add that entry to the list.

In some cases, there is not enough space for the array to spill. Instead, you will get a "#SPILL!" error in the cell that also shows the range where the array is trying to spill to:

Introduction to the Spilled Operator

With spilled array comes the added complexity of figuring out how to reference the result. If you entered a dynamic array formula and the result ended up in A3: A8, you could use that in other formulas. But what if the user adds data somewhere else that changes the result? That reference now becomes outdated. A simple fix for this is to use the "A1#" notation. The "#" essentially specifies the spilled array result of a dynamic array formula. In the last example,instead of using A3: A8, we would use "A3#" to reference that spilled array.

We can utilize this in our sample: say we wanted to get the total quantity of all the products sold. First, we find all the different products (using UNIQUE):

That result spills into the range H34: H37, so we can use the spill operator to refer to this range as "H34#". We can use the SUMIF formula to add the quantity of each product compared to our spilled array referencing it with the spill operator:

We can move some of the arrays around and add borders and colors to make things more precise. Here we have some fundamental product analysis with our data using dynamic arrays:

That's all there is to using dynamic arrays with SpreadJS in the designer and in code!

For more information, check our dynamic array demos here. And don't forget to download a trial of SpreadJS today so you can try out these dynamic arrays for yourself!