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

问题描述

我有Google时间表数据的电子表格;它每个月都有一张工作表，每个工作表有六个列块，每个客户端一个块。

我创建了一个汇总表并获取总计对于每个客户端，并将其显示在列表中：

 函数getClientTotals（sheetname，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），它传递月份的表名和每个客户端的列数（在模式修改的情况下）。

这一切都可以正常工作，但是，当源数据发生变化时，它不会更新，我添加了一个 onEdit 触发器; no喜欢它更新如果你去脚本编辑器并点击保存，但没有用，我错过了什么？ 解决方案

您错过了挑剔的缓存 bug 功能，它的工作原理如下：

Google认为您的所有自定义功能都只依赖 / strong>的参数值直接返回结果（您可以选择性地依赖其他静态数据）。

给定这个先决条件，他们只能在参数例如

让我们来看看我们在单元格B1上有文本10，然后在其他一些单元格上输入 = myFunction（B1）

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

，当你使用表名作为参数来动态获取并返回结果时，你打破了缓存规则。

不幸的是，你不能自定义功能没有这个惊人的功能。因此，您必须将其更改为直接接收值，而不是表名，或者不要使用自定义函数。例如，你可以在你的脚本上有一个参数，告诉它的总结应该在哪里，并且当总数发生变化时，有一个 onEdit 更新它们。

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

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.

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?

解决方案

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

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.

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

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.

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屋！