How to use a custom function with an ArrayFormula

1
votes

I want to write a function that can be used inside an ArrayFormula. My table is like this:

 | A | B | C |
1| a |   |   |
2| b |   |   |
3| c |   |   |

First I wrote a simple function to return the input (so I know it works inside the ArrayFormula):

function retAddress(cell){
  return cell;
}

On B1 I wrote =ArrayFormula(retAddress(address(row(B:B),column(A:A),4))) and apparently it worked as expected, it returned each address, like this:

 | A | B | C |
1| a | A1|   |
2| b | A2|   |
3| c | A3|   |

Now, on column C, I wanted to return the values of column A, so I wrote a function like this:

function retValue(cell){
  var cellRang = SpreadsheetApp.getActive().getRange(cell);
  return cellRang.getValue();
}

And on C1 I wrote =ArrayFormula(retValue(address(row(B:B),column(A:A),4))) but it gives me error Exception: Range not found (line 2)., which is the line with getRange(cell) method.

If I write the function without ArrayFormula like this:

On C1, =retValue(address(row(C1),column(A:A),4))

On C2, =retValue(address(row(C2),column(A:A),4))

On C3, =retValue(address(row(C3),column(A:A),4))

I get the expected result:

 | A | B | C |
1| a | A1| a |
2| b | A2| b |
3| c | A3| c |

So, how to make it work in ArrayFormula?

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulascustom-function

1 Answers

3
votes

Issue:

SpreadsheetApp.getActive().getRange(cell)

cell is array if you provide a array input. getRange method expects a single string as input.

Solution:

  • map the array to single value

References:

Snippet#1:

function retValue(cell){
  if(cell.map) {
    return cell.map(retValue);
  } else {
    var cellRang = SpreadsheetApp.getActive().getRange(cell);
    return cellRang.getValue();
  }
}

Snippet#2:

Note that in the previous snippet you're calling getValue() 1 time per each cell in the input array. This is extremely slow. Better way is to call it as a batch:

=retValues("A1:B4")

function retValues(cell){//modified
  var cellRang = SpreadsheetApp.getActive().getRange(cell);
  return cellRang.getValues();//modified
}
  • Note that:
    • Only 1 call to getValues() is made.
    • Formula returns a array without explicit use of =ARRAYFORMULA(). All custom formulas are by default, array formulas, but they need to be configured to return values as arrays in apps script.