刷新通过 Google 工作表中的自定义函数检索的数据

小开

你错过了精心设计的臭虫缓存特性，它是这样工作的:

Google 认为所有自定义函数都直接依赖于它们的参数值来返回结果(您可以选择依赖于其他静态数据)。

有了这个先决条件，它们只有在参数改变时才能计算你的函数。

假设我们在 B1单元格中输入文本“10”，然后在其他单元格中输入 =myFunction(B1)

将计算 myFunction 并检索其结果。然后，如果您将单元格 B1值更改为“35”，自定义将按预期重新计算，并正常检索新结果。现在，如果您再次将单元格 B1更改为原来的“10”，则不会重新计算，原来的结果将立即从缓存中检索。

因此，当您使用工作表名称作为参数来动态获取它并返回结果时，就违反了缓存规则。

不幸的是，如果没有这个神奇的特性，您就不能拥有自定义函数。因此，您必须要么更改它以直接接收值，而不是工作表名称，要么不使用自定义函数。例如，您可以在脚本中设置一个参数，告诉摘要应该放在哪里，并让 onEdit在总的变化发生时更新它们。

小开

如果您编写了自定义函数并在电子表格中将其用作公式，那么每次打开电子表格或修改任何引用单元格时，都会重新计算公式。

如果您只想一直盯着电子表格并希望它的值发生变化，那么可以考虑添加一个定时触发器来更新单元格。阅读更多关于触发器给你的信息

小开

最佳答案

好吧，看起来我的问题是，谷歌的行为在一个奇怪的方式-它不重新运行脚本，只要脚本参数是相似的，它使用缓存的结果从以前的运行。因此，它不会重新连接到 API，也不会重新获取价格，它只是返回之前缓存的脚本结果。

点击这里查看更多信息(如果你受到影响，在这些问题上添加一个星号) :

and Henrique G. Abreu's answer

我的解决方案是在脚本中添加另一个参数，我甚至都没有使用这个参数。现在，当您使用与以前的调用不同的参数调用函数时，它将不得不重新运行脚本，因为这些参数的结果将不在缓存中。

因此，每当我调用函数时，对于额外的参数，我传递“ $A $1”。我还创建了一个名为 refresh 的菜单项，当我运行它时，它会将当前日期和时间放在 A1中，因此所有以 $A1作为第二个参数的脚本调用都必须重新计算。下面是我的剧本中的一些代码:

function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Refresh",
functionName : "refreshLastUpdate"
}];
sheet.addMenu("Refresh", entries);
};


function refreshLastUpdate() {
SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());
}


function getPrice(itemId, datetime) {
var headers =
{
"method" : "get",
"contentType" : "application/json",
headers : {'Cache-Control' : 'max-age=0'}
};


var jsonResponse = UrlFetchApp.fetch("http://someURL?item_id=" + itemId, headers);
var jsonObj = eval( '(' + jsonResponse + ')' );
return jsonObj.Price;
SpreadsheetApp.flush();
}

当我想将 ID 为5的商品的价格放在一个单元格中时，我使用以下公式:

=getPrice(5, $A$1)

当我想刷新价格时，我只需点击“刷新”-> “刷新”菜单项。请记住，您需要在更改 onOpen()脚本之后重新加载电子表格。

小开

缓存问题的另一种解决方案。

在你的方法中有一个虚拟变量。通过

Filter(<the cell or cell range>,1=1)

作为该参数的值。

例如:。

=getValueScript("B1","B4:Z10", filter(B4:Z10,1=1))

不使用滤波器输出。但是它向电子表格表明，这个公式是敏感的 B4: Z10范围。

小开

如@Brionius 所说，在函数上增加一个额外的动态参数。如果您使用 now () ，您可能会遇到超时问题，使得更新速度稍微慢一点..。

cell A1 = int(now()*1000)
cell A2 = function(args..., A1)

小开

我所做的类似于 tbkn23。这个方法不需要任何用户操作，只需要进行更改。

我要重新计算的函数有一个额外的未使用的参数 $A $1

=myFunction(firstParam, $A$1)

但是在代码中，函数签名是

function myFunction(firstParam)

我没有使用 Refresh 函数，而是使用了 onEdit (e)函数，如下所示

function onEdit(e)
{
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}

每当编辑电子表格中的任何单元格时，都会触发此函数。现在编辑一个单元格，在 A1中放置一个随机数，这会像 tbkn23建议的那样刷新参数列表，导致重新计算定制函数。

小开

