CODEWITHSUNDEEP
triggersgoogle-apps-scriptgoogle-sheetsgoogle-sheets-apidelayed-execution
Alan Turing
Alan Turing

Reputation: 12571

Delayed Execution on Edit with Google Apps Script

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:

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?

Upvotes: 2

Views: 2650

Answers (1)

Ryan Roth
Ryan Roth

Reputation: 1424

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.

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:

  1. A change is made and onEdit() triggers
  2. The onEdit() trigger only records the changed cell locations to a local variable or Cache
  3. A time-based trigger fires every minute/hour/whenever
  4. 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.

Upvotes: 2

Related Questions