Script to summarise data not updating

Question

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?

Answer 1

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.