您可以做的是在电子表格中的某处设置另一个单元格，每次添加新的表格时都会更新该单元格。确保它不会针对每次更改进行更新，而只是在您想要进行计算时(在您的情况下，当您添加一个工作表时)进行更新。然后将对此单元格的引用传递给自定义函数。如前所述，自定义函数可以忽略此参数。

小开

我在为工作创建仪表板时也遇到过类似的问题。Chamil 的上述解决方案(即使用作为值传递给函数中的虚拟变量的 SheetFilter 函数)工作得很好，尽管 Arsen 最近发表了评论。在我的例子中，我使用一个函数来监视一个范围，但是不能在同一个范围上使用过滤器，因为它创建了一个循环引用。因此，我只有一个单元格(在我的例子中是下面代码中的 E45) ，在这个单元格中，我可以随时更改函数的编号:

=myFunction("E3:E43","D44",filter(E45,1=1))

正如 Chamil 指出的，脚本中没有使用过滤器:

function myFunction(range, colorRef, dummy) {
variable 'dummy' not used in code here
}

小开

Working off of Lexi's script as-is, it didn't seem to work anymore with the current Sheets, but if I add the dummy variable into my function as a parameter (no need to actually use it inside the function), it will indeed force google sheets to refresh the page again.

所以，像这样声明: function myFunction (firstParam，false) ，然后调用它，就像我建议的那样。

此外，如果一个随机变量出现在你所有编辑的工作表上是一件麻烦的事情，一个简单的补救办法就是限制在一个工作表上，如下所示:

function onEdit(e)
{
e.source.getSheetByName('THESHEETNAME').getRange('J1').setValue(Math.random());
}

小开

有一些设置可以让你自动更新 NOW():

小开

If your custom function is inside a specific column, simply order your spreadsheet by that column.

排序操作强制刷新数据，这将一次为该列的所有行调用自定义函数。

小开

考虑到 Henrique Abreu 解释的这个特性，您可以尝试使用 开箱即用的电子表格功能查询，我经常在工作中使用这个 SQL 查询在原始数据，并得到数据作为摘要到一个不同的选项卡，结果数据是实时更新后，原始数据的变化。

我的建议是基于这样一个事实，即您的脚本没有进一步的工作，如 URL 获取，只是数据工作，因为没有实际的数据读取，我不能给出一个精确的解决方案与查询。

关于亨利克 · 阿布鲁提到的 缓存功能(我没有足够的声誉在他的回答下直接评论) ，我做了测试，发现:

looks there is no cache working, testing function's script shown below:

函数加法器(基){ 睡眠(5000) ; 返回基数 + 10; }

通过调用一个单元格应用表中的自定义函数 Adder () ，然后来回更改该单元格值，每次我看到加载消息和总时间超过5秒。这可能与 GAS 问题中提到的更新有关:

这个问题现在已经解决了。新建表中的自定义函数现在可以感知上下文，并且不会像以前那样主动地缓存值。

在这个主题提到的问题仍然存在，我的测试表明，谷歌表重新计算自定义功能每次只有当
- 由函数直接调用的值被更改。
函数 getCellValue (sheetName，row，col.) { Var ss = SpreadsheetApp.getActiveSpreadsheet () ; Var sh = ss.getSheetByName (sheetName) ; 返回 sh.getRange (row，col.getValue () ; }

黄色单元格中任何值的更改都将导致重新计算自定义函数; 实际数据源值的更改将被函数忽略。
- 包含单元格位置的函数会在工作表中更改。例如，在左侧或上方插入/删除行/列。

小开

我不想有一个虚拟的参数。 YMMV 对此。

1一个单元格是“项目列表”，一个是“刷新”

如果单元格为“刷新”，则脚本使用“ onEdit”:

A)清空文档缓存

B)用外部数据填充 doc 缓存(在我的例子中是一个表)

c)For all cells with my 'getStockoData(...' custom function

拿到配方
Set’= 0’
设定配方

D)将(1)中的单元格设置为“ Ready”

这确实刷新了你想要的位，但是并不快。

小开

我在函数中使用了一个虚拟变量，这个变量指的是电子表格中的一个单元格。然后在脚本中有一个 Myfunction()，它在该单元格中写入一个 Math.Random数字。

MyFunction是一个触发服务(编辑/当前项目触发器) ，你可以选择不同的事件触发器，例如打开或时间驱动，在那里你可以选择一个时间段，从1分钟到一个月。

小开

由于 google app 脚本是 JS 的扩展，函数应该能够处理比函数签名中定义的更多或更少的参数。如果你有一些函数，比如

function ADD(a, b) {
return CONSTANTS!$A$1 + a + b
}

然后你会把这个叫做

=ADD(A1, B1, $A$2)

