Get Number Set Statistics with an Excel Online Script in Power Automate

#powerautomate #typescript #javascript #statistics

There are a few methods to find the Sum, Mean and Mode of arrays of numbers in Power Automate by using Child Flows or Low Code Instant Plug-Ins and Power Fx expressions.

One of the better (and fastest) ways to do this using a simple TypeScript code executed with the Excel Online “Run script” action.

If you don’t know JavaScript, it’s not too hard to generate what you need using an AI Assistant.

Create an Excel Online File to Host Your Scripts

I created one in a SharePoint Site documents folder and named it “Power Automate Scripts”

Add New Script

Click “Automate” then “New Script”

Write Your TypeScript

I tried out ChatGPT to generate my TypeScript. It didn’t work right away. Excel did not like some of the syntax, but it was fairly easy to fix by telling ChatGPT the errors that Excel reported.

I also experimented with Grok and DeepSeek, both of which gave similar results but also contained some errors. The GPTs tend to want to write a script that reads a range on your Excel sheet, so you have to specify that they are taking inputs from the “Run Script” action in a Power Automate Cloud Flow.

Here is the final working TypeScript.



function main(workbook: ExcelScript.Workbook, inputArray: number[]): {
    modes: number[],
    maxFrequency: number,
    inputLength: number,
    minValue: number,
    maxValue: number,
    mean: number,
    standardDeviation: number,
    variance: number,
    sum: number
} {
    if (inputArray.length === 0) {
        throw new Error("Input array is empty");
    }

    // Count occurrences of each number
    const frequencyMap: Record<number, number> = {};
    inputArray.forEach((num) => {
        frequencyMap[num] = (frequencyMap[num] || 0) + 1;
    });
    // Find the maximum frequency
    const maxFrequency = Math.max(...Object.values(frequencyMap));

    // Find all numbers that have the max frequency
    const modeNumbers = Object.keys(frequencyMap)
        .filter((key) => frequencyMap[parseFloat(key)] === maxFrequency)
        .map((key) => parseFloat(key));

    // Find min and max values
    const minValue = Math.min(...inputArray);
    const maxValue = Math.max(...inputArray);

    // Calculate sum
    const sum = inputArray.reduce((sum, num) => sum + num, 0); // Added sum calculation

    // Calculate mean
    const mean = inputArray.reduce((sum, num) => sum + num, 0) / inputArray.length;

    // Calculate variance
    const variance = inputArray.reduce((sum, num) => sum + Math.pow(num - mean, 2), 0) / inputArray.length;

    // Calculate standard deviation
    const standardDeviation = Math.sqrt(variance);

    return {
        modes: modeNumbers,
        maxFrequency,
        inputLength: inputArray.length,
        minValue,
        maxValue,
        mean,
        standardDeviation,
        variance,
        sum
    };
}

Paste Your TypeScript into the Excel Code Editor and Save

Check that it finds no errors in the code, name the script and save.

Add the “Run script” Action in a Power Automate Flow

Add the Excel Online (Business) Connector “Run Script” action.

Locate your Excel file

Select the script you just added from the drop down:

Pass in the simple array of numbers you want it to analyze

Test

Here I will use a large SharePoint List that has over 20k items with an “Amount” column.

I list all the records using pagination.

I select only the amount column into a simple array.

The simple array is passed into the “Run script” action and returns the following:

{
        "result": {
            "modes": [
                9593.63
            ],
            "maxFrequency": 3,
            "inputLength": 23343,
            "minValue": 1.26,
            "maxValue": 9999.54,
            "mean": 4986.318588870353,
            "standardDeviation": 2882.714208802088,
            "variance": 8310041.209629446,
            "sum": 116395634.82000065
        },
        "logs": []
    }