Combining runs from "on edit" or "on change" triggers in Google Apps Script

Question

Combining runs from "on edit" or "on change" triggers in Google Apps Script

I've been working on a script to alert me about low stock items in my inventory tracking spreadsheet, but the script works a little too well.

The issue is that each time I update the inventory for 10 items, the script runs and sends out 10 email notifications due to Google's "On Edit" trigger. To try and consolidate all these emails into one, I attempted to use Utilities.sleep(300000) to create a delay before the script runs again. However, this just resulted in the same 10 emails being delayed by 5 minutes.

It seems like Google processes the script in parallel with every trigger activated, causing multiple notifications to be sent out instead of just one consolidated email.

Any suggestions on how I can modify my script to only send a single email notification after all edits have been made?

This is my first coding project, so I'm still learning. Any guidance or help would be greatly appreciated!

javascript google-apps-script google-sheets triggers

Answer 1

Answer №1

Before anything else, take a look at the resources provided in this link. My suggestion would be to utilize the update(recipient, subject, body) function to configure the emails for continuous updating of the same email. Following this, you can incorporate an "On Edit" feature to activate a timer that will send the email after each edit and a designated delay period.

Answer 2

Before anything else, take a look at the resources provided in this link. My suggestion would be to utilize the update(recipient, subject, body) function to configure the emails for continuous updating of the same email. Following this, you can incorporate an "On Edit" feature to activate a timer that will send the email after each edit and a designated delay period.

Answer 3

Answer №2

Concern:

A situation arises where the onEdit trigger activates every time the script undergoes editing. To avoid triggering an email with each edit, using the onEdit trigger may not be the most suitable choice.

Solution #1. Utilize a button or custom menu:

To address this issue, consider executing the function through a button or a custom menu. This way, you have control over when to send an email.

Solution #2. Time-driven approach:

If manual triggering is not preferred, another option is setting up a time-based trigger (refer to available triggers here). This trigger can run at specified intervals such as hourly, daily, or every few minutes to check for changes in low stock items and send emails accordingly.

To track changes effectively, consider using PropertiesService to store low stock items during each email alert and compare them with current data upon trigger execution.

In essence, the script operated by the time-driven trigger should perform the following tasks:

function sendEmailIfChanges() {
  // Retrieve low stock items from spreadsheet
  // Fetch stored properties from previous run
  // Compare current and stored low stock items
  // If differences are found (e.g., item IDs or quantities), 
  // send email and update low stock items using PropertiesService
}

Resources:

Answer 4

Concern:

A situation arises where the onEdit trigger activates every time the script undergoes editing. To avoid triggering an email with each edit, using the onEdit trigger may not be the most suitable choice.

Solution #1. Utilize a button or custom menu:

To address this issue, consider executing the function through a button or a custom menu. This way, you have control over when to send an email.

Solution #2. Time-driven approach:

If manual triggering is not preferred, another option is setting up a time-based trigger (refer to available triggers here). This trigger can run at specified intervals such as hourly, daily, or every few minutes to check for changes in low stock items and send emails accordingly.

To track changes effectively, consider using PropertiesService to store low stock items during each email alert and compare them with current data upon trigger execution.

In essence, the script operated by the time-driven trigger should perform the following tasks:

function sendEmailIfChanges() {
  // Retrieve low stock items from spreadsheet
  // Fetch stored properties from previous run
  // Compare current and stored low stock items
  // If differences are found (e.g., item IDs or quantities), 
  // send email and update low stock items using PropertiesService
}

Combining runs from "on edit" or "on change" triggers in Google Apps Script

Answer №1

Answer №2

Concern:

Solution #1. Utilize a button or custom menu:

Solution #2. Time-driven approach:

Resources:

Similar questions

Why is it that styling <div> and <img> with a URL doesn't seem to work, even when applying the same styles?

Select state and city options similar to the national breakdown page

Strategies for avoiding sudden movements in the navigation bar?

Tips on changing tabs by simply clicking the save button or linking the save button to tab-switching functionality

retrieving the value of an object key based on changing information

Retrieving MongoDB Records in Meteor

Express.js never terminates a session

Traverse through the nested JSON array using a loop

Placing emphasis on an object that appears following a period of waiting

Determining the best application of guards vs middlewares in NestJs

Vue automatically refreshes momentjs dates prior to making changes to the array

Ensure that video controls remain visible for 3 seconds after hovering over them

Retrieve the nth element from an array using a function that requires 2 arguments

Returning a JSON representation of a JavaScript object

elimination of nonexistent object

Issue alert before running tests on component that includes a Material UI Tooltip

Enhancing webpage performance by updating CSS properties dynamically based on mouse movement using JavaScript

"Encountered a Json error with a bizarre character causing the string

Stubborn boolean logic that refuses to work together

Running globally installed node modules on Windows 7 is not supported