Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
171 views
in Technique[技术] by (71.8m points)

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.

question from:https://stackoverflow.com/questions/23177356/how-to-force-new-google-spreadsheets-to-refresh-and-recalculate

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...