我可以在Google表格中将标准函数包装在自定义函数中以控制其运行时间吗? [英] In google sheets can I wrap a standard function in a custom function to control when it is run?

问题描述

我最初是在WebApps上问这个的，以为我只是想念一个Google的把戏.在那里，我收到了一个答案，那就是它需要自定义功能.

https://webapps.stackexchange.com/questions/129068/recalculate-google -按需工作表

这是我最初的问题:

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

=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范围内进行更改，但将旧值保留为两个 他人查看的不同数据透视表是依赖的 在上面.

Google电子表格的重新计算设置全部为更改且 foo "

有没有办法做到这一点?

自定义函数仅在其参数之一更改时更新.因此，如果该函数仅取决于HOLD/FETCH单元格，但执行查询单元格的公式，我认为我赢了.

我的研究:

此答案 Google表格自定义函数内置函数是具体来说是告诉OP如何用脚本语言来完成他想做的事情.我怀疑用appscript重写查询不会获得胜利.

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

提出了

作为解决方案，但实际上是在脚本中重写了我的内置函数.我想尽可能少地使用脚本，并且尽可能通用地使用脚本，因为这样可以使长期维护和修改变得更加容易.

上面的查询功能是一个示例.我正在寻找一种更通用的解决方案，该解决方案允许我使用同一脚本使用任意公式.

此在Google产品论坛上的讨论: https://support.google.com/docs/forum/AAAABuH1jm01F-8MzzCxbY/?hl=zh-CN&gpf=%23!topic%2Fdocs%2F1F-8MzzCxbY 说，您可以不能从脚本中调用内置函数.但是问题已经有4年了.

我也在那儿问过这个问题，但通常在Google产品论坛上问的是冰雹玛丽".

可行的解决方案:

• 调用内置函数的脚本示例.
• 指向附件的链接，该附件允许重新计算范围的开/关
• 比自定义公式更通用的方法来控制重新计算.

我不想要一个脚本来模拟所需的内置脚本.

解决方案

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

我在为客户进行的项目中要做的是将昂贵的"公式另存为脚本中的变量，并具有冻结/取消冻结某些范围(对重新计算时间有很大影响的那些范围)的按钮. /p>

• 取消冻结"按钮会将公式添加到电子表格
• 冻结"按钮将公式结果置于公式使用的范围内
• 有一个文档属性，用于存储冻结/未冻结的电子表格状态
• 侧边栏用于显示电子表格状态按钮.

• 调用内置函数的脚本示例.

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

---

对于我的客户，他们按表有一个数组公式(IMPORTRANGE，QUERY，FILTER，ARRAYFORMULA等)，所有包含的公式都在A1上.公式将保存在以下形式的对象数组中

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

包括了address属性，用于将来的改进.

冻结"功能的关键代码行是

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

请注意，以上几行清除了整个工作表.

I originally asked this on WebApps, thinking that I was just missing a google trick. There I received the answer that it would require a custom function.

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

Here is my original question:

I have the following formula in a sheet:

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

With E1 being a drop down with values HOLD and FETCH

The recalculation when I go to FETCH takes about 13 seconds.

But when it is in HOLD, the query doesn't exceute, and everything goes blank. Not what I want.

I'm trying to avoid recalculating this query every time I make a change in the Cust_Orders range, but keep the old values, as two different pivot tables that are viewed by other people are dependent on it.

Google spreadsheet recalc settings all are 'on change and foo'

Is there a way to do this?

Custom functions only update when one of their parameters changes. So if the function only depends on the HOLD/FETCH cell, but executes the formula is the query cell, I think I win.

My research:

This answer google sheets custom function built-in function is specific in telling the OP how to do what he wants in the script language. I suspect that rewriting the query in appscript would not be a net win.

This link Using Bound Google Scripts to Generate a Query Object

was proposed as a solution, but this in effect is rewriting my built-in function within the script. I want to use scripts as rarely as possible, and as generically as possible, as it makes long term maintenance and modification easier.

The query function above is an example. I am looking for a more general solution that allows me to use an arbitrary formula using the same script.

This discussion on google product forums: https://support.google.com/docs/forum/AAAABuH1jm01F-8MzzCxbY/?hl=en&gpf=%23!topic%2Fdocs%2F1F-8MzzCxbY says you can't call built-ins from scripts. But the question is almost 4 years old.

I have asked this question there too, but generally asking on Google Product Forums is a Hail Mary.

A viable solution:

• An example of a script calling a built-in function.
• A link to an add-on that allows recalculation of a range to be toggled on/off
• A more general method than custom formulas to control recalc.

I do NOT want a script that emulates the desired built-in inside the script.

解决方案

• A more general method than custom formulas to control recalc.

What I'm doing on project for a client is to have the "expensive" formulas saved as variables on the script and have buttons to freeze/unfreeze the certain ranges (those that have a high impact on the recalculation time.

• The "unfreeze" button adds the formulas to the spreadsheet
• The "freeze" button put the formulas results over the range used by the formulas
• There is a document property that stores the frozen/unfrozen spreadsheet state
• A sidebar is used to show the buttons the spreadsheet status.

• An example of a script calling a built-in function.

Scripts can get values, display values, formulas, and other stuff but they can not call built-in functions.

---

In the case of my client, they have one array formula (IMPORTRANGE, QUERY, FILTER, ARRAYFORMULA, etc) by sheet, all the formulas that are been included are on A1. The formulas are saved in an array of objects of the following form

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

The address property, is included for future improvements.

The key code lines of the "freeze" function are

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

Please note that the above lines copy-paste-as-values the whole data range.

The key code lines of the "unfreze" function are

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

Please note that the above lines clear the whole sheet.

这篇关于我可以在Google表格中将标准函数包装在自定义函数中以控制其运行时间吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！