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

Here is to 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.