JAVASCRIPT

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

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)

// 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

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

Screenshot