3

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(); 
}

1 Answer 1

10

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;
  }
}
2
  • I was trying to apply it when the custom formula has more than one argument and to use arrayformula like this: arrayformula(custom(A2:A,B2:B)) where my custom formula accepts for example two imput argument. Would you advise how to do it?
    – David Leal
    Sep 11, 2021 at 0:56
  • It does not really make the function work with an arrayformula : the function now handles a range in addition to a single cell value and it can be used with a range without the need to use an arrayformula : DOUBLE(A1:A) works out of the box.
    – laurent
    Feb 11, 2022 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.