LET

This function is used to assign names to calculation results. The variable names can then be used to store intermediate calculations, values, or defining names within braces of the LET() function. To use this function, you have to define pairs of names and values associated with it and a calculation that uses them all.

By using this function, users do not have to remember what a specific range or cell reference referred to, what your calculation is supposed to do or even copy-pasting the same expression all over again.

Syntax

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

Arguments

This function has these arguments:

Data Types

Returns variant data type.

Remarks

The last argument must always be a calculation that returns a result.

Examples

The following code sample shows the basic usage of the LET function.

var formula = '=LET(x,2,x+3)';
sheet1.setValue(2, 0, 'Pair varible');
sheet1.setValue(2, 1, formula);
sheet1.setValue(3, 0, 'Result');
sheet1.setFormula(3, 1, formula);

The following code sample shows the usage of the LET local variable and custom name with the LET function.

sheet1.addCustomName('user', '="Michael"');
                
var formula = '=LET(user,"Ivy","The actual user is: "&user)';
sheet1.setValue(2, 0, 'Always use LET local variable first');
sheet1.setValue(2, 1, formula);
sheet1.setValue(3, 0, 'Result');
sheet1.setFormula(3, 1, formula);

// Use custom name if local variable is not available
var formula = '=LET(user,user,"The actual user is: "&user)';
sheet1.setValue(5, 0, 'Use custom name if local variable is not available');
sheet1.setValue(5, 1, formula);
sheet1.setValue(6, 0, 'Result');
sheet1.setFormula(6, 1, formula);

Note: The name argument should not contain a '.' and has been updated to match Excel's updated behavior. Refer Release Notes for Version 14.1.0 to learn more.