EN VI

Google-apps-script - Apps Script - Less Than operator not functioning correctly?

How to Google-apps-script - Apps Script - Less Than operator not functioning correctly

I have a price tracker of investment assets that already updates automatically from the web using IMPORTXML. I have set up another sheet in this workbook that includes Sell Targets, and pulls the current prices from the price tracker sheet. I would like to have the spreadsheet email me if the Price goes over the Sell Target, and then mark a reserved column as "SENT" to keep the trigger from repeatedly sending emails.

I'm close to getting it working, but I must be missing something fundamental with Apps Script arrays. When I log from within the loop using console.log, I can see that 4 total cells are being marked as "SENT" when only one of them should be. Can you tell me what I am doing wrong?

function sendEmail() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sell Targets");
  
  var SellTargetRange = sheet.getRange("W3:W");
  var SellTarget = SellTargetRange.getValues();
  
  var PriceRange = sheet.getRange("D3:D");
  var Price = PriceRange.getValues();
  
  var EmailSentRange = sheet.getRange("X3:X");
  var EmailSent = EmailSentRange.getValues();
  
  var lastRow = sheet.getLastRow();


  for (var i = 0; i < (lastRow-1); i++) {
    if ((SellTarget[i] < Price[i]) && (EmailSent[i] != "SENT")) {
      //var emailAddress = "[email protected]";
      //var subject = "subject";
      //var message = "sell";
      
      //MailApp.sendEmail(emailAddress, subject, message);

      
      sheet.getRange("X" + (i+3)).setValue("SENT");


    console.log(EmailSent[i])
    console.log(Price[i])
    console.log(SellTarget[i])
      }
  }
}

Solution:

Try it this way:

SellTarget, Price and EmailSent are two dimensional arrays not flat.

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sell Targets");
  const vs = sh.getRange(3, 1, sh.getLastRow() - 2, 24).getValues();
  vs.forEach((r, i) => {
    if (r[22] < r[3] && r[23] != "SENT") {
      let emailAddress = "[email protected]";
      let subject = "subject";
      let message = "sell";
      MailApp.sendEmail(emailAddress, subject, message);
      sh.getRange(i + 3, 24).setValue("SENT");
    }
  });
}