如何在ArrayFormula中使用自定义函数 [英] How to use a custom function with an 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?

推荐答案

问题：

Issue:

SpreadsheetApp.getActive().getRange(cell)

cell 是数组（如果提供数组）输入。 getRange 方法需要一个字符串作为输入。

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

• 映射数组为单个值

• map the array to single value

• 自定义函数#Optimization


• 最佳做法

• Custom Function#Optimization
• Best practices

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（）进行一次调用。
  
  
  • 公式返回一个数组，而没有明确使用 = ARRAYFORMULA（）。默认情况下，所有自定义公式都是数组公式，但是需要将它们配置为在应用脚本中以数组形式返回值。
  
  
  • 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.

    这篇关于如何在ArrayFormula中使用自定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！