如何强制新的 Google 电子表格刷新和重新计算? [英] 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.

推荐答案

File ->电子表格设置 ->(Tab) 计算 ->重新计算(3 个选项)

File -> Spreadsheet Settings -> (Tab) Calculation -> Recalculation (3 Options)

• 变化中
• 每时每刻都在变化
• 每小时变化一次
  这会影响 NOW、TODAY、RAND 和 RANDBETWEEN 的更新频率.

但是…….. 只有当函数的参数(它们的范围、单元格)受到影响时它才会更新.

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

我正在使用 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 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.

查找有关该链接的更多信息 来自 Edward Moffett:强制谷歌表公式重新计算.

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

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