Member-only story
Steer Clear of These onEdit Traps in Google Apps Script: Top 3 Tips! 🚧
The onEdit trigger can feel unreliable and messy, but when done right it is powerful and reliable.
The onEdit trigger is likely the most used trigger in Google Apps Script. It runs automatically with an event object whenever you change a value in a spreadsheet — programmatic changes excluded — thus allowing you to execute a script based on context. When done properly, it can be extremely powerful. When done wrong, however, it can feel unreliable and messy.
In this article, we will learn to avoid three common pitfalls:
- Not Exiting Early
- Making a Single Function Do Everything
- Expecting onEdit to Catch All Changes by Default
For this purpose, we will build a simple script to handle a task list in Google Sheets. It will do two things: add a checkbox next to new tasks and add a completion date when each task is checked as done.
As usual, the final source code is available in a GitHub repository.
Before jumping into the code, be sure to review the documentation on how to create an onEdit trigger and what the event object looks like. Note that we will be using the range property from the event object quite extensively.
Pitfall 1: Not Exiting Early
onEdit reacts to each change that you make in your document. This means that it will be executed quite a lot. However, in most cases, you will only want it to work on a certain tab or within a certain range. To ensure that it is efficient and does not slow you down needlessly, the very first thing that you must do is test whether the conditions for your intended actions are met. Exit immediately if they are not.
In our case, we will only need the trigger to do something if columns A and B in our spreadsheet are changed. This is one way to do it:
Our spreadsheet will be quite basic; see the screenshot below for reference. Now, we will enter…
