Apps Script Equivalent of Google Sheets Functions
If you are accustomed to using both Google Sheets functions and working with Apps Script (or JavaScript) codes, you have probably noticed some equivalences between different functions and methods.
If not, here you will discover the Apps Script equivalent of functions such as IF, LAMBDA, LET, MAP, REDUCE, and many others.
The functions are listed in alphabetical order.
AND
The AND function returns TRUE if all tests are TRUE:
=AND(1+2=3,1<2)Its Apps Script equivalent is the && operator:
1 + 2 === 3 && 1 < 2;AVERAGE
The AVERAGE function calculates the average of the values in an array:
=AVERAGE({8,45,16,25,30})Its Apps Script equivalent is:
let array = [8, 45, 16, 25, 30];
let average = array.reduce((total, value) => total + value, 0) / array.length;COUNTIF
The COUNTIF function counts the number of values in an array that match a criterion:
=COUNTIF({8;45;16;25;30},"<30")Its Apps Script equivalent could be:
[8, 45, 16, 25, 30].filter(value => value < 30).length;Or alternatively:
[8, 45, 16, 25, 30].reduce((count, value) => value < 30 ? count + 1 : count, 0);FILTER
The FILTER function allows filtering the values of an array:
=FILTER(A1:A6,A1:A6<30)Its Apps Script equivalent is the filter method:
[8, 45, 16, 25, 30].filter(value => value < 30);IF
The well-known IF function:
=IF(A1<B1,A1,B1)Its Apps Script equivalent in long form:
if (a1 < a2) {
result = a1;
} else {
result = a2;
}Or the short version with the ternary operator:
result = a1 < a2 ? a1 : a2;IFERROR
The IFERROR function returns the first argument's value if there's no error, or the second argument's value if the first one is an error:
=IFERROR(err(),123)Its Apps Script equivalent could be the ?? operator, which returns the right value if the left one is null or undefined:
[1, 2][3] ?? 123;Or the || operator, which returns the right value if the left one is 0, false, '', NaN, null, or undefined:
0 || 123;INDEX
The INDEX function returns a value based on its position in an array:
=INDEX({1;2;3;4},3)Its Apps Script equivalent is:
[1, 2, 3, 4][2];JOIN
The JOIN function allows merging an array into a string:
=JOIN(", ",{1;2;3;4})Its Apps Script equivalent is the join method:
[1, 2, 3, 4].join(', ');LAMBDA
The LAMBDA function allows creating a function:
=LAMBDA(value,value+10)Its Apps Script equivalent is the arrow function:
value => value + 10;More examples with LAMBDA or the arrow function are available on this page.
LEFT
The LEFT function allows extracting a portion from the left of a string:
=LEFT("Sheets-Pratique",6)Its Apps Script equivalent is the slice (or substring) method:
'Sheets-Pratique'.slice(0, 6);LET
The LET function allows keeping a value to avoid recalculating it several times in the formula:
=LET(value,123,value)Its Apps Script equivalent is a simple variable declaration to which a value is assigned:
let value = 123;MAP
The MAP function applies a LAMBDA function to each value in an array:
=MAP({10;20;30},LAMBDA(value,value+5))Its Apps Script equivalent is the map method:
[10, 20, 30].map(value => value + 5);MATCH
The MATCH (or XMATCH) function is used to get the position of a value in an array:
=MATCH("Sheets",{"Excel";"Google";"Sheets";"Pratique"},0)Its Apps Script equivalent is the indexOf method:
['Excel', 'Google', 'Sheets', 'Pratique'].indexOf('Sheets');MAX
The MAX function returns the largest value in an array:
=MAX({11;-44;33;-22})Its Apps Script equivalent is Math.max:
Math.max(...[11, -44, 33, -22]);MIN
The MIN function returns the smallest value in an array:
=MIN({11;-44;33;-22})Its Apps Script equivalent is Math.min:
Math.min(...[11, -44, 33, -22]);MID
The MID function extracts a portion from the middle of a string:
=MID("Google Apps Script",8,4)Its Apps Script equivalent is the slice (or substring) method:
'Google Apps Script'.slice(7, 11);OR
The OR function returns TRUE if at least one of the tests is TRUE:
=OR(1+2=4,1>2,2>1-3)Its Apps Script equivalent is the || operator:
1 + 2 === 4 || 1 > 2 || 2 > 1 - 3;REDUCE
The REDUCE function applies a LAMBDA function to each value in an array:
=REDUCE(0,{10;20;30},LAMBDA(total,value,total+value))Its Apps Script equivalent is the reduce method:
[10, 20, 30].reduce((total, value) => total + value, 0);REGEXEXTRACT
The REGEXEXTRACT function allows extracting one or more parts of a string based on a regular expression:
=REGEXEXTRACT("JavaScript","[A-Z][a-z]*$")Its Apps Script equivalent is the match method:
'JavaScript'.match(/[A-Z][a-z]*$/g);REGEXMATCH
The REGEXMATCH function allows verifying if a string matches a regular expression:
=REGEXMATCH("test","[rt]est")Its Apps Script equivalent is the test method:
/[rt]est/.test('test');REGEXREPLACE
The REGEXREPLACE function allows replacing parts of a string based on a regular expression:
=REGEXREPLACE("JavaScript","[Jva]{4}","Apps ")Its Apps Script equivalent is the replace method:
'JavaScript'.replace(/[Jva]{4}/g, 'Apps ');RIGHT
The RIGHT function allows extracting a portion from the right of a string:
=RIGHT("Sheets-Pratique",8)Its Apps Script equivalent is the slice method:
'Sheets-Pratique'.slice(-8);ROUND
The ROUND function rounds a value to the nearest integer:
=ROUND(12.345)Or based on the specified number of decimals:
=ROUND(12.345,2)Its Apps Script equivalent is Math.round:
Math.round(12.345); // Integer
Math.round(12.345 * 100) / 100; // 2 decimalsSame principle for FLOOR (Math.floor or parseInt) and CEILING (Math.ceil).
SORT
The SORT function is used to sort the values of an array:
=SORT({1;10;2;20;0},1,0)Its Apps Script equivalent is the sort method:
[1, 10, 2, 20, 0].sort((a, b) => b - a);SPLIT
The SPLIT function divides a string into an array:
=SPLIT("11/22/33","/")Its Apps Script equivalent is the split method:
'11/22/33'.split('/');SUBSTITUTE
The SUBSTITUTE function replaces parts of a string:
=SUBSTITUTE("Excel-Pratique","Excel","Sheets")Its Apps Script equivalent is the replace (or replaceAll) method:
'Excel-Pratique'.replace('Excel', 'Sheets');SUM
The SUM function:
=SUM({8;45;16;25;30})Its Apps Script equivalent is:
[8, 45, 16, 25, 30].reduce((total, value) => total + value, 0);SUMIF
The SUMIF function sums values that meet a criterion:
=SUMIF({8;45;16;25;30},"<30")Its Apps Script equivalent is:
[8, 45, 16, 25, 30].reduce((total, value) => value < 30 ? total + value : total, 0);UNIQUE
The UNIQUE function removes duplicates from an array:
=UNIQUE({1;4;3;1;2;3;1})Its Apps Script equivalent could be:
[...new Set([1, 4, 3, 1, 2, 3, 1])];