How to use a custom function with an ArrayFormula

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 answer

  • answered 2019-09-15 16:06 TheMaster

    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.