SCAN function

PRO

The SCAN function in Jspreadsheet Formulas Pro allows you to perform calculations on each element in an array, returning a cumulative result based on the array values. This function is highly useful when you want to carry out the same operation on multiple data points. It analyses each item in the array, applies the specified calculation, and accumulates the results. This makes handling large amounts of data simpler and more efficient.

Documentation

The SCAN function is a function that allows you to apply a calculation to each element in an array and return an accumulated result based on the values in the array.

Syntax

SCAN(initial_value, array, lambda(accumulator, value))

Parameter	Description
`initial_value`	Sets the starting value for the accumulator.
`array`	An array to be scanned.
`lambda(accumulator, value))`	A LAMBDA that is called to scan the array. The LAMBDA takes two parameters: the accumulator and the current value in the array.

Behavior

The SCAN function processes each element of an array in sequence while carrying forward an accumulator value. At each step, the provided lambda function is applied to the current array element and the accumulator, producing a new cumulative result. The function then outputs an array containing the intermediate results of every step in the scan.

Numbers: Can be accumulated using mathematical operations such as sums, products, or custom calculations.
Text: Can be combined progressively, such as concatenating strings across an array.
Booleans: Treated as numbers (TRUE = 1, FALSE = 0) and can be included in calculations.
Empty cells: Evaluated as empty strings ("") or 0 depending on the lambda logic.
Errors: Any error values in the array are propagated unless explicitly handled inside the lambda.

Common Errors

Error	Description
#VALUE!	Occurs if the lambda function is incorrectly defined or incompatible with the array element type.
#NUM!	Can occur if mathematical operations in the lambda lead to invalid results (e.g., division by zero).
#N/A	If the array includes missing values that the lambda cannot handle.

Best practices

Always define your lambda parameters clearly (accumulator, value).

Initialize with a proper initial_value (e.g., 0 for sums, 1 for products, "" for text).

Test the lambda on a smaller array before applying it to large datasets.

Handle potential error values inside the lambda to avoid breaking calculations.

Usage

A few examples using the SCAN function.

SCAN(0, A1:A5, LAMBDA(a, b, a + b))  
→ Returns a running total of values in A1:A5

SCAN(1, A1:A5, LAMBDA(a, b, a * b))  
→ Returns cumulative product of values in A1:A5

SCAN("", A1:A5, LAMBDA(a, b, a & "-" & b))  
→ Returns concatenated string of A1:A5 with dashes

SCAN(0, A1:A5, LAMBDA(a, b, IF(b>10, a+b, a)))  
→ Returns cumulative sum of numbers greater than 10

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YWMxNWFjNzkxZmNjZDNmNGRkYTRlMzMxZTM0ZjgyNDcyODkzODRkYWUyMjY3NGI0OTdkMTg0ZTM2ZGIxNTNmNGFkYmQwNjg3OWE4YjJmNWZmMmVmZGZhMTgxYTYxMjRjZjJhYjE0YmIyOWU0MjhiNDgxZmZiZTE5OGQ1NmFmNmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE9EYzNNamM0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Sales",
        "Running Total",
        "Product"
    ],
    [
        100,
        "=SCAN(0,A2:A6,LAMBDA(acc,val,acc+val))",
        "Widget A"
    ],
    [
        250,
        "",
        "Widget B"
    ],
    [
        180,
        "",
        "Widget C"
    ],
    [
        320,
        "",
        "Widget D"
    ],
    [
        90,
        "",
        "Widget E"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('YWMxNWFjNzkxZmNjZDNmNGRkYTRlMzMxZTM0ZjgyNDcyODkzODRkYWUyMjY3NGI0OTdkMTg0ZTM2ZGIxNTNmNGFkYmQwNjg3OWE4YjJmNWZmMmVmZGZhMTgxYTYxMjRjZjJhYjE0YmIyOWU0MjhiNDgxZmZiZTE5OGQ1NmFmNmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE9EYzNNamM0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Sales",
        "Running Total",
        "Product"
    ],
    [
        100,
        "=SCAN(0,A2:A6,LAMBDA(acc,val,acc+val))",
        "Widget A"
    ],
    [
        250,
        "",
        "Widget B"
    ],
    [
        180,
        "",
        "Widget C"
    ],
    [
        320,
        "",
        "Widget D"
    ],
    [
        90,
        "",
        "Widget E"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('YWMxNWFjNzkxZmNjZDNmNGRkYTRlMzMxZTM0ZjgyNDcyODkzODRkYWUyMjY3NGI0OTdkMTg0ZTM2ZGIxNTNmNGFkYmQwNjg3OWE4YjJmNWZmMmVmZGZhMTgxYTYxMjRjZjJhYjE0YmIyOWU0MjhiNDgxZmZiZTE5OGQ1NmFmNmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE9EYzNNamM0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Sales",
        "Running Total",
        "Product"
    ],
    [
        100,
        "=SCAN(0,A2:A6,LAMBDA(acc,val,acc+val))",
        "Widget A"
    ],
    [
        250,
        "",
        "Widget B"
    ],
    [
        180,
        "",
        "Widget C"
    ],
    [
        320,
        "",
        "Widget D"
    ],
    [
        90,
        "",
        "Widget E"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YWMxNWFjNzkxZmNjZDNmNGRkYTRlMzMxZTM0ZjgyNDcyODkzODRkYWUyMjY3NGI0OTdkMTg0ZTM2ZGIxNTNmNGFkYmQwNjg3OWE4YjJmNWZmMmVmZGZhMTgxYTYxMjRjZjJhYjE0YmIyOWU0MjhiNDgxZmZiZTE5OGQ1NmFmNmUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE9EYzNNamM0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Sales",
        "Running Total",
        "Product"
    ],
    [
        100,
        "=SCAN(0,A2:A6,LAMBDA(acc,val,acc+val))",
        "Widget A"
    ],
    [
        250,
        "",
        "Widget B"
    ],
    [
        180,
        "",
        "Widget C"
    ],
    [
        320,
        "",
        "Widget D"
    ],
    [
        90,
        "",
        "Widget E"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub