google-apps-script - ArrayFormula 破坏了 getLastRow() 功能。可能的解决方法？

Question

在我的电子表格中，我有一个运行脚本，它使用 getLastRow()功能作为其逻辑的重要组成部分。

自从我在我的一列中应用数组公式以来，getLastRow()功能不能正常工作。即使其他列中没有其他值，数组公式似乎一直“应用”到工作表的底部，因此，getLastRow()返回有数组公式的最后一行，而不是实际的非空行。

编写一个检查哪些单元格为空的慢速函数对我来说不是一个选择，因为脚本在运行这样的事情时会用完(它有数万行)。

有没有人对解决方法有任何建议？

这是ARRAYFORMULA :

=ArrayFormula(IF(A2:A="",,WEEKNUM(A2:A, 2)))

Answer 1

问题:

传统用法 ARRAYFORMULA(IF(A:A="",...)) 在所有可用行中添加了不希望的空字符串

解决方案:

使用 ARRAYFORMULA正确使用 INDEX/COUNTA (确定需要的最后一行)确保公式只填充到需要的行而不是伪装

INDEX/COUNTA :INDEX返回一个值以及一个单元格引用。 A2:INDEX(A2:A,COUNTA(A2:A)) => 如果 COUNTA(...)返回 10 => A2:INDEX(A2:A,10) => A2:A11是提供给weeknum的最终引用

假设您的数据之间没有空格，

=ARRAYFORMULA(WEEKNUM(A2:INDEX(A2:A,COUNTA(A2:A)),2))

另一种选择是使用 ARRAY_CONSTRAIN/COUNTA :

=ARRAY_CONSTRAIN(ARRAYFORMULA(WEEKNUM(A2:A, 2)),COUNTA(A2:A))

COUNTA的用法假设中间没有空白单元格。如果有，您可能需要手动添加偏移量。如果有两个空白单元格，将 2 添加到 COUNTA

A2:INDEX(A2:A,COUNTA(A2:A)+2)

除非 Google 进行内置优化， INDEX/COUNTA优于 ARRAY_CONSTRAIN