Hacker News Reader

Comment by scubbo | Hacker News Reader

> Sheets doesn’t even have proper tables, much less lambdas

I never formally studied computer science, so it's possible that I'm missing some distinction between lambdas and functions, but doesn't [0] indicate that Google Sheets supports custom named functions?

[0] https://developers.google.com/apps-script/guides/sheets/func...

View on HN

The simplest answer is that "named functions" and "lambdas" are mutually exclusive, since a simple definition of a lambda is an "anonymous function," or one that doesn't have a name. That's all both practically and technically correct but I call it the "simple" answer because it kind of misses the point that named functions and lambdas are used in different ways. One articulation would be that named functions serve primarily as a means of reuse (to avoid repeatedly writing out the same thing) while lambdas serve primarily as a mean of containment (both in terms of scope and to package up a bit of logic in a form that can easily be passed).

This is all a bit more complicated in the spreadsheet world because spreadsheets have historically maintained a separation between "the document" and "extra code." In Excel, this is the separation between the spreadsheet itself and VBScript macros which might be packaged with it. In Google Sheets, it is the separation between the spreadsheet itself and AppScript.

In Excel, it is possible to implement functions in the sheet by use of the LAMBDA function in a cell. Amusingly, because of Excel's general concept of named cells, these lambdas can actually have names if you want, and can be called by those names. This allows easy implementation of e.g. recursive logic within the sheet, something that was not historically possible without the use of VBScript.

Google Sheets still doesn't have that capability, and the functions you mention are an AppScript feature that can be called from within the sheet.

You will notice in reading this that part of the confusion is that the difference between a conventional "programming language" and the spreadsheet environment means that the terms "function" and "lambda" have somewhat different meanings in spreadsheets than in a more general-purpose programming language.

> a lambda is an "anonymous function," or one that doesn't have a name.

I'd frame this slightly differently. The process of defining a function involves:

1. Creating a function object.

2. (Optional) Assigning a name to it.

Lambda is Step 1, and it happens even in programming languages that don't have a so-called "lambda" feature. Such languages always automatically do both steps. Those that do have "lambda" simply make it available to the programmer explicitly, and programmers who use it usually omit Step 2.

In Scheme and occasionally in Common Lisp it's not unusual to perform both steps explicitly and use lambda to create named functions. It's fairly common in modern Javascript too.

The thing that's new in Excel (since 2020) is the ability to create new functions at all and it's reasonable to just call that "lambda."

I think it's important to be clear that it's been possible to create new functions in Excel since time immemorial, but it used to be possible only in VBScript - the same situation as most other spreadsheet editors, e.g. Google Sheets where it must be done in AppScript.

That's a helpful perspective - thank you!

It’s mostly an ergonomic difference.

Google Sheets has custom functions in Javascript; excel has them in VBScript. Both are part of the 'product', but they’re not integrated into the spreadsheet user interface at all. Using them is more like writing an extension to the Excel/Google Docs app, than it is like editing a document.

To define a custom function you need to leave the spreadsheet UI and define it in a totally different programming language. It’s a major barrier to entry that most users don’t cross, and the context-switching is a hassle even if you’re comfortable with both.

Lambdas let you write a function that takes arguments in the same way you would write a formula that references other cells.

Reply on news.ycombinator.com