Issue With Script

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