Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Force New Google Spreadsheets to refresh and recalculate?

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

like image 401
iddo Avatar asked Apr 20 '14 01:04

iddo


People also ask

How do I trigger a recalculate in Google Sheets?

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).

Why is my Google Sheets not updating?

Make sure that your permissions on the google sheet are set to exactly "anyone with the link can view". If the permission is not exactly this, the sync process will be unable to open the sheet in order to sync, which may result in getting stuck. To solve, set the permission to "anyone with the link can view".


2 Answers

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 birth 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 gets 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.

like image 115
user3536211 Avatar answered Oct 01 '22 20:10

user3536211


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.

like image 39
user2715182 Avatar answered Oct 01 '22 19:10

user2715182