How to Force New Google Spreadsheets to refresh an

Question

There were some codes written for this purpose but with the new add-ons they are no longer applicable.

Thanks in advance, Iddo

Answer 1

Quick, but manual updating

NOW(), TODAY(), RAND(), or RANDBETWEEN()

Press Backspace ← or Del on any empty cell, to immediately trigger a recalculation of formulas depending on NOW(), TODAY(), RAND(), or RANDBETWEEN() (in all Sheets of the whole Spreadsheet, as usual).

(If no empty cell is at hand, you can delete a filled cell instead and then undo that with Ctrl+z.)

INDIRECT()’s

INDIRECT()’s are unfortunately not updated like this by default.

You can update a range of cell(s) of such formulas by pasting the range on itself:

1. select range
2. Ctrl+c
3. Ctrl+v

Answer 2

When the problem is in the recalculation of an IF condition, I add AND(ISDATE(NOW());condition) so that the cell is forced to recalculate according to what is set in the Calculation tab in Spreadsheet Settings as explained before.

This works because NOW is one of the functions that is affected by the Calculation setting and ISDATE(NOW()) always returns TRUE.

For example, in one of my sheets I had the following condition which I use to check whether a sheet with name stored in C1 is already created:

=IF(ISREF(INDIRECT(C$1&"!A1")); TRUE; FALSE)

In this case C1="February", so I expected the condition to become TRUE when a sheet with this name was created, which didn't happen. To force it to update, I changed the Calculation setting and used:

=IF(AND( ISDATE(NOW()) ; ISREF(INDIRECT(C$1&"!A1")) ); TRUE; FALSE)

Answer 3

What worked for me is inserting a column before the first column and deleting it immediately. Basically, do a change that will affect all the cells in the worksheet that will trigger recalculation.

Answer 4

Old question ... nonetheless, just add a checkbox somewhere in the sheet. Checking or unchecking it will refresh the cell formulae.

Answer 5

File -> Spreadsheet Settings -> (Tab) Calculation -> Recalculation (3 Options)
- On change
- On change and every minute
- On change and every hour
This affects how often NOW, TODAY, RAND, and RANDBETWEEN are updated.

but.. .. it updates only, if the functions arguments (their ranges, cells) are affected by that.
  

from my example
I use google spreadsheet to find out the age of a person. I have his birthday date in the format (dd.mm.yyyy) -> it's the used format here in Switzerland.

=ARRAYFORMULA(IF(ISTEXT(K4:K), IF(TODAY() - DATE(YEAR(TODAY()), MONTH(REGEXREPLACE(K4:K, "[.]", "/")), DAY(REGEXREPLACE(K4:K, "[.]", "/"))) > 0, YEAR(TODAY()) - YEAR(REGEXREPLACE(K4:K, "[.]", "/")) + 1, YEAR(TODAY()) - YEAR(REGEXREPLACE(K4:K, "[.]", "/"))), IF(LEN(K4:K) > 0, IF(TODAY() - DATE(YEAR(TODAY()), MONTH(K4:K), DAY(K4:K)) > 0, YEAR(TODAY()) - YEAR(K4:K) + 1, YEAR(TODAY()) - YEAR(K4:K)), "")))

I'm using TODAY() and I did the recalculation settings described above. -> but no automatically refresh. :-(
It updates only, if I change some value inside the ranges where the function is looking for.

So I wrote a Google Script (Tools -> Script Editor..) for that purpose.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetMaster = ss.getSheetByName("Master"); 
  var sortRange = sheetMaster.getRange(firstRow, firstColumn, lastRow, lastColumn);  

  sortRange.getCell(1, 2).setValue(sortRange.getCell(1, 2).getValue());
}

You need to set numbers for firstRow, firstColumn, lastRow, lastColumn

The Script get active when the spreadsheets open, writes the content of one cell into the same cell again. That's enough to trigger the TODAY() function.

Look for more information on that link from Edward Moffett. Force google sheet formula to recalculate

Best regards,
Christoph