如何将自定义函数与 ArrayFormula 一起使用

Question

我想写一个可以在ArrayFormula中使用的函数。我的桌子是这样的：

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

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

function retAddress(cell){
return cell;
}

在 B1 上，我写了=ArrayFormula(retAddress(address(row(B:B),column(A:A),4)))，显然它按预期工作，它返回每个地址，如下所示：

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

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

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

在 C1 上我写了=ArrayFormula(retValue(address(row(B:B),column(A:A),4)))但它给了我错误Exception: Range not found (line 2).，这是getRange(cell)方法的行。

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

在 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中工作呢？

Answer 1

问题：

SpreadsheetApp.getActive().getRange(cell)

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

溶液：

• 将数组map为单个值

引用：

• 自定义功能#优化
• 最佳实践

片段#1：

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

片段#2：

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

=retValues("A1:B4")

function retValues(cell){//modified
var cellRang = SpreadsheetApp.getActive().getRange(cell);
return cellRang.getValues();//modified
}

• 请注意：
  • 仅拨打 1 次getValues()。
  • 公式返回一个数组，而不显式使用=ARRAYFORMULA()。默认情况下，所有自定义公式都是数组公式，但需要将它们配置为在应用脚本中以数组的形式返回值。