JAVASCRIPT
Information
Information
We've added a few Java Script based formula. More information can be found on
the help page of the JAVASCRIPT
formulas, which also has a screencastify
video available.
- JS_MAP
- JS_SORT
If you want to have more Java Script goodness added, use the Google Forms on our website.
Syntax and Usage
Syntax and Usage
JS_MAP(E1:F5;"i > 2 ? cell : cell + 1")
Outputs a range that is incremented when the relative row is larger than 2.
Syntax (JsDocs text from script)
Syntax (JsDocs text from script)
// Author: Jacob Jan Tuinstra
/**
* Display the range, mapped as you would do in JavaScript.
* @param {A1} range The range or array to be mapped
* @param {"cell + 2"} operation internal keys are; cell, row index = i, column index = j
* @param {2} iterator [optional] The number of times to iterate (only for single cells)
* return mapped range
* @customfunction
*/
function JS_MAP(range, operation, iterator) {
range = range.map ? range : [[range]];
if(iterator && typeof iterator === "number") {
range = Array.apply(null, Array(iterator)).map(function (val) {
return range[0];
});
}
return range.map ( function (row, i) {
return row.map ( function (cell, j) {
return eval(!operation ? "cell" : operation);
})
});
}
/**
* Display the range, sorted either ascendingly or descendingly.
* @param {A:E} range The range or array to be sorted
* @param {true|false} sort_order [optional] omitted or true = ascendingly, false = descendingly
* return sorted range
* @customfunction
*/
function JS_SORT(range, sort_order) {
return arguments.length === 1 ? range.sort() : (sort_order.length === 0 || sort_order !== false ? range.sort() : range.sort().reverse());
}
Examples
Examples
JS_MAP(A2:B6,"'row index: ' + i + '; column index: '+ j")
JS_MAP(A2:B6,"j === 0 ? cell : cell + 1")
JS_MAP(TRANSPOSE(SPLIT(REPT(A1&"|",A6),"|")),"cell + ' ' + (i + 1)")
JS_MAP(A1,"cell + ' ' + (i + 1)",A6)
SORT(JS_MAP(DATEVALUE(C1),"new Date(((cell + Math.floor(i/10))-25569)*24*60*60*1000).getDay() % 6 ? cell + Math.floor(i / 10) + (i % 10 === 9 ? 18 / 24 : (i % 10 + 9) / 24) : null",260))
JS_MAP(A2:A6,"SpreadsheetApp.getActive().getSheetName() + ' ' + cell")
JS_MAP(OFFSET(C1,0,0,UTIL_TOTALSHEETS()),"SpreadsheetApp.getActive().getSheets()[i].getName()")
JS_SORT({A2:A6;B2:B6})
Video
Video
Screenshot
Screenshot