从脚本触发电子表格重新计算 [英] Trigger spreadsheet recalculation from script

问题描述

我有一个可以删除一个或多个命名范围的脚本。但是，在脚本结束后或在执行刷新命令后，工作表不会重新计算。我可以强制重新计算的唯一方法是在一个空单元格中插入一个新的公式或者更改现有的公式。

我试过只用 SpreadsheetApp.flush（）来触发，但如果表单没有可见的更改，则不会导致重新计算。

这是一个缺陷还是有一个更明智的做法？

解决方案

如果对电子表格采取的操作对任何公式，那么将不会重新计算。例如，删除一个命名范围没有重新计算的影响，除非在公式中引用了该命名范围。 （如果是这种情况，那么电子表格可能因删除而中断。）

Spreadsheet.flush（）不强制重新计算 - 它只应该写入待处理的电子表格更改。 （如果这种变化不影响公式......你就会明白）。

最终的结果是你只剩下你描述的解决方案;您需要更新参与计算的单元格。替换一个公式就可以了。对不起，没有魔术咒语。

I have a script that can delete one or more named ranges. However, the sheet does not recompute either after the script finishes or after a flush command is given. The only way I can force a recalculation is to physically insert a new formula in an empty cell or change an existing one.

I've tried using just SpreadsheetApp.flush() to trigger, but it doesn't cause a recalc if there is no visible change to the sheet.

Is this a deficiency or is there a more sensible approach?

解决方案

If the action taken upon the spreadsheet has no impact on any formulas, then there will be no recalculation. Deleting a named range, for example, has no recalculation impact unless that named range is referenced in a formula. (And if that's the case, then the spreadsheet was probably broken by the deletion.)

Spreadsheet.flush() does not force recalculation - it's only supposed to write pending changes to the spreadsheet. (And if that change doesn't affect formulas... you get the idea.)

The net result is that you're left with the solution you describe; you need to update a cell that's involved in calculations. Replacing a formula will do. Sorry, no magic incantation for this.

这篇关于从脚本触发电子表格重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！