How to use a custom function with an ArrayFormula

Question

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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 15, 2019 at 16:06

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adriano_Pinaffo Over a year ago

Snippet 1 kinda of solves the problem although it is very slow as you pointed out. For snippet 2 I have to enter "A1:B4" as input, but if I copy the formula to another cell it doesn' t update it as it is a string. So, instead of entering =retValues("A1:B4"), how do I enter =retValues(A1:B4), without quotes?

TheMaster Over a year ago

@Adriano Your retAddress should work fine. What's wrong with =retAddress(A1:B4) or just =arrayformula(a1:b4)? I added the quotes, because you wanted to provide the result of =ADDRESS() ,a string to script. If you still have issues, Kindly ask a new question with enough details.

Adriano_Pinaffo Over a year ago

My point is that passing as input A1:B4 will actually pass the values of those cells in a 2 dimensional array, but not the address itself, that's why I've been using =address(). But you answer worked fine with =ArrayFormula() and =address(), so thank you.

Collectives™ on Stack Overflow

How to use a custom function with an ArrayFormula

1 Answer 1

Issue:

Solution:

References:

Snippet#1:

Snippet#2:

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Issue:

Solution:

References:

Snippet#1:

Snippet#2:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related