Script to summarize data not updating

Question

I have a Google spreadsheet spreadsheet. he has a sheet for each month, each sheet contains six blocks of columns, one block for each client.

I created a pivot sheet that goes and gets the total for each customer 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 just added a cell to my pivot sheet containing =getClientTotals($C$7,$C$8) , which passes the sheet name for the month and the number of columns for each client (in case of modifications to the scheme).

Everything works fine, but when you change the source data, it does not update. I added an onEdit trigger; no joy. It is updated if you go to the script editor and click "Save", but this is not useful. Did I miss something?

Answer 1

You are missing the fast cache feature . It works as follows:

Google believes that all your custom functions depend only on the parameter values ​​directly, in order to return their result (you can optionally depend on other static data).

Given this assumption, they can evaluate your functions only when changing a parameter. eg.

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 will be restored. Then, if you change the value of cell B1 to "35", the user will be reevaluated as expected, and the new result will be restored in normal mode. Now, if you again change cell B1 to the original "10", there will be no re-evaluation, the original result will be obtained immediately from the cache.

So, when you use the sheet name as a parameter to dynamically retrieve it and return the result, you break the caching rule.

Unfortunately, you don’t have custom features without this awesome feature. Thus, you will either have to change it to get the values ​​directly, instead of the sheet name, or not use the user-defined function. For example, you might have a script parameter indicating where to summarize the summary, and update their onEdit with every change.

Answer 2

another solution to the cache problem.

have a dummy variable in your method. pass the

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

as the value of this parameter.

eg.

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

filter output is not used. however, he indicates to the spreadsheet that this formula is sensitive to the B4: Z10 range.

Answer 3

I had a similar problem creating a panel for work. The Chamil solution above (namely, using a sheet filter function passed as the value of a dummy variable in your function) works fine, despite a more recent comment from Arsen. In my case, I used a function to control the range and could not use the filter in the same range, since it created a circular link. So I just had a cell (in my case E45 in the code below), in which I changed the number anytime I want my function to be updated:

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

As Shamil pointed out, the filter is not used in the script:

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

Answer 4

I use a dummy variable in a function, this variable refers to a cell in the spreadsheet, then I have a Myfunction () script that writes the Math.Ramdon number in this cell. This "MyFunction" is in the trigger service (edit / current project triggers), and you can select different event triggers, for example, when opened or by time, there you can choose, for example, a period of time, from 1 minute to a month

Answer 5

What you can do is set up another cell somewhere in the spreadsheet, which will be updated every time a new sheet is added. Make sure that it is not updated for each change, but only when you want to perform a calculation (in your case, when you add a sheet). Then you pass the link to this cell to your custom function. As already mentioned, a user-defined function may ignore this parameter.

Answer 6

Given this feature described by Enrique Abreu, you can try the QUERY spreadsheet function , which SQL query is what I often use in the work on the source data and receive data in the form of a summary on another tab, the result data is updated in real time after changes in the source data.

My suggestion is based on the fact that your script does not have advanced work, such as fetching URLs, just working with data, since without reading real data I can not give an exact solution with QUERY.

Regarding the caching feature mentioned by Henrique Abreu (I don't have enough reputation for comments directly under his answer), I tested and found that:

• the cache does not work, the script test function is shown below:

  adder (base) {Utilities.sleep (5000); return basis + 10; }

applying this special functions adder () in a worksheet, calling a cell, and then changing the value of this cell back and forth every time I see a loading message and the total time is more than 5 seconds. This may be due to the update mentioned in this GAS Problem:

This problem has now been fixed. User-defined functions in New Sheets are now context-sensitive and do not cache values ​​as aggressive.

2. the problem mentioned in this section remains, my testing shows that the google sheet recounts the user function every time ONLY WHEN

   • the DIRECTLY value called by the function changes.

   getCellValue function (sheet name, row, column) {var ss = SpreadsheetApp.getActiveSpreadsheet (); var sh = ss.getSheetByName (sheetName); return sh.getRange (string, col) .getValue (); }

   
   Changing any value in the yellow cells will recalculate the user-defined function; the actual change in the value of the data source is ignored by the function.

   • Function
   • containing the cell changes on the sheet. ex. insert / delete row / column top or left.

Answer 7

I did not want to have a dummy parameter. YMMV on this one.

1 Cell, which is a "List of elements", one of which is "Refresh"

2 Script with "onEdit" if the cell is "Update":

a) Clear document cache

b) Fill the document cache with external data (table in my case)

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

• get the formula

• set '= 0'

• install ofula

d) Set the cell to (1) with the value "Finish"

This updates the bits you want, BUT NOT FAST.

Answer 8

Since the Google application script is a JS extension, functions must be able to process more arguments than defined in the function signature, or less. So if you have any function like

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

then you would call this function as

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

where $ A $ 2 is some kind of checkbox (insert → checkbox) that you can click to “refresh” after you need to change the value from the sheet and cell. CONSTANTLY $ A $ 1

Answer 9

As @Brionius said, add an extra dynamic argument to the function. if you use now (), you may have problems with the timeout, make the update a little slower ...

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