时间戳

首页 > 解决方案 > 在谷歌表格中，我可以将标准函数包装在自定义函数中以控制它何时运行？

google-apps-script - 在谷歌表格中，我可以将标准函数包装在自定义函数中以控制它何时运行？

问题描述

我最初在 WebApps 上问过这个问题，以为我只是错过了一个谷歌技巧。在那里我收到了需要自定义函数的答案。

https://webapps.stackexchange.com/questions/129068/recalculate-google-sheet-on-demand

这是我原来的问题：

我在工作表中有以下公式：

=if(E1="HOLD",,query(Cust_Orders!B6:Z5000,"Select Y,G,I,H,K where H>0  "))

E1 是具有值 HOLD 和 FETCH 的下拉列表

我去 FETCH 时的重新计算大约需要 13 秒。

但是当它处于 HOLD 状态时，查询不会执行，一切都变为空白。不是我想要的。

我试图避免每次更改 Cust_Orders 范围时重新计算此查询，但保留旧值，因为其他人查看的两个不同的数据透视表依赖于它。

谷歌电子表格重新计算设置都是'on change and foo '

有没有办法做到这一点？

自定义函数仅在其参数之一更改时更新。所以如果函数只依赖HOLD/FETCH单元格，但执行公式是查询单元格，我想我赢了。

我的研究：

这个答案google sheet custom function built-in function专门告诉 OP 如何用脚本语言做他想做的事。我怀疑在 appscript 中重写查询不会是净赢。

此链接使用绑定的 Google 脚本生成查询对象

被提议作为一种解决方案，但这实际上是在脚本中重写我的内置函数。我想尽可能少地使用脚本，并且尽可能通用，因为它使长期维护和修改更容易。

上面的查询函数就是一个例子。我正在寻找一个更通用的解决方案，允许我使用相同的脚本使用任意公式。

谷歌产品论坛上的讨论： https: //support.google.com/docs/forum/AAAABuH1jm01F-8MzzCxbY/?hl= en&gpf=%23!topic %2Fdocs%2F1F-8MzzCxbY 说你不能从脚本。但这个问题已经将近4岁了。

我也在那里问过这个问题，但通常在谷歌产品论坛上问是万岁。

一个可行的解决方案：

我不想要一个在脚本中模拟所需内置的脚本。

标签： google-apps-scriptgoogle-sheets

解决方案

  • 一种比自定义公式更通用的方法来控制重新计算。

我为客户做的项目是将“昂贵”的公式保存为脚本上的变量，并有按钮来冻结/解冻某些范围（那些对重新计算时间有很大影响的范围。

  • “解冻”按钮将公式添加到电子表格
  • “冻结”按钮将公式结果放在公式使用的范围内
  • 有一个存储冻结/解冻电子表格状态的文档属性
  • 侧边栏用于显示电子表格状态的按钮。
  • 调用内置函数的脚本示例。

脚本可以获取值、显示值、公式和其他内容，但它们不能调用内置函数。

就我的客户而言，他们有一个按表格排列的数组公式（IMPORTRANGE、QUERY、FILTER、ARRAYFORMULA 等），包含的所有公式都在 A1 上。公式保存在以下形式的对象数组中

var formulas = [
  {
    name:'Sheet1',
    address:'A1',
    formula:'=IMPORTRANGE(...)'
  }
]

包含地址属性以供将来改进。

“冻结”功能的关键代码行是

var dataRange = sheet.getDataRange();
dataRange.copyTo(dataRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

请注意，以上行将整个数据范围复制粘贴为值。

“解冻”功能的关键代码行是

formulas.forEach(function(item){
  var sheet = spreadsheet.getSheetByName(item.name);
  sheet.clear();
  sheet.getRange(item.address).setFormula(item.formula);
});

请注意，以上行清除了整张纸。

推荐阅读