1

I know there are alternatives to this. You can simply pass the variables you want to pass to the custom function. But that's not what I want to happen. Something like:

=CUSTOMFUNCTION(A2,A3)(A4,A5)

Dynamically generating functions from a set of parameters. I can see this being useful in some places, but it definitely is niche.

I've tried passing the following:

return LAMBDA(var1, var1 + 1) // returns a "Undefined function" Error.

return "LAMBDA(var1, var1 + 1)" // returns a string. I tried appending a (A4) in Google Sheets, doesn't work.

return (var1) => var1 + 1 // returns a string, surprisingly.

I'm at least 90% certain that what I'm thinking of here is currently unsupported. But it would still be great if I can get an answer.

Improve this question
4
  • 1
    I do not think that is possible with a custom function because you cannot use Range.setFormula() in that context. Make sure you are not asking an XY question. Commented Nov 3, 2022 at 15:33
  • "LAMBDA(var1, var1 + 1)". Leaving aside the fact that native functions can't be called in apps script, may I respectfully suggest that the syntax of your LAMBDA function is invalid on two counts. First, the variable ("var1") ends in a digit and (based on my limited testing) this seems to be a no-no - "var" is OK, "var1q" is OK, but "var1" will result in an error. Second, you need to include the cell reference containing the value of the variable. So, a valid function might be =lambda(var,var+1)(A2), where cell A2 contains the value of "var". Commented Nov 4, 2022 at 7:27
  • Have you considered writing a script that uses setFormula(formula) Ref? Commented Nov 5, 2022 at 1:45
  • Thanks for everyone's suggestions! I should have made it clear that this was mostly a hypothetical and the way I wanted it to work won't. I wanted that specific syntax, but I'll have to go a different route then. I'll take both your suggestions and just use setFormula() on a different cell. Also, good point @Tedinoz about the variable names, should have just stuck with foo and bar. Commented Nov 5, 2022 at 6:26

1 Answer 1

Reset to default
1

That cannot be done, because Google Sheets custom functions can only return numbers, text strings, Booleans, Date objects, and arrays that contain those types of values. They cannot return lambda functions or formulas, and cannot use Range.setFormula() or other such methods.

What you can do, however, is to return lambda functions within let(). Named lambdas are first-class citizens so they let you implement closures, true recursion and other types of abstractions. They make the Google Sheets formula language Turing complete even if you do not use named functions.

See How to implement recursion in a Google Sheets formula at Stack Overflow for some examples.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.