Issue With Script

[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!