ncalm

function main(
  workbook: ExcelScript.Workbook,
  outputRange: string = "A1",
  newSheetName: string = "Sheet Report"
) {
  const sheets = workbook.getWorksheets();

  // Create or clear the report sheet
  let thisSheet = workbook.getWorksheet(newSheetName);
  if (thisSheet) {

ncalm

{"OrderDate","Category","SubCategory","Product","SalesAmount","OrderQuantity";40541,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40541,"Bikes","Road Bikes","Road-650 Black, 62",699.0982,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40542,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40542,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40542,"Bikes","Mountain Bikes","Mountain-100 Black, 48",3374.99,1;40542,"Bikes","Mountain Bikes","Mountain-100 Silver, 38",3399.99,1;40543,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40543,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40543,"Bikes","Road Bikes","Road-650 Red, 52",699.0982,1;40543,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40543,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40544,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40544,"Bikes","Road Bikes","Road-150 Red, 44",35

ncalm

/*
FINITE_DIFF function

array is a vector (one column or one row)

1) Check if array is a vector. If not, return #VALUE!

2) Multiply array by similarly sized array of {1, -1, ..., -1}.

3) REDUCE SUM over (2)

ncalm

FIRST = LAMBDA(arr, INDEX(arr, 1, 1));
LAST = LAMBDA(arr, INDEX(arr, 1, 1));

RESAMPLE = LAMBDA(rule,
    LAMBDA(datetime_index, values, functions, 
        LET(
            week_day, WEEKDAY(datetime_index, 2),
            date_year, YEAR(datetime_index), 
            group_function, SWITCH(
                LOWER(rule),

ncalm

import subprocess

result = subprocess.run(['pip', 'list'], capture_output=True, text=True)
lines = result.stdout.splitlines()[2:]
[tuple(line.split()[:2]) for line in lines]

ncalm

/*
For a single data with sets of 'every' columns with differing counts of rows, 
trim each set and stack them on top of each other

Dependencies:
STACKER:
https://gist.github.com/ncalm/ef7ed953571eec1475c291948aa2dbc3

EveryXtoN:
https://gist.github.com/ncalm/48b96ac45685a7897fdf0a7336b2e96b

ncalm

/*
Return every 'x' integers no larger than n, optionally skipping the first 'skip'
integers in such a sequence

e.g. 
EveryXtoN(10, 2) = {1; 3; 5; 7; 9}
EveryXtoN(10, 2, 1) = {3; 5; 7; 9}
EveryXtoN(10, 2, 2) = {5; 7; 9}

EveryXtoN(10, 3) = {1; 4; 7; 10}

ncalm

Sub GetThemeColorsForPythonPalette()
    Dim i As Integer
    Dim colorHex As String
    Dim pythonCode As String
    Dim colorsArray() As String
    Dim themeColor As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets
        If ws.Name = "Theme Colors" Then

ncalm

/* Takes a name in First Last format and produces a regex string
of variants of that name that might be found before the @ in an email
address.
Optional parameters can be set to FALSE to exclude specific variants
*/

EMAILVARIANTS = LAMBDA(
    name, [firstlast], [finitlast], [firstlinit],
          [firstdotlast], [finitdotlast], [firstunderlast],
    LET(

ncalm

/*
axis is (default) 0 for rows 1 for columns
array is a 2D array
at is a position at which to partition the array on the axis

So this returns two thunks, stacked vertically, the first
of which contains the first 5 rows of the array. The second contains the second
5 rows of the array. 

PARTITION(0)(SEQUENCE(10,10), 5)