如何在 ArrayFormula 中使用自定义函数

本文介绍了如何在 ArrayFormula 中使用自定义函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想编写一个可以在 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 |   |   |

首先我写了一个简单的函数来返回输入(所以我知道它在 ArrayFormula 中工作):

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

function retAddress(cell){
  return cell;
}

在 B1 我写了 =ArrayFormula(retAddress(address(row(B:B),column(A:A),4))) 显然它按预期工作，它返回了每个地址，像这样:

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|   |

现在，在 C 列上，我想返回 A 列的值，所以我写了一个这样的函数:

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

在 C1 上我写了 =ArrayFormula(retValue(address(row(B:B),column(A:A),4))) 但它给了我错误 Exception:未找到范围(第 2 行).，这是带有 getRange(cell) 方法的行.

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.

如果我像这样编写没有 ArrayFormula 的函数:

If I write the function without ArrayFormula like this:

在 C1 上，=retValue(address(row(C1),column(A:A),4))

在 C2 上，=retValue(address(row(C2),column(A:A),4))

在 C3 上，=retValue(address(row(C3),column(A:A),4))

我得到了预期的结果:

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

那么，如何让它在 ArrayFormula 中工作?

So, how to make it work in ArrayFormula?

问题:

SpreadsheetApp.getActive().getRange(cell)

cell 如果您提供数组输入，则为数组.getRange 方法需要一个字符串作为输入.

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

map 数组到单个值

map the array to single value

自定义函数#Optimization
最佳做法

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

代码段#2:

请注意，在前面的代码段中，您对输入数组中的每个单元格调用 getValue() 1 次.这是非常缓慢的.更好的方法是将其称为批处理:

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
}

请注意:
- 仅对 getValues() 进行了 1 次调用.
- Formula 返回一个没有显式使用 =ARRAYFORMULA() 的数组.默认情况下，所有自定义公式都是数组公式，但需要将它们配置为在应用脚本中以数组形式返回值.

ArrayFormula