Is ArrayFormula not working with custom functions returning arrays?

Question

If a write a simple Google Apps Script custom function like the following

function colFunc(x) {
  
   if(Array.isArray(x)) {
     return x.map(v => colFunc(v))
   }

  return "A";
}

I can call colFunc() within an ArrayFormula and everything is working perfectly as expected (Google Sheet will fill the entire column with "A").

=ArrayFormula(colFunc(A1:A))

Please note that, in this specific example, the only use of the parameter x is to allow me to recognize when the function has been called on the entire column or on a single cell.

Let's now assume I want to create a function that will return 3 values for 3 horizontal cell. The formula code would be:

function cell3Func(x) {

  return [["a", "b", "c"]];
}

and if I call it in the standard way:

=cell3Func(A1)

I get the expected result distributed over 3 horizontally aligned cells with values "a", "b", and "c".

Now, what if I want to write a formula that fills 3 cells (horizontally placed) while being able to leverage the power of the ArrayFormula() for an entire column?

Based on the previous 2 examples, I would write a function like this:

function col3cellFunc(x) {

   if(Array.isArray(x)) {
     return x.map((v,i) => col3cellFunc(v[i]))
   }

   return [["a", "b", "c"]];
}

Well, Google Sheet doesn't like this option and simply display as a result an empty cell. I was not able to find and documentation about this kind limitation for ArrayFormula(). I know there are several limits (Query(), Match(), Index(), ...), but I can't find anything about this way of using it.

Obviously my real functions are doing a lot more than just returning "a", "b" and "c". If the issue was to fill 3 columns with "a", "b", and "c" there are several other and simpler solutions.

Any suggestion or recommendation?

TheMaster TheMaster · Accepted Answer · 2020-12-26T07:51:12

All custom functions are array formulas. There is no need to explicitly specify that it is a array formula using =ARRAYFORMULA(). The only condition to return the array is that it returns a 2D array^Related. col3cellFunc doesn't do that. x is a 2D array and the script is mapping each element to a another 2D array, which will result in a 4D array. If the intention is to create a column of [a,b,c], You could simply use

function col3cellFunc(x) {
   return x.map(row => ['a','b','c'])//Note how  each row is mapped to a 1D array and NOT a 2D one
}

If the intention is different, You should devise a optimal strategy to mould the 2D input to a desired 2D output.

Is ArrayFormula not working with custom functions returning arrays?

1 Answers