Syntax
parameter - (Optional) A value that you want to pass to the function, such as a cell reference, string or number. You can enter up to 253 parameters.
calculation - (Required) The formula you want to execute and return as the result of the function. It must be the last argument and it must return a result.
Tip
We used a dynamic array formula in the example, you need to enable it with the following code
Basic Usage
Create the Lambda function in a cell
Add the Lambda to the Name Manager, then use in a cell.
Example
Convert Fahrenheit to Celsius
Find the hypotenuse
Count words
Compute the volume of a sphere
var data = {
values: {
1: { 1: "Examples" },
3: { 1: "Example 1: Convert Farenheit to Celsius", 8: "Convert Celsius to Farenheit" },
4: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
5: { 1: "Name:", 3: "ToCelsius", 8: "Name:", 10: "ToFarenheit" },
6: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
7: {
1: "Comment:",
3: "Convert a Farenheit temperature to Celsius",
8: "Comment:",
10: "Convert a Celsius temperature to Farenheit",
},
8: {
1: "Refers To:",
3: "=LAMBDA(temp,(5/9)*(temp-32))",
8: "Refers To:",
10: "=LAMBDA(temp,(9/5)*temp+32)",
},
9: { 1: "Data", 3: "Formula", 4: "Result", 8: "Data", 10: "Formula", 11: "Result" },
10: { 1: 104, 8: 40 },
11: { 1: 86, 8: 30 },
12: { 1: 68, 8: 20 },
13: { 1: 50, 8: 10 },
14: { 1: 32, 8: 0 },
16: { 1: "Example 2: Find the hypotenuse", 8: "Find the hypotenuse using a range" },
17: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
18: { 1: "Name:", 3: "Hypotenuse", 8: "Name:", 10: "Hypotenuse2" },
19: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
20: {
1: "Comment:",
3: "Returns the length of the hypotenuse of a right triangle",
8: "Comment:",
10: "Returns the length of the hypotenuse of a right triangle",
},
21: {
1: "Refers To:",
3: "=LAMBDA(a,b,SQRT(a^2+b^2))",
8: "Refers To:",
10: "=LAMBDA(a,b,IF(AND(ISOMITTED(b),COUNT(a)=2),SQRT(SUM(a^2)),SQRT(a^2+b^2)))",
},
22: {
1: "Data",
3: "Formula",
4: "Result",
8: "Data",
10: "Formula",
11: "Result",
12: "Result2",
15: "Same data inverted, refereced in columns:",
},
23: { 1: 3, 2: 4, 8: 3, 9: 4, 14: "Data", 15: 3, 16: 5, 17: 7, 18: 9 },
24: { 1: 5, 2: 12, 8: 5, 9: 12, 15: 4, 16: 12, 17: 24, 18: 40 },
25: { 1: 7, 2: 24, 8: 7, 9: 24, 14: "Result" },
26: { 1: 9, 2: 40, 8: 9, 9: 40, 14: "Result2" },
27: { 10: "This version makes the 2nd parameter optional and calculates the result using a 2-cell range." },
29: { 1: "Example 3: Count words", 8: "Count words in a range" },
30: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
31: { 1: "Name:", 3: "CountWords", 8: "Name:", 10: "CountWordsRange" },
32: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
33: {
1: "Comment:",
3: "Returns the word count in a text string",
8: "Comment:",
10: "Returns the word count in a cell range",
},
34: {
1: "Refers To:",
3: '=LAMBDA(text,IF(LEN(TRIM(text))=0,0,LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))+1))',
8: "Refers To:",
10: "=LAMBDA(range,SUM(CountWords(range)))",
},
36: { 1: "Data", 3: "Formula", 4: "Result", 8: "Data", 10: "Formula:", 11: "Result:" },
37: { 1: "Something wicked this way comes.", 8: "Something wicked this way comes." },
38: { 1: "I came, I saw, I conquered.", 8: "I came, I saw, I conquered." },
39: {
1: "A quick brown fox jumped over the lazy dog.",
8: "A quick brown fox jumped over the lazy dog.",
},
40: { 1: "Use the Force, Luke!", 8: "Use the Force, Luke!" },
43: { 1: "Example 4: Find the date for Thanksgiving", 8: "Find the date for Easter" },
44: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
45: { 1: "Name:", 3: "ThanksgivingDate", 8: "Name:", 10: "EasterDate" },
46: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
47: {
1: "Comment:",
3: "Returns the date Thanksgiving in the USA falls on for a given year",
8: "Comment:",
10: "Returns the date Easter in the USA falls on for a given year",
},
48: {
1: "Refers To:",
3: '=LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy"))',
8: "Refers To:",
10: '=LAMBDA(year,FLOOR("5/"&DAY(MINUTE(year/38)/2+56)&"/"&year,7)-34)',
},
49: { 8: "Date", 10: "Formula", 11: "Result" },
50: { 1: "Data", 3: "Formula", 4: "Result" },
57: { 1: "Example 5: Get the duplicate values in a range or array", 8: "Generate a random GUID" },
58: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
59: { 1: "Name:", 3: "GetDuplicates", 8: "Name:", 10: "Guid" },
60: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
61: {
1: "Comment:",
3: "Gets the duplicate values in a range or array",
8: "Comment:",
10: "Generate a random GUID",
},
62: {
1: "Refers To:",
3: "=LAMBDA(values, UNIQUE(FILTER(values, COUNTIFS(values,values)>1)))",
8: "Refers To:",
10: '=LAMBDA(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8)))',
},
64: { 1: "Data", 3: "Formula", 4: "Result" },
65: { 1: "jack" },
66: { 1: "jill" },
67: { 1: "jason" },
68: { 1: "jack", 8: "Formula" },
69: { 1: "alex", 8: "Result" },
70: { 1: "allen" },
71: { 1: "alex" },
73: { 1: "Example 6: Compute the volume of a sphere" },
74: { 1: "Define the following in Name Manager:" },
75: { 1: "Name:", 3: "SphereVolume" },
76: { 1: "Scope:", 3: "Workbook" },
77: { 1: "Comment:", 3: "Compute the volume of a sphere" },
78: { 1: "Refers To:", 3: "=LAMBDA(r, 4/3*PI()*r^3)" },
79: { 1: "Data", 3: "Formula", 4: "Result" },
},
formulas: {
10: { 3: "FORMULATEXT(E11)", 4: "TOCELSIUS(B11)", 10: "FORMULATEXT(L11)", 11: "TOFARENHEIT(I11)" },
11: { 3: "FORMULATEXT(E12)", 4: "TOCELSIUS(B12)", 10: "FORMULATEXT(L12)", 11: "TOFARENHEIT(I12)" },
12: { 3: "FORMULATEXT(E13)", 4: "TOCELSIUS(B13)", 10: "FORMULATEXT(L13)", 11: "TOFARENHEIT(I13)" },
13: { 3: "FORMULATEXT(E14)", 4: "TOCELSIUS(B14)", 10: "FORMULATEXT(L14)", 11: "TOFARENHEIT(I14)" },
14: { 3: "FORMULATEXT(E15)", 4: "TOCELSIUS(B15)", 10: "FORMULATEXT(L15)", 11: "TOFARENHEIT(I15)" },
23: {
3: "FORMULATEXT(E24)",
4: "HYPOTENUSE(B24,C24)",
10: "FORMULATEXT(L24)",
11: "HYPOTENUSE2(I24:J24,)",
12: "HYPOTENUSE2(I24,J24)",
},
24: {
3: "FORMULATEXT(E25)",
4: "HYPOTENUSE(B25,C25)",
10: "FORMULATEXT(L25)",
11: "HYPOTENUSE2(I25:J25,)",
12: "HYPOTENUSE2(I25,J25)",
},
25: {
3: "FORMULATEXT(E26)",
4: "HYPOTENUSE(B26,C26)",
10: "FORMULATEXT(L26)",
11: "HYPOTENUSE2(I26:J26,)",
12: "HYPOTENUSE2(I26,J26)",
15: "HYPOTENUSE2(P24:P25,)",
16: "HYPOTENUSE2(Q24:Q25,)",
17: "HYPOTENUSE2(R24:R25,)",
18: "HYPOTENUSE2(S24:S25,)",
},
26: {
3: "FORMULATEXT(E27)",
4: "HYPOTENUSE(B27,C27)",
10: "FORMULATEXT(L27)",
11: "HYPOTENUSE2(I27:J27,)",
12: "HYPOTENUSE2(I27,J27)",
15: "HYPOTENUSE2(P24,P25)",
16: "HYPOTENUSE2(Q24,Q25)",
17: "HYPOTENUSE2(R24,R25)",
18: "HYPOTENUSE2(S24,S25)",
},
37: {
3: "FORMULATEXT(E38)",
4: "COUNTWORDS(B38)",
10: "FORMULATEXT(L38)",
11: "COUNTWORDSRANGE(I38:J41)",
},
38: { 3: "FORMULATEXT(E39)", 4: "COUNTWORDS(B39)" },
39: { 3: "FORMULATEXT(E40)", 4: "COUNTWORDS(B40)" },
40: { 3: "FORMULATEXT(E41)", 4: "COUNTWORDS(B41)" },
50: { 8: "YEAR(NOW())", 10: "FORMULATEXT(L51)", 11: "EASTERDATE(I51)" },
51: {
1: "YEAR(NOW())",
3: "FORMULATEXT(E52)",
4: "THANKSGIVINGDATE(B52)",
8: "I51+1",
10: "FORMULATEXT(L52)",
11: "EASTERDATE(I52)",
},
52: {
1: "B52+1",
3: "FORMULATEXT(E53)",
4: "THANKSGIVINGDATE(B53)",
8: "I52+1",
10: "FORMULATEXT(L53)",
11: "EASTERDATE(I53)",
},
53: {
1: "B53+1",
3: "FORMULATEXT(E54)",
4: "THANKSGIVINGDATE(B54)",
8: "I53+1",
10: "FORMULATEXT(L54)",
11: "EASTERDATE(I54)",
},
54: {
1: "B54+1",
3: "FORMULATEXT(E55)",
4: "THANKSGIVINGDATE(B55)",
8: "I54+1",
10: "FORMULATEXT(L55)",
11: "EASTERDATE(I55)",
},
55: {
1: "B55+1",
3: "FORMULATEXT(E56)",
4: "THANKSGIVINGDATE(B56)",
8: "I55+1",
10: "FORMULATEXT(L56)",
11: "EASTERDATE(I56)",
},
65: { 3: "FORMULATEXT(E66)", 4: "GETDUPLICATES(B66:B72)" },
68: { 10: "FORMULATEXT(K70)" },
69: { 10: "GUID()" },
80: { 2: "SEQUENCE(11)", 3: "FORMULATEXT(E81)", 4: "SPHEREVOLUME(C81#)" },
},
cellStyles: {
"B2:C2": 0,
"B4:D4": 1,
"I4:K4": 1,
L4: 2,
"B5:E5": 3,
"I5:L5": 3,
"B6:B9": 4,
"C6:C9": 5,
"D6:D9": 6,
"E6:E9": 7,
"I6:I9": 4,
"J6:J9": 5,
"K6:K9": 6,
"L6:L9": 7,
B10: 8,
C10: 9,
"D10:E10": 10,
I10: 8,
J10: 9,
"K10:L10": 10,
B11: 11,
C11: 12,
D11: 13,
E11: 14,
I11: 11,
J11: 12,
"K11:L11": 13,
"B12:B14": 15,
"C12:C14": 16,
"D12:D14": 17,
"E12:E14": 18,
"I12:I14": 15,
"J12:J14": 16,
"K12:L15": 17,
B15: 19,
C15: 20,
D15: 21,
E15: 22,
I15: 19,
J15: 20,
"B17:D17": 1,
"E17:E18": 2,
"I17:K17": 1,
L17: 2,
"B18:E18": 2,
"I18:L18": 3,
"B19:B22": 4,
"C19:C22": 5,
"D19:D22": 6,
"E19:E22": 7,
"I19:I22": 4,
"J19:J22": 5,
"K19:K22": 6,
"L19:L22": 23,
"M19:M22": 7,
B23: 24,
C23: 25,
D23: 26,
E23: 27,
I23: 28,
"J23:L23": 29,
M23: 30,
"P23:S23": 31,
"B24:C27": 32,
"D24:E27": 17,
"I24:J27": 32,
"K24:M27": 17,
O24: 26,
"P24:S25": 32,
O25: 25,
"O26:O27": 26,
"P26:S27": 17,
"K28:M28": 33,
"B30:D30": 1,
"E30:E31": 2,
"I30:K30": 1,
"B31:E31": 2,
"I31:J31": 2,
"B32:B34": 4,
"C32:C34": 5,
"D32:D34": 6,
"E32:E34": 7,
"I32:I34": 4,
"J32:J34": 5,
"K32:K34": 6,
"L32:L34": 7,
B35: 34,
C35: 35,
D35: 36,
E35: 37,
I35: 34,
J35: 35,
K35: 38,
L35: 39,
B36: 40,
C36: 41,
D36: 42,
E36: 43,
I36: 40,
J36: 41,
K36: 44,
L36: 45,
B37: 46,
C37: 31,
D37: 25,
E37: 47,
I37: 24,
J37: 25,
"K37:L37": 48,
"B38:B41": 49,
"C38:C41": 16,
"D38:E41": 17,
"I38:I41": 49,
"J38:J41": 16,
"K38:L38": 17,
"B44:D44": 1,
"I44:K44": 1,
"L44:L45": 2,
B45: 2,
"I45:L45": 2,
"B46:B48": 4,
"C46:C48": 5,
"D46:D48": 6,
"E46:E48": 7,
"I46:I49": 4,
"J46:J49": 5,
"K46:K48": 6,
"L46:L48": 7,
B49: 34,
C49: 35,
D49: 36,
E49: 37,
K49: 50,
L49: 51,
B50: 40,
C50: 41,
D50: 42,
E50: 43,
"I50:J50": 52,
K50: 48,
L50: 53,
B51: 54,
C51: 52,
D51: 26,
E51: 27,
"I51:I56": 49,
"J51:J56": 16,
"K51:L56": 17,
"B52:B56": 49,
"C52:C56": 16,
"D52:E56": 17,
"B58:D58": 1,
"I58:K58": 1,
"L58:L59": 2,
B59: 2,
"I59:L59": 2,
"B60:B62": 4,
"C60:C62": 5,
"D60:D62": 6,
"E60:E62": 7,
"I60:I62": 4,
"J60:J62": 5,
"K60:K62": 6,
"L60:L62": 7,
B63: 34,
C63: 35,
D63: 36,
E63: 37,
I63: 34,
J63: 35,
K63: 36,
L63: 37,
B64: 40,
C64: 41,
D64: 42,
E64: 43,
"I64:I67": 55,
"J64:J67": 56,
"K64:K67": 57,
"L64:L67": 58,
B65: 59,
C65: 60,
"D65:E65": 10,
"B66:B72": 49,
C66: 16,
"D66:E67": 17,
"C67:C72": 61,
I68: 40,
J68: 41,
K68: 42,
L68: 43,
I69: 62,
J69: 63,
"K69:K70": 17,
I70: 8,
J70: 9,
"B74:D74": 1,
B75: 2,
"B76:B79": 4,
"C76:C79": 5,
"D76:D78": 6,
"E76:E78": 7,
D79: 50,
E79: 51,
B80: 59,
C80: 60,
"D80:E80": 64,
"B81:B91": 49,
"C81:C91": 16,
"D81:E91": 17,
},
styles: {
records: [
{ foreColor: 0, font: 0, border: [null, null, 0] },
{ foreColor: 0, font: 1, border: [null, null, 1] },
{ foreColor: 0, font: 1 },
{ foreColor: 0, font: 1, border: [null, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, null, 2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, 2, 2] },
{ backColor: 3, font: 1, border: [2, null, 2, 2] },
{ backColor: 3, font: 1, border: [2, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, null, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, 2, 2, 2] },
{ backColor: 6, foreColor: 5, border: [null, null, 3, 2] },
{ backColor: 6, foreColor: 5, border: [null, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [null, 3, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [null, 2, 3, 3] },
{ backColor: 6, foreColor: 5, border: [3, null, 3, 2] },
{ backColor: 6, foreColor: 5, border: [3, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 3, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 2, 3, 3] },
{ backColor: 6, foreColor: 5, border: [3, null, 2, 2] },
{ backColor: 6, foreColor: 5, border: [3, 3, 2] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 3, 2, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 2, 2, 3] },
{ backColor: 3, font: 1, border: [2, null, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, 4, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [4] },
{ backColor: 4, foreColor: 1, font: 1, border: [4, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, null, 3, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, null, 3] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, 4, 3, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, 3] },
{ backColor: 6, foreColor: 5, border: [3, 3, 3, 3] },
{ backColor: 9, border: [5, 5, 5, 5] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, null, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, null, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, 2] },
{ backColor: 3, font: 1, border: [2, null, null, 2] },
{ backColor: 3, font: 1, border: [2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, null, 2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, 2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, null, 2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, 2, 2] },
{ backColor: 3, font: 1, border: [null, null, 2, 2] },
{ backColor: 3, font: 1, border: [null, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, 3, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, 4] },
{ backColor: 4, foreColor: 1, font: 1, wordWrap: true, border: [2] },
{ backColor: 6, foreColor: 5, border: [3, null, 3, 3] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, null, 2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1 },
{ backColor: 4, foreColor: 1, font: 1, wordWrap: true, border: [2, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, null, 4] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, null, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, null, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, null, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, 2] },
{ backColor: 6, foreColor: 5, border: [3, null, 3] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, null, null, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, 2, 2, 2] },
],
borders: [
{ color: "#accdea", style: 5 },
{ color: "#9bc3e6", style: 2 },
{ color: "#000000", style: 1 },
{ color: "#7f7f7f", style: 1 },
{ color: "#9bc3e6", style: 1 },
{ color: "#b2b2b2", style: 1 },
],
colors: [
"#44546a",
"#ffffff",
"#4472c4",
"#d9e3f2",
"#5b9bd5",
"#3f3f76",
"#ffcc99",
"#fa7d00",
"#f2f2f2",
"#ffffcc",
],
fonts: ["700 17.3px Calibri", "700 14.7px Calibri"],
},
others: {
columnWidth: {
0: 30,
1: 86,
2: 86,
3: 186,
4: 179,
5: 39,
6: 39,
7: 39,
8: 86,
9: 86,
10: 275,
11: 187,
12: 112,
13: 39,
15: 68,
16: 68,
17: 68,
18: 68,
},
rowHeight: { 1: 24, 2: 21, 3: 21, 16: 21, 29: 21, 43: 21, 57: 21, 73: 21 },
spans: [
"B78:C78",
"B79:C79",
"B80:C80",
"B70:C70",
"I70:J70",
"B71:C71",
"B72:C72",
"B76:C76",
"B77:C77",
"K63:L68",
"B65:C65",
"B66:C66",
"B67:C67",
"B68:C68",
"B69:C69",
"I69:J69",
"B61:C61",
"I61:J61",
"B62:C62",
"I62:J62",
"B63:C64",
"D63:E64",
"I63:J68",
"B55:C55",
"I55:J55",
"B56:C56",
"I56:J56",
"I59:L59",
"B60:C60",
"I60:J60",
"B52:C52",
"I52:J52",
"B53:C53",
"I53:J53",
"B54:C54",
"I54:J54",
"B49:C50",
"D49:E50",
"I49:J49",
"K49:L49",
"I50:J50",
"B51:C51",
"I51:J51",
"B47:C47",
"D47:E47",
"I47:J47",
"B48:C48",
"D48:E48",
"I48:J48",
"B40:C40",
"I40:J40",
"B41:C41",
"I41:J41",
"I45:L45",
"B46:C46",
"D46:E46",
"I46:J46",
"B37:C37",
"I37:J37",
"B38:C38",
"I38:J38",
"B39:C39",
"I39:J39",
"B34:C34",
"I34:J34",
"K34:L34",
"B35:C36",
"D35:E36",
"I35:J36",
"K35:L36",
"B31:E31",
"I31:J31",
"B32:C32",
"I32:J32",
"K32:L32",
"B33:C33",
"I33:J33",
"K33:L33",
"B23:C23",
"I23:J23",
"P23:S23",
"O24:O25",
"B30:D30",
"I30:J30",
"B21:C21",
"D21:E21",
"I21:J21",
"B22:C22",
"D22:E22",
"I22:J22",
"B18:E18",
"I18:L18",
"B19:C19",
"D19:E19",
"I19:J19",
"B20:C20",
"D20:E20",
"I20:J20",
"B14:C14",
"I14:J14",
"B15:C15",
"I15:J15",
"B17:D17",
"I17:K17",
"B11:C11",
"I11:J11",
"B12:C12",
"I12:J12",
"B13:C13",
"I13:J13",
"B9:C9",
"D9:E9",
"I9:J9",
"K9:L9",
"B10:C10",
"I10:J10",
"B7:C7",
"D7:E7",
"I7:J7",
"K7:L7",
"B8:C8",
"D8:E8",
"I8:J8",
"K8:L8",
"B2:C2",
"I4:K4",
"B5:E5",
"I5:L5",
"B6:C6",
"D6:E6",
"I6:J6",
"K6:L6",
],
},
customNames: {
CountWords: 'LAMBDA(text,IF(LEN(TRIM(text))=0,0,LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))+1))',
CountWordsRange: "LAMBDA(range,SUM(CountWords(range)))",
EasterDate: 'LAMBDA(year,FLOOR("5/"&DAY(MINUTE(year/38)/2+56)&"/"&year,7)-34)',
GetDuplicates: "LAMBDA(values, UNIQUE(FILTER(values, COUNTIFS(values,values)>1)))",
Guid: 'LAMBDA(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8)))',
Hypotenuse: "LAMBDA(a,b,SQRT(a^2+b^2))",
Hypotenuse2: "LAMBDA(a,b,IF(AND(ISOMITTED(b),COUNT(a)=2),SQRT(SUM(a^2)),SQRT(a^2+b^2)))",
SphereVolume: "LAMBDA(r, 4/3*PI()*r^3)",
ThanksgivingDate:
'LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy"))',
ToCelsius: "LAMBDA(temp,(5/9)*(temp-32))",
ToFarenheit: "LAMBDA(temp,9/5*temp+32)",
},
};
window.onload = function () {
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
// add custom name
foreachObj(data.customNames, function (name, formula) {
workbook.addCustomName(name, formula);
});
initSheet1(workbook.getSheet(0));
workbook.resumePaint();
};
function initSheet1(sheet) {
setSheetPr(sheet);
setCells(sheet);
}
function setCells(sheet) {
foreachObj(data.values, function (r, row) {
foreachObj(row, function (c, v) {
setValue(sheet, Number(r), Number(c), v);
});
});
foreachObj(data.formulas, function (r, row) {
foreachObj(row, function (c, v) {
setFormula(sheet, Number(r), Number(c), v);
});
});
foreachObj(data.cellStyles, function (ref, id) {
setStyle(sheet, ref, data.styles.records[id]);
});
}
function setValue(sheet, r, c, v) {
if (v === undefined || v === null) return;
sheet.setValue(r, c, v);
}
function setFormula(sheet, r, c, v) {
if (v === undefined || v === null) return;
sheet.setFormula(r, c, v);
}
function setStyle(sheet, ref, v) {
if (v === undefined || v === null) return;
var styles = data.styles;
var range = sheet.getRange(ref);
var foreColor = styles.colors[v.foreColor];
var backColor = styles.colors[v.backColor];
var font = styles.fonts[v.font];
var wordWrap = v.wordWrap;
if (foreColor) {
range.foreColor(foreColor);
}
if (backColor) {
range.backColor(backColor);
}
if (font) {
range.font(font);
}
if (wordWrap) {
range.wordWrap(wordWrap);
}
var border = v.border || [];
var borderTop = styles.borders[border[0]];
var borderRight = styles.borders[border[1]];
var borderBottom = styles.borders[border[2]];
var borderLeft = styles.borders[border[3]];
if (borderTop) {
range.borderTop(createLineStyle(borderTop));
}
if (borderBottom) {
range.borderBottom(createLineStyle(borderBottom));
}
if (borderLeft) {
range.borderLeft(createLineStyle(borderLeft));
}
if (borderRight) {
range.borderRight(createLineStyle(borderRight));
}
}
function setSheetPr(sheet) {
// set column width
foreachObj(data.others.columnWidth, function (index, v) {
sheet.setColumnWidth(Number(index), v);
});
// set row height
foreachObj(data.others.rowHeight, function (index, v) {
sheet.setRowHeight(Number(index), v);
});
// set spans
var spans = data.others.spans || [];
for (var i = 0; i < spans.length; i++) {
var range = sheet.getRange(spans[i]);
sheet.addSpan(range.row, range.col, range.rowCount, range.colCount);
}
}
function createLineStyle(v) {
return new GC.Spread.Sheets.LineBorder(v.color, v.style);
}
function foreachObj(obj, func) {
if (!obj) return;
var keys = Object.keys(obj);
for (var i = 0; i < keys.length; i++) {
var key = keys[i];
var v = obj[key];
func(key, v);
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
code {
border: 1px solid #000;
}
