Post number 6 of “The Practical Google Sheets Guide”
These blog posts are part of research I’m doing for a potential new book, hopefully allowing me to further my love for spreadsheets. Theses posts are hoping to provide a practical cookbook of examples to the reader and allow me to learn what people are interested in and how to approach different subjects working with Google Sheets.
TLDR: This post is a breakdown in how to use and implement Lambda functions in your google sheets. Instead of a step by step guide on how to create something specific, this post will give you the details on why and how to implement Lamdba as part of your google sheets.
If this post interests you, maybe checkout the some of the earlier posts including:
Introduction
It took me a little while to get my head around similar Lambda functions in Python, so when I saw you could also use them in google sheets, I thought it would be a good chance to expand my understanding of them, with a post taking a deep dive. Lambda functions are a recent addition to Google Sheets and allow you to create custom, reusable functions within google sheets, without needing to use the usual scripting or coding methods. We are hoping this post will give you a good foundation on understanding Lambda’s, allow you to understand their purpose while understanding there advantages over usual sheets functions.
Benefits of Lambda's in Google Sheets:
- No need for AppScript or macros
- Makes complex formulas reusable and easier to manage
- Can help with consistency and reduce errors in spreadsheets
- Avoid repeating the same logic or calculations multiple times in a spreadsheet
- Named functions, allow users to give their custom functions a name, making them easily recognisable and reusable
Limitations of Lambda's in Google Sheets:
- Lambdas are designed for use within a single Google Sheet
- When you define a custom Lambda function, it doesn't automatically generate tooltips with argument descriptions
- Using excessively large Lambda’s can cause your spreadsheets to run slowly
- Custom Google Sheets functions can't return other functions or formulas, including Lambda functions
What You Need To Know To Get This Done?
This post is pretty basic and will run you through get started with Lambda functions. You will only need a basic understanding of google sheets and how to implement formulas and functions.
The Basic Syntax For Lambda Functions
The syntax for Lambda functions is simple but sometimes can be confusing.
=LAMBDA([parameters], expression)
- Parameters: You can define multiple parameters separated by commas, but adding any parameters to your Lambda is optional.
- Expression: This is where you perform the calculation or operation, most of the time using the parameters.
Let’s start with a really easy example:
=LAMBDA(a, b, a + b)(2, 3)
In the example above, we have assigned both x and y as parameters, then provided an expression that is used to add the two parameters together. We then add a second set of parenthesis that assign values to the parameters.
Thats a really basic example, but hopefully if we give two more, which are a little more complex, you can start to see how they can start to be useful in your sheets.
=LAMBDA(n, IF(MOD(n, 2) = 0, "Even", "Odd"))(7)
In the above example, we can start to see how we can chain functions together in the expression part of the Lambda. In the example, we have only defined one parameter which is “n”, with the expression being, divide n by 2 using the MOD function, and if the output is 0, we know that n is an even number, otherwise it is odd.
=LAMBDA(a, b, IF(LEN(a) > LEN(b), a, b))("apple", "banana")
In this example we have two parameters and we compare the length between the two, although we provide the parameters as strings directly in the Lambda, there is no reason why we cannot provide cells in our sheet as the parameter values.
A More Detailed Example
Ok, now we know the basic implementation of Lambda’s we can now look at a more real world example. I wasn’t sure what product to calculate and create my Lambda for, so I went with hamburgers. In the image below we have a really basic sheet where we have the types of hamburgers, the quantity sold and the unit price for each hamburger. In this example, we are going to use a Lambda to calculate the total cost, while also including sales tax.
To create our Lambda we will use column D to add it, where will multiply the quantity by the price, and then multiply that value by 8%, which is our fictional tax value. So firstly, our parameters are Price(column C) and Quantity(column B). Our Lambda for the second row will look something like this:
=LAMBDA(price, quantity, price * quantity * 1.08)(C2, B2)
Where:
price, quantity- are our parametersprince * quantity * 1.08- are our expression(C2, B2)- are the values we will use for row 2
With our Lambda implemented copied to all the rows in column D, our sheet now looks like the image below:
Expanding Usability
There are one or two ways that we can add to our Lambda functions to make sure they are clear in there purpose and anyone using the sheet will be able to understand what they do.
First thing we can do is use the LET function as part of our Lambda to give it a descriptive name. In the example below, we have taken our calculation we made earlier and with the LET function given it the descriptive name of TotalCostWithTax. Here is how we would implement it below:
=LET(TotalCostWithTax, LAMBDA(price, quantity, price * quantity * 1.08), TotalCostWithTax(B2, C2))
This improves readability and makes it easier for others to understand your spreadsheet and what your Lambda does.
There is also the option to use the N function to add notes. This is good practice for any large formula or function, we simply add + N and a description in brackets.
In our example above, we could add a description like the following:
=LAMBDA(price, quantity, price * quantity * 1.08)(C2, B2)+N("This Lambda calculates the total including the current 8% tax rate")
Knowing the basics goes a long way in using and starting to implement them further in your google sheet applications. It is similar to any function you might be using in your sheets, the more you use them, the more comfortable you become with them and look to utilise them further.
About The Author
The post is written by Vincent Sesto, a Aussie Software Engineer, living and working in Auckland, New Zealand. If you are interested in my authors page on Amazon, feel free to checked it out at the following link:
https://us.amazon.com/stores/author/B073R3VW2G