使用Google Apps脚本进行编辑时延迟执行 [英] Delayed Execution on Edit with Google Apps Script
问题描述
我有一个相对较大的电子表格（300行30列），我根据电子表格中的值对其进行着色。我只使用两次访问来最少访问API：
I have a relatively large spreadsheet (300 rows, 30 columns) that I color based on the values in the spreadsheet. I'm doing accessing the API minimally using only two accesses:
-
getValues（...）以访问数据范围的所有值。 -
setBackgrounds（...）可以设置数据的所有背景
getValues(...)to access all the values of the data range.setBackgrounds(...)to set all the backgrounds of the data range.
运行时间约为半秒或更短。但是，如果我让它使用 onEdit（）在每次编辑时运行，就会受到影响，但是我也不想在我定期进行更新时没有编辑，似乎很浪费。有没有一种好的方法可以使脚本以延迟的方式运行，并在编辑时按固定的时间间隔进行更新？
This runs in about half a second or less. However, it gets in the way if I make it run on every edit using onEdit(), but I also don't want it to be updated at regular time intervals when I'm not editing it, seems like a waste. Is there a good way to make the script run in a "delayed" way, updating at regular time intervals while I'm editing?
推荐答案
首先，我要说的是，您应该查看Google表格的条件格式（表格中的格式>条件格式菜单项），您可能不需要使用Apps脚本就可以执行所需的大部分工作。
Firstly, I would say you should look at Google Sheets' conditional formatting (Format > Conditional formatting menu item in Sheets) -- you may be able to do much of what you need without involving Apps Script at all.
如果不成功，您可以设置基于时间的常规触发器，以检查修改并适当更改背景。您可以使用单独的onEdit（）触发器来支持此触发器，以记录内部发生的更改。流程如下：
Failing that, you can set up a regular time-based trigger to check for edits and change the backgrounds appropriately. You can support this trigger with a separate onEdit() trigger to record what has changed internally. The flow goes like this:
- 进行更改并触发onEdit（）
- onEdit （）触发器仅将更改的单元格位置记录到本地变量或Cache
- 基于时间的触发器每分钟/小时/每当触发
- 基于时间的触发器会检查缓存中是否有已编辑的单元格，更改其背景，然后从缓存中清除它们
- A change is made and onEdit() triggers
- The onEdit() trigger only records the changed cell locations to a local variable or Cache
- A time-based trigger fires every minute/hour/whenever
- The time-based trigger checks the cache for edited cells, alters their backgrounds, then clears them from the cache
在工作流程中，这种方法可能并不比仅使用时间触发器直接更改单元格更好。
That said, depending on your workflow this approach may not be much better than simply using a time trigger to change the cells directly.
这篇关于使用Google Apps脚本进行编辑时延迟执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
