如何在电子表格完成计算之前暂停应用脚本 [英] How to pause App Scripts until spreadsheet finishes calculation

问题描述

因为 google 电子表格不支持迭代，我编写了自己的简单应用程序脚本来根据电子表格输出的计算调整输入.但是，在更改输入变量后，电子表格会重新计算，但应用程序脚本似乎不会等待重新计算，因此我最终会检索诸如Thinking..."或#NA"之类的值.有没有办法暂停脚本并等待计算完成，然后再移动到脚本的下一行?

Because google spreadsheets does not support iterations, I wrote my own simple app script to adjust an input based upon the calculation of the spreadsheet output. However, after I change the input variable, the spreadsheet recalculates but app scripts does not seem to wait for that recalculation so I end up retrieving values such as "Thinking..." or "#NA". Is there a way to pause a script and wait for the calculation to complete before moving to the next line in the script?

目前，我只是使用循环来观察单元格，但我想知道是否有更优雅的方式来暂停执行，直到工作表完成计算.

Currently, I am just using a loop to watch the cell but I wanted to find out if there was a more elegant way to pause the execution until the sheet was done calculating.

我写了很多 Excel 宏，Excel VBA 总是在移动到代码的下一行之前等待计算完成.Apps Script 似乎没有这样做，所以我希望有一种简单的方法来做到这一点.

I write a lot of Excel Macros and Excel VBA always waits for the calculation to complete before moving to the next line in the code. Apps Script does not seem to do this so I am hoping there is an easy way to do this.

第二个问题:由于此迭代可能需要一些时间，因此如何中断和终止脚本的运行?我似乎找不到办法做到这一点.

A second question: Because this iteration can take some time, how does one interrupt and terminate a script from running? I can't seem to find a way to do this.

推荐答案

这里有一个非常简单的方法来阻止下一个脚本启动，直到当前脚本在 google apps 脚本中完成.只需在您连续处理的每个脚本之后添加对 testWait() 的调用.SpreadsheetApp.flush() 似乎还将电子表格上的超时计时器重置为默认的 5 分钟，因此您有更多时间一次性处理多个脚本.

Here is a very simple way of preventing the next script from starting until the current script completes in google apps scripts. Just add a call for testWait() after each script you are processing successively. The SpreadsheetApp.flush() also seems to reset the timeout timer on the spreadsheet back to the default 5min so you have more time to process multiple scripts in one go.

//holds processing of next script till last one has completed
function testWait(){
  var lock = LockService.getScriptLock(); lock.waitLock(300000); 
  SpreadsheetApp.flush(); lock.releaseLock();
}

这篇关于如何在电子表格完成计算之前暂停应用脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！