其中 $A $2是一些复选框(插入-> 复选框) ，您可以点击“刷新”后，您需要更改的价值从表格和单元格 CONSTANTS $A $1

小开

使用谷歌财务函数作为参数

那些功能力量每隔 x 分钟重新加载一次

小开

脚本逻辑:

自定义函数不会更新，除非它的参数发生变化。
创建一个 onChange触发器，使用 TextFinder更改电子表格中所有自定义函数的所有参数
通过@tbkn23和@Lexi Brush 添加额外虚拟参数的想法在这里实现，参数是一个随机数。这个答案主要是因为使用了类 TextFinder(Apps 脚本的一个相对较新的补充)而有所不同，这是因为
- 不需要额外的手机。
- 不需要菜单 > 不需要额外的点击。如果您需要一个自定义刷新器，那么复选框是一个更好的实现
- 您还可以更改 < a href = “ https://stackoverflow./a/61946592/”> 公式本身，而不是更改参数
- 可以将更改/触发器配置为仅过滤掉某些更改。例如，下面的示例脚本触发器过滤掉除 INSERT_GRID/REMOVE_GRID(Grid = Sheet)之外的所有更改。这适用于提供 sheetnames的自定义函数。任何地方的编辑都不会改变工作表/工作表名称的列表，但是插入或删除工作表会。

样例自定义函数(刷新) :

/**
* @customfunction
* @OnlyCurrentDoc
* @returns Current list of sheet names
*/
const sheetNames = () =>
SpreadsheetApp.getActive()
.getSheets()
.map((sheet) => sheet.getName());

提神功能:

/**
* @description Automatically refreshes specified custom functions
* @author TheMaster https://stackoverflow.com/users/8404453
* @version 2.0.0
* @changelog
*    Updated to support all custom functions and arguments
*    Avoid eternal loops
*/


/**
* @listens to changes in a Google sheet
* @see https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually
*/
function onChange(e) {
/* Name of the custom function that is to be refreshed */
const customfunctionName = 'SHEETNAMES',
regexPattern = `=${customfunctionName}${String.raw`\(([^)]*?)?(?:,\s*?"RANDOM_ID_\d+")?\)`}`,
replacementRegex = `=${customfunctionName}${String.raw`($1,"RANDOM_ID_${
Math.floor(Math.random() * 500) + 1
}")`}`;


/* Avoid eternal loop
* Increase timeout  if it still loops
*/
const cache = CacheService.getScriptCache(),
key = 'onChangeLastRun',
timeout = 5 * 1000 /*5s*/,
timediff = new Date() - new Date(JSON.parse(cache.get(key)));
if (timediff <= timeout /*5s*/) return;
cache.put(key, JSON.stringify(new Date()));


/* Following types of change are available:
* EDIT
* INSERT_ROW
* INSERT_COLUMN
* REMOVE_ROW
* REMOVE_COLUMN
* INSERT_GRID
* REMOVE_GRID
* FORMAT
* OTHER - This usually refers to changes made by the script itself or sheets api
*/
if (!/GRID|OTHER/.test(e.changeType)) return; //Listen only to grid/OTHER  change
SpreadsheetApp.getActive()
.createTextFinder(regexPattern)
.matchFormulaText(true)
.matchCase(false)
.useRegularExpression(true)
.replaceAllWith(replacementRegex);
}

To Read:

小开

As noted earlier:

自定义函数不会更新，除非它的参数发生变化。

可能的解决方案是在单个单元格中创建一个复选框，并使用该单元格作为自定义函数的参数:

Create a checkbox: select free cell e.g. [A1], go to [Insert] > [Checkbox]
使这个单元格成为一个参数: =myFunction(A1)
单击复选框刷新公式

小开

我从 1分44秒开始跟踪这个视频，这对我很有用。

您应该使用一个特定的更新函数，初始化一个“当前时间”变量，并将这个永久更新的变量传递给您的自定义函数。然后转到 Triggers，为更新函数设置一个“每分钟”时间驱动的触发器(或者为更新选择另一个时间间隔)。

密码:

function update() {
var dt = new Date();
var ts = dt.toLocaleTimeString();
var cellVal = '=CustomFunction("'+ ts + '")';
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(cellVal);
}

小开

今天我解决了这个问题

为函数添加另一个参数:

function MY_FUNC(a, b, additional_param) { /* ... */ }

也向这个参数添加一个值，引用一个单元格:

=MY_FUNC("a", "b", A1)

并在引用的单元格(A1)中放置一个复选框。

现在，只需单击(在复选框上)一下即可强制重新计算函数。