如何强制新的Google Spreadsheets刷新并重新计算? [英] 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.

预先感谢， 身份证明

推荐答案

文件->电子表格设置->(制表符)计算->重新计算(3个选项)
-更改时
-随时随地
-随时随地变化
这会影响NOW，TODAY，RAND和RANDBETWEEN的更新频率.

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.
  

在我的示例中
我使用谷歌电子表格来找出一个人的年龄.我的生日日期格式为(dd.mm.yyyy)->这是瑞士在这里使用的格式.

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)), "")))

我正在使用TODAY()，并且进行了上述重新计算设置. ->但不会自动刷新. :-(
仅当我在函数要查找的范围内更改某些值时，它才会更新.

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.

为此，我为此编写了一个Google脚本(工具->脚本编辑器..).

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());
}

您需要为firstRow，firstColumn，lastRow，lastColumn

打开电子表格时，脚本将激活，再次将一个单元格的内容写入同一单元格.足以触发TODAY()函数.

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.

从Edward Moffett查找有关该链接的更多信息. 强制Google工作表公式进行重新计算

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

最好的问候，
克里斯多夫(Christoph)

Best regards,
Christoph

这篇关于如何强制新的Google Spreadsheets刷新并重新计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！