I am trying to adopt functional programming style on Google Sheets and have got quite satisfactory results, such as functors, recursive functions and currying functions. However, I am stuck by the pipe function.
It is quite easy to realize the pipe function with other modern programming languages, because they allow us to wrap functions in an array, which is able to be unwrapped and returns us functions functioning as well as the original ones.
For example, with JavaScript, we have
function pipe(){
return input => [...arguments].reduce((result, func)=>func(result), input)
}
so that we are allowed to do this:
pipe(plusThree, multiplyFour, minusSeven)(2)
However, on Google Sheets, it seems that the only way creating an array is to put items in a "virtual" range (a bundle of cells), No matter whether we do MAKEARRAY or {"func", "arr"}. Unfortunately, once we put a function in a cell, it immediately executes and return an error. Therefore, the formula I tried fails
=LET(
plusOne, LAMBDA(num, num+1),
multTwo, LAMBDA(num, num*2),
pipe, LAMBDA(functions,
LAMBDA(input,
LET(
recursiveFunction,
LAMBDA(self, idx,
IF(
idx < COLUMNS(functions),
CHOOSECOLS(functions, idx)(self(self, idx+1)),
CHOOSECOLS(functions, idx)(input)
)
),
recursiveFunction(recursiveFunction, 1)))),
result, pipe({plusOne, multTwo})(5),
result
)
(My apologies to use a recursive function. It would be much easier to read if I wrote it with REDUCE, which I tried for the very first time and failed, sadly.)
The key point is that I do not have any other way unwrapping an array of functions and get the unwrapped functions in it function as well as the ones before wrapping!
And LAMBDA in Google Sheets does not allow us to have uncertain number of arguments in a function (which is soooo frustrating). Zero argument is not allowed, either. (We can use a meaningless placeholder to simulate it, though.) So, even CHOOSE seems a candidate to iterate arguments without wrapping it in an (range-like) array, our pipe function cannot use it as the key part to iterate functions as arguments.
So, I am really eager to know whether it is a way simulating pipe function on Google Sheets.
The spreadsheet I am working on is noted here, for anyone it may concern: https://docs.google.com/spreadsheets/d/1Z1_udcul2sNtzBtJbCeDlnMR2J4JymytfScKFyUtiw8/edit#gid=1987950802
Any discussions or ideas are much appreciated!
I was able to mostly recreate the LAMB namespace that's explained on this site Excel Lambda Introducing the LAMB Namespace.
I called it PIPER and added some logging and helper functionality. The latest version can be found here. (PIPER)
LAMBDA(Names,Initial,
LET(SQRT_,LAMBDA(vector,INDEX(SQRT(vector))),
LN_,LAMBDA(vector,INDEX(LN(vector))),
LOG_,LAMBDA(base,LAMBDA(vector,INDEX(LOG(vector,base)))),
LOG_10_,LAMBDA(vector,INDEX(LOG_(10)(vector))),
POWER_, LAMBDA(exponent, LAMBDA(vector, INDEX(POWER(vector, exponent)))),
RECIPROCAL_,LAMBDA(vector,INDEX(POWER_(-1)(vector))),
RECIPROCAL_SQ_,LAMBDA(vector,INDEX(POWER_(-2)(vector))),
CUBEROOT_,LAMBDA(vector, INDEX(POWER_(1/3)(vector))),
MINVERSE_,LAMBDA(m,INDEX(MINVERSE(m))),
MTRANSPOSE_,LAMBDA(m,INDEX(TRANSPOSE(m))),
MMULT_, LAMBDA(vectorA, LAMBDA(vectorB, INDEX(MMULT(vectorA, vectorB)))),
FnNames, VSTACK("sqrt", "ln", "log", "log_10", "power", "reciprocal", "reciprocal_sq", "cuberoot", "triple", "minverse", "mtranspose", "mmult"),
CHOOSER_, LAMBDA(name, CHOOSE(IFERROR(MATCH(LOWER(name), FnNames,0), name), SQRT_, LN_, LOG_, LOG_10_, POWER_, RECIPROCAL_, RECIPROCAL_SQ_, CUBEROOT_, TRIPLE, MINVERSE_, MTRANSPOSE_, MMULT_)),
STACKER_, LAMBDA(s,f,v, INDEX(VSTACK(HSTACK(s, IF(COLUMNS(v)>1, HSTACK(f, SEQUENCE(1, COLUMNS(v)-1, 2)), f)), HSTACK(INDEX(s+SEQUENCE(ROWS(v))*POWER(10, -(1 + FLOOR(LOG10(ABS(ROWS(v))))))), v)))),
REDUCER_,LAMBDA(prev,cur,INDEX(CHOOSER_(cur)(prev))),
REDUCER_LOG_,LAMBDA(prev,cur,
LET(StageNos,INDEX(prev,0,1),
StageNo,INT(MAX(StageNos)),
StageHeader,INDEX(prev,MATCH(StageNo,StageNos,0),0),
StageFn, INDEX(StageHeader,1,2),
StageVal, FILTER(FILTER(prev, StageNos>StageNo), ISBETWEEN(SEQUENCE(1, COLUMNS(prev)),2, COUNTA(StageHeader))),
NextVal, CHOOSER_(cur)(StageVal),
IFERROR(VSTACK(prev, STACKER_(StageNo+1,cur,NextVal))))),
IS_LOGIT_,LAMBDA(n,LOWER(INDEX(n,1,1))="logit"),
IS_HELP_,LAMBDA(n,OR(LOWER(INDEX(n,1,1))="help",INDEX(n,1,1)="?")),
NAMES_,LAMBDA(n, IF(IS_LOGIT_(n), FILTER(TOCOL(n), SEQUENCE(ROWS(TOCOL(n)))>1), TOCOL(n))),
IF(IS_HELP_(Names), VSTACK(, "Execute a process pipe by providing either the function IDs shown below or function names and initial value like this: PIPER({1,""triple"",8},12). A single function can be executed by providing the name of the function and no initial value. For example PIPER(""triple"",)(9)=27"&CHAR(10)&CHAR(10)&REDUCE("ID|FUNCTION",SEQUENCE(ROWS(FnNames)), LAMBDA(t,i,t&CHAR(10)&i&"|"&UPPER(INDEX(FnNames,i))))),
IF(ISBLANK(INDEX(Initial,1,1)),
CHOOSER_(Names),
IF(IS_LOGIT_(Names),
REDUCE(STACKER_(0, "initial", Initial), NAMES_(Names), REDUCER_LOG_),
REDUCE(Initial,NAMES_(Names),REDUCER_))))))
Bottom line, it uses an array or function names to identify the index for the next function in the pipe, then uses CHOOSE to get that function and pass it parameters.
As its written, the functions and their names are hardcoded into the function, but I've been working on some similar stuff lately involving multiple actions and their triggers. Each action has an associated trigger, similar to the function names in PIPER. I use it for State Management. Both the triggers and actions are easily expandable. The actions can be just a value, or a lambda function that can accept up to 3 optional parameters, based on my specific use case:
LAMBDA(state, stateCount, previousTrigSum, triggers, actions, LET(
_F1,"Calls action with various call signatures",
TakeAction, lambda(id,s,i, let(a, actions(id), if(iserror(a(s,i,id)), if(iserror(a(s,i)), if(iserror(a(s)), if(iserror(a()), a, a()), a(s)), a(s,i)), a(s,i,id)))),
_F2,"Gets sum of trigger indexes",
TriggerSum, lambda(t, sumproduct(tocol(t),sequence(counta(t)))),
_F3,"Returns which trigger ran",
WhichAction,lambda(p,c,abs(p-c)),
currentTrigSum, TriggerSum(triggers),
actionId, WhichAction(previousTrigSum, currentTrigSum),
newState, TakeAction(actionId, state, stateCount),
HSTACK(newState, stateCount+1, currentTrigSum)))
(<stateRef>, <stateCountRef>, <previousTrigSumRef>, HSTACK(A1,B1,C1), LAMBDA(id, CHOOSE(id, 25, LAMBDA(a, a+2), LAMBDA(a,b, a+b))))
I'm still trying to figure out the preferred structure, but bundling my triggers and actions up into those two parameters seems to help with a lot of things. Here's a sample sheet that goes into the State Management.