Google script (custom function) doesn't get updated inside a cell

Question

I have this function :

function clastrow(targetsheet) {
  var SS= SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = SS.getSheetByName(targetsheet);
  return(Sheet.getLastRow());
}

function clastcol(targetsheet) {
  var SS= SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = SS.getSheetByName(targetsheet);
  return(Sheet.getLastColumn());
}

It simply will return the last row/col of the target sheet. Then i use it for example in cell A1 :

=clastrow(B1)

where B1 contains the sheetname of target sheet to be calculated. It works but when i modify the target sheet , the output value of this custom function didn't get updated.

Finally, rather than using it as formula, i end up adding a button and assign the script to manually update the value.

How can i fix this if i want to keep using it as formula ? is it normal that the function won't update automatically ?

Answer 1

This behavior is a side effect of the memoization used by Google Sheets to cache the return values of custom functions. Its purpose is to improve the performance of custom functions. As long as the custom function's parameters remain the same, it will return the cached result without recalculating it.

The standard recipe to make the spreadsheet recalculate the cell value periodically is to add a dummy parameter that gets updated with new values every now and then. So try calling your custom function like this:

=clastrow(B1, D1)

Your custom function does not need to actually read the dummy parameter -- its presence is enough to avoid the memoization effect. The custom function will get called every time the value in cell D1 changes, which is not a perfect solution, but it is usually good enough for most needs.

To get the last row and last column in Sheet1, use these formulas in another sheet:

=arrayformula( max( if( len(Sheet1!A1:ZZZ), row(Sheet1!A1:ZZZ) ) ) )

=arrayformula( max( if( len(Sheet1!A1:ZZZ), column(Sheet1!A1:ZZZ) ) ) )

These formulas will not be affected by memoization and will update right away when Sheet1 is changed.

See Get the cell address of maximum column and row containing the most right and bottom value.