How to get email alerts when a cell value changes (based on Formula) on Google sheets?

Question

I want a notification/email when a cell value in column 5 of the spreasheet changes to 'Buy' which is based on formula =IF(AND(B2>D2),"Buy","Skip"). Attachment Link provided below. The Sheet autorefreshes based on Googlefinance data.

I have tried the below script, but it triggers notification/Email only when I change the cell manually. I am not a code writer, but have managed to find below script and added some tweaks of my own. Can somebody please help me in resolving this or provide an alternate solution? Also script written on third Party apps will be appreciated.

function triggerOnEdit(e)

{
  showMessageOnUpdate(e);
}

function showMessageOnUpdate(e)
{
  var range = e.range;
  
  SpreadsheetApp.getUi().alert("range updated " +   range.getA1Notation());  
}

function checkStatusIsBuy(e)
{
  var range = e.range;
  
  if(range.getColumn() <= 5 && 
     range.getLastColumn() >=5 )
  {
    var edited_row = range.getRow();
    
    var strongBuy = SpreadsheetApp.getActiveSheet().getRange(edited_row,5).getValue();
    if(strongBuy == 'Buy')
    {
      return edited_row;
    }
  }
  return 0;
}
function triggerOnEdit(e)
{
  showMessageOnBuy(e);
}
function showMessageOnBuy(e)
{
  var edited_row = checkStatusIsBuy(e);
  if(edited_row > 0)
  {
    SpreadsheetApp.getUi().alert("Row # "+edited_row+"Buy!");
  }
}

function sendEmailOnBuy(e)
{
  var buy_row = checkStatusIsBuy(e);
  
  if(buy_row <= 0)
  {
    return;
  }
  
  sendEmailByRow(buy_row);
}

function sendEmailByRow(row)
{
  var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,5).getValues();
  var row_values = values[0];
  
  var mail = composeBuyEmail(row_values);
  
  SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"\n message "+mail.message);
}

function composeBuyEmail(row_values)
{
  var stock_name = row_values[0];
  
  var cmp = row_values[1];
  
  var volume = row_values[2];

  var message = "The Status has changed: "+stock_name+" "+cmp+
    " Volume "+volume;
  var subject = "Strong Buy "+stock_name+" "+cmp
  
  return({message:message,subject:subject});
}

function triggerOnEdit(e)
{
  sendEmailOnBuy(e);
}

var admin_email='sendemail@gmail.com';

function sendEmailByRow(row)
{
  var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,5).getValues();
  var row_values = values[0];
  
  var mail = composeBuyEmail(row_values);
  
  SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"\n message "+mail.message);

[https://docs.google.com/spreadsheets/d/12k5DF8rvuKex77B8uRWpx1FoMmSNaMdGvhNEEbXKCFc/edit?usp=sharing][1]

Answer 1

Apps script triggers cannot be trigerred by non manual input as specified in the documentation:

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

What you can do instead is to set up a time-based trigger that scans your column 5 at regular interval and sends you an email if a cell contains 'Buy':

function sendEmailOnUpdate() {
      var range = SpreadsheetApp.getActive().getRange('E2:E').getValues();
      
      for (i = 0; i < range.length; i++) {
        var cell = range[i];
    
        if (cell == 'Buy') {
          // Send email using Gmail
        }
      }
    }

To set the trigger, head to Triggers and select time-based:

If you don't want to receive more emails for a cell you receive an alert for, make sure to make apps script change its value once the email has been sent.