Google-sheets – How to use custom function with arrayformula

google sheetsgoogle-apps-scriptgoogle-sheets-arrayformulagoogle-sheets-custom-function

I'd like to use a Google Apps Script custom function in conjunction with arrayFormula so this may be applied to a range. How do I achieve this?

Let's say I have wrote a function that will return current cell coordinates and wish to apply/run the custom function on each cell in a range (A1:A), what syntax should I use?

Cell value:

=arrayFormula(testFunction()) // works only on one cell

Code:

function testFunction(){
  return SpreadsheetApp.getActiveRange().getA1Notation(); 
}

Best Answer

In order to use a custom function with array formula, it should be able to return to get as input and return as result array of values.

The custom function provided as example has no input parameter and return a single value. Please checkout https://developers.google.com/apps-script/guides/sheets/functions. It shows how to write a simple function, DOUBLE(), and also explains how to enhance it to make it able to return an array of values, in this case, by using the Array.prototype.map JavaScript method:

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value or range of cells to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  if (input.map) {            // Test whether input is an array.
    return input.map(DOUBLE); // Recurse over array if so.
  } else {
    return input * 2;
  }
}
Related Topic