汇总未更新数据的脚本 [英] Script to summarise data not updating

问题描述

我有一份包含时间表数据的 Google 电子表格；它有每个月的工作表，每张工作表有六个列块，每个客户一个块.

I have a Google spreadsheet of timesheet data; it has a sheet for each month, each sheet is a lot of six column blocks, one block per client.

我创建了一个汇总表，可以获取每个客户的总数并将其显示在列表中:

I have created a summary sheet that goes and gets the total for each clients and displays it in a list:

function getClientTotals(sheetname, colcount)
{  
  colcount = colcount ? colcount : 6;
  var res;      
  var ss = SpreadsheetApp.openById('myid_goes_here');
  if(ss)
  {
    res = [];
    var totrow = ss.getRange(sheetname + '!A1:ZZ1').getValues()[0];
    for(var i = 0; i < totrow.length; i += colcount)
    {
      res.push([totrow[i], totrow[i + colcount - 1]]);
    }
  }   
  return res;
}

然后我刚刚在我的汇总表中添加了一个包含 =getClientTotals($C$7,$C$8) 的单元格，它传入了月份的工作表名称和每个客户的列数(在架构"修改的情况下.

I have then just added a cell to my summary sheet containing =getClientTotals($C$7,$C$8) which passes in the sheet name for the month and the number of columns for each client (in case of "schema" modifications.

这一切正常，但是，它不会在源数据更改时更新.我添加了一个 onEdit 触发器；没有快乐.如果您转到脚本编辑器并点击保存，它会更新，但这没有用.我错过了什么吗?

This all works fine, however, it does not update when the source data is changed. I have added an onEdit trigger; no joy. It updates if you go to the script editor and hit Save, but that's not useful. Am I missing something?

推荐答案

您缺少了挑剔的缓存 bug 功能.它是这样工作的:

You're missing the fastidious caching bug feature. It works this way:

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

Google considers that all your custom functions depend only on their parameters values directly to return their result (you can optionally depend on other static data).

鉴于此先决条件，他们只能在参数更改时评估您的功能.例如

Given this prerequisite they can evaluate your functions only when a parameter changes. e.g.

假设我们在单元格 B1 上有文本10"，然后在其他一些单元格上输入 =myFunction(B1)

Let's suppose we have the text "10" on cell B1, then on some other cell we type =myFunction(B1)

myFunction 将被评估并检索其结果.然后，如果您将单元格 B1 的值更改为35"，则自定义将按预期重新评估，并正常检索新结果.现在，如果您再次将单元格 B1 更改为原始的10"，则无需重新评估，会立即从缓存中检索原始结果.

myFunction will be evaluated and its result retrieved. Then if you change cell B1 value to "35", custom will be re-evaluated as expected and the new result retrieved normally. Now, if you change cell B1 again to the original "10", there's no re-evaluation, the original result is retrieved immediately from cache.

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

So, when you use the sheet name as a parameter to fetch it dynamically and return the result, you're breaking the caching rule.

不幸的是，如果没有这个惊人的功能，您就无法拥有自定义功能.因此，您必须更改它以直接接收值而不是工作表名称，或者不使用自定义函数.例如，您可以在脚本中设置一个参数，告知摘要应该放在何处，并在总数发生变化时让 onEdit 更新它们.

Unfortunately, you can't have custom functions without this amazing feature. So you'll have to either change it to receive the values directly, instead of the sheet name, or do not use a custom function. For example, you could have a parameter on your script telling where the summaries should go and have an onEdit update them whenever a total changes.

这篇关于汇总未更新数据的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！