Google Sheets tutorial: Using BYROW and LAMBDA functions

When exporting email to Google Sheets, the rows will be added continuously. Now, if you want to add a new column that processes rows that are dynamically added by cloudHQ, you can use Google Sheets functions BYROW and LAMBDA.

BYROW and LAMBDA are functions allow you to perform complex calculations and transformations on data.

BYROW

BYROW is not a Google Sheets function by itself, but rather a way to perform calculations or transformations across rows in a range. It is used with other functions like FORMULATEXT, GOOGLETRANSLATE, LET, SUM, or AVERAGE that can operate on row-by-row basis. BYROW can be used to apply the same calculation to each row in a range, and then return an array of results. This can be particularly useful for analyzing data in a tabular format, where each row represents an individual record or entity.

For example, let’s say you have a table with sales data for different products and you want to calculate the total sales for each row (product). You can use BYROW in combination with the SUM function like this: 

=BYROW(A2:C, LAMBDA(x, SUM(x)))

This formula will calculate the sum of values in the range A2:C2 and can be dragged down to apply the same calculation to other rows. 

=BYROW(B2:B10,LAMBDA(r,FORMULATEXT(r)))

This formula is designed to extract the formulas as text from the range B2:B, operating on a row-by-row basis (a range like “B2:B” represents an open-ended range starting from cell B2 and extending to the last row of the sheet in column B). It uses the LAMBDA function to create a custom function that takes a single input ‘r’ and returns the formula as text using the FORMULATEXT function.

Here’s a breakdown of the formula:

  1. BYROW(B2:B, …): This part is intended to apply a custom function to each row in the range B2:B.
  2. LAMBDA(r, FORMULATEXT(r)): This part defines a custom function using LAMBDA that takes a single input ‘r’ (a cell in the range B2:B10) and returns the formula in that cell as text using the FORMULATEXT function.

LAMBDA

LAMBDA is a powerful function that allows you to create custom, reusable functions without having to write code. LAMBDA enables you to define a function with parameters, a calculation, and a return value. Once defined, you can use the LAMBDA function just like any other function.

For example, let’s say you want to create a custom function to calculate the square of a number. You can define a LAMBDA function like this: 

=LAMBDA(x, x * x)

To use this LAMBDA function, you can give it a name using the Name Manager in Excel. Let’s call it “Square”. Now you can use the custom “Square” function in your worksheet like this: 

=Square(5)

This will return the square of 5, which is 25.

In summary, BYROW is a technique used to apply calculations or transformations on a row-by-row basis in a range, while LAMBDA is a function that allows you to create custom, reusable functions.

Examples

get cloudHQ URL of the message

=BYROW(A2:A,LAMBDA(r, REGEXEXTRACT(FORMULATEXT(r), "(https://.*)"", ")))

The regular expression provided in the formula consists of the following parts:

  1. (https://.*): This part of the regular expression is looking for a substring that starts with “https://”. The .* following it means that it will match any character (except a newline) 0 or more times, effectively capturing any text that follows “https://”.
  2. `”””, “: This part of the regular expression is looking for a literal double quote followed by a comma and a space. Note that in Google Sheets, you need to use two double quotes to represent one literal double quote within a formula.

    3. Get Gmail URL of the message

    =BYROW(A2:A,LAMBDA(r, CONCATENATE("https://mail.google.com/mail/u/0/#inbox/",REGEXEXTRACT(FORMULATEXT(r), "https://.*"", ""(.*)"""))))

    This formula is written in Google Sheets and its function is to generate hyperlinks for a Gmail inbox based on specific inputs in column A.

    You can access an example spreadsheet here: https://docs.google.com/spreadsheets/d/1M_A4G2QMffFPb28ZRR8W4IHRw_-76rEzKrR2T8aubqU/edit#gid=0

    The BYROW function is used to iterate over a range of cells (in this case, cells A2 to A). With each iteration, the function takes the value from the respective row in column A and uses it as input for a LAMBDA function.

    Inside the LAMBDA function, the FORMULATEXT function is used to convert the value from each cell in the range into text.

    The REGEXEXTRACT function is then applied to the text for each cell, extracting a specific portion of the text that matches the provided Regular Expression pattern (“https://.*””, “”(.*)”””)). This pattern is used to match and extract a specific portion of the value from each cell.

    The extracted text is then concatenated to the string “https://mail.google.com/mail/u/0/#inbox/”. The CONCATENATE function is used to join the two pieces of text together.

    Ultimately, this formula generates a hyperlink to a specific mail in the Gmail inbox by matching and extracting a portion of the value from each cell in the specified range in column A, and appending it to the base URL of the Gmail inbox.

Tagged: