如何暂停应用程序脚本，直到电子表格完成计算 [英] How to pause App Scripts until spreadsheet finishes calculation

问题描述

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

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

我写了很多Excel宏和Excel在移动到代码中的下一行之前，VBA总是等待计算完成。应用程序脚本似乎不这样做，所以我希望有一个简单的方法来做到这一点。

第二个问题：因为这个迭代需要一些时间，所以如何中断并终止脚本运行？我似乎无法找到这样做的方法。

解决方案

脚本在约6分钟后超时，以防止无限循环运行程序。编辑：哎呀，忘了回答你的第一个问题：我认为onEdit是在重新计算值后运行的，但是我认为有一种手动方法可以停止脚本。显然我没有使用足够的公式来看这个。如果它不在等待，那么最好的办法就是做这样的事情：

  while（value ===Thinking .. 。|| value ===#NA）{
 Utilities.sleep（10000）; 
} 
  

暂停脚本几秒钟，然后再次检查。

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.

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.

解决方案

Scripts timeout after about 6 minutes to prevent infinite loops and constantly running programs. I don't think there's a manual way to stop a script.

Edit: Oops, forgot to answer your first question: I thought onEdit ran after values were recalculated, but apparently I don't use enough formulas to see this. If it's not waiting, then the best way is to do something like this:

while(value === "Thinking..." || value === "#NA") {
  Utilities.sleep(10000);
}

It pauses the script for a few seconds and then checks again.

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