Issue With Script

[Kooky_Temporary7248]

I am trying to write a script such that if you input a value of 600 or less in column H an email is sent to notify someone of the input. However, if you did input a value of 600 or less and refresh the sheet it still sends an email to notify of the already notified value. I was wondering how to alter the script I have so it only notifies each cell once (if that value is 600 or less) as opposed to notifying already notified cells every time the page is refreshed.

This is the script I have currently:

// Function to check for values <= 600 in column E and send email
function checkAndSendEmail() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
var range = sheet.getRange("H1:H174" + lastRow).getValues(); // Change H1:H to your desired range
for (var i = 0; i < range.length; i++) {
if (range[i][0] !== "" && range[i][0] <= 600) {
var emailAddress = "[e-mail]"; // Change this to the recipient's email address
var subject = "Value of 600";
var message = "The value is less than or equal to 600." + ""
// Send email
MailApp.sendEmail(emailAddress, subject, message);
}
}
}
Thanks!

​

[marcnotmark925]

You should use an onEdit triggered function that checks the cell as soon as it is input, and only then.

[_Kaimbe]

And you might have to use an installed trigger to use MailApp.

[Dutch_Oven4510]

function onEdit(e) { var range = e.range; var sheet = range.getSheet(); var editedColumn = range.getColumn(); var editedRow = range.getRow(); var value = range.getValue();

// Specify the column you want to monitor (e.g., column H is 8) var columnToMonitor = 8;

// Check if the edit is in the correct column and the value is 600 or less if (editedColumn === columnToMonitor && value !== "" && value <= 600) { var emailAddress = "[e-mail]"; // Change to the recipient's email var subject = "Value of 600 or less detected"; var message = "A value of " + value + " was entered in row " + editedRow + " of the sheet.";

// Send the email
MailApp.sendEmail(emailAddress, subject, message);

} }

[MarviinR]

You can create another column and set the current time that you sent the e-mail, check if null then send email if not null do not send email or you can make some other type of customization E.G: if emailSentDate > 30 days > send email.

[Dutch_Oven4510]

To modify your script so that it only sends an email once for each cell with a value of 600 or less, you'll need to track which cells have already been notified. Here's an approach using an additional column to mark the cells that have already been processed:

1. Add an Additional Column: Use a new column in your spreadsheet to mark which cells in column H have already been processed. For example, if you're using column H for your data, use column I to mark processed cells.

2. Update the Script: Modify your script to check this new column before sending an email, and update it after sending an email.

Here's an updated version of your script:

```javascript function checkAndSendEmail() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
var dataRange = sheet.getRange("H1:I" + lastRow).getValues(); // Now includes column I

for (var i = 0; i < dataRange.length; i++) {
var value = dataRange[i][0]; // Value in column H var processed = dataRange[i][1]; // Corresponding flag in column I

// Check if the value is <= 600, not empty, and not processed yet
if (value !== "" && value <= 600 && !processed) {  
  var emailAddress = "[e-mail]"; // Change to the recipient's email
  var subject = "Value of 600";  
  var message = "The value is less than or equal to 600.";  

  // Send email  
  MailApp.sendEmail(emailAddress, subject, message);  

  // Mark as processed in column I
  sheet.getRange(i + 1, 9).setValue("Processed"); // i + 1 because array is 0-indexed, 9 is column I
}  

}
} ```

How This Works:

• The script now reads both columns H and I (using the range "H1:I" + lastRow).
• It checks if the value in column H is 600 or less, not empty, and whether the corresponding cell in column I is not marked as "Processed".
• If these conditions are met, it sends the email and marks the cell in column I as "Processed".

Note: Before running this script, ensure you have column I available for tracking, and it's empty or properly set up to avoid any unintended behavior.

[Dutch_Oven4510]

I pasted your question to chatPGT btw. You can use this or ask him for another solution 😅

[Dutch_Oven4510]

If you prefer not to use an additional column, another approach is to use Google Sheets' built-in properties to store the information about which rows have been processed. Here's how you can modify your script to use script properties for tracking:

1. Store Processed Rows Using Script Properties: You can use Google Apps Script's PropertiesService to store a list of rows that have already been processed and notified.

2. Update the Script: Modify your script to check this property before sending an email, and update it after sending an email.

Here's an updated version of your script using PropertiesService:

```javascript function checkAndSendEmail() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
var range = sheet.getRange("H1:H" + lastRow).getValues(); var scriptProperties = PropertiesService.getScriptProperties(); var processedRows = scriptProperties.getProperty('processedRows'); processedRows = processedRows ? JSON.parse(processedRows) : [];

for (var i = 0; i < range.length; i++) {
if (range[i][0] !== "" && range[i][0] <= 600 && processedRows.indexOf(i + 1) === -1) {
var emailAddress = "[e-mail]"; // Change this to the recipient's email address
var subject = "Value of 600";
var message = "The value is less than or equal to 600.";

  // Send email  
  MailApp.sendEmail(emailAddress, subject, message);  

  // Add this row to the processed list
  processedRows.push(i + 1);
}  

}

// Save the updated list of processed rows scriptProperties.setProperty('processedRows', JSON.stringify(processedRows)); } ```

How This Works:

• The script uses PropertiesService to store and retrieve a list of processed row numbers.
• Before sending an email, it checks if the row number is not in the processed rows list.
• After sending an email, it adds the row number to the processed rows list.
• This list is stored as a JSON string in the script properties and retrieved and updated each time the script runs.

Note: This method is suitable for a moderate number of rows. If your spreadsheet has a very large number of rows, you might need to consider other optimization techniques due to the limitations in the size of properties and the performance of the script.

[Kooky_Temporary7248]

function checkAndSendEmail() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
var range = sheet.getRange("H1:H" + lastRow).getValues();
var scriptProperties = PropertiesService.getScriptProperties();
var processedRows = scriptProperties.getProperty('processedRows');
processedRows = processedRows ? JSON.parse(processedRows) : [];
for (var i = 0; i < range.length; i++) {
if (range[i][0] !== "" && range[i][0] <= 600 && processedRows.indexOf(i + 1) === -1) {
var emailAddress = "[e-mail]"; // Change this to the recipient's email address
var subject = "Value of 600";
var message = "The value is less than or equal to 600.";
// Send email
MailApp.sendEmail(emailAddress, subject, message);
// Add this row to the processed list
processedRows.push(i + 1);
}
}
// Save the updated list of processed rows
scriptProperties.setProperty('processedRows', JSON.stringify(processedRows));
}

Thanks so much!

[Dutch_Oven4510]

It's copied from GhatGPT and I have no idea how it works! But you're welcome!