0

I'm trying to create an email trigger in Google Sheets that will send me an email anytime the Price Fair Value Ratio for any stock is less than 1 (meaning the stock is undervalued). The Ratio is in column 6 and I pull in other corresponding columns into the email that is sent. However, when I set it up the trigger to run on edit, it does not send the intended emails. Any help would be great, thanks!

    function FairValueNotification(e) {
      var r = e.range;
  var s = r.getSheet();
  var ss = e.source;
  var sName = s.getSheetName();
  if(sName != "Brokerage" || r.getColumn() != 6) return;
     //Price Fair Value Ratio
  var value = e.value;
     //Target Number of Shares
  var value6 = s.getRange(r.getRow(), 13).getValue();
    //Current Number of Shares
  var value7 = s.getRange(r.getRow(), 12).getValue();
    //Number of Shares to Buy
  var value8 = value6 - value7
  if(value > 1) return;
  if(value7 > value6) return;  
    //Stock Name
  var value2 = s.getRange(r.getRow(), 1).getValue();
    //Stock Current Price
  var value3 = s.getRange(r.getRow(), 9).getValue();
    //Price Fair Value $
  var value4 = s.getRange(r.getRow(), 5).getValue();
    //Stock Ticker
  var value5 = s.getRange(r.getRow(), 2).getValue();
  value = value.toString();
  var email = Session.getActiveUser().getEmail();
  GmailApp.sendEmail("EMAIL", 'STOCK PRICE ALERT: ' + value2 + ' (' + value5 + ') - Brokerage', 'The following stock is currently undervalued:' + '\n\n' + 'Stock: ' + value2 + '\n' + 'Ticker: ' + value5 + '\n' + 'Current Price: $' + value3 + '\n' + 'Stock Fair Value: $' + value4 + '\n' + 'Stock Value Ratio: ' + value + '\n\n' + 'You should buy ' + value8 + ' shares.' + '\n' + 'You currently own ' + value7 + ' shares and have a target of ' + value6 + ' shares.');
}
Eyes0ftheworld
  • 1
  • 2
  • Your following code `GmailApp.sendEmail("EMAIL", .. , ... )` has no defined email address i.e "EMAIL". Did you do that to obfuscate the code or is that a bug? Secondly, is the sheet updated by a person or by importHTML (or similar) function? – Jack Brown Jan 09 '18 at 18:05
  • Thanks for your comments. I did that to hide my email - not a bug. I set the sheet to update formulas every minute, however, I also tried manually updating the share price column without any result. – Eyes0ftheworld Jan 09 '18 at 19:18
  • Few things you can try, 1) Delete trigger and reset the trigger 2) Set notifications to fire immediately so you get an email right away when there is error 3) Try running the above code by itself by using a secondary function to call this above function. – Jack Brown Jan 09 '18 at 19:51
  • Thanks - what sort of secondary function would I write? I'm fairly new to this. – Eyes0ftheworld Jan 09 '18 at 19:56
  • The above code is terminated if the share ration is less then 1 `if(value < 1) return;`. If you want the code to run when the value is less then 1 you should modify it to the following `if(value > 1) return;`. And you can the find example of secondary function call [here](https://pastebin.com/gYh5RH4F) – Jack Brown Jan 09 '18 at 20:16
  • Thanks! I got the email to send. However, it only sends when I manually update the "Price Fair Value $" column. That column is a formula and is automatically updated based on the formula "Current Price" / "Fair Value". Is there a way for it to recognize changes due to formula and not a physical update of that column? – Eyes0ftheworld Jan 09 '18 at 20:53
  • I don't believe you can detect an update of a formula. However, you can check for the value of ratio in a particular row each time an update is made in that row, like so `var value = s.getRange(r.getRow(),6).getValue()`. Then check to see if the value is greater or lower then 1. – Jack Brown Jan 09 '18 at 21:57
  • Is there a way to edit my code above to work with a time trigger? Like every day, it sends the emails for those stocks where both "Fair Value Threshold" is less than 1 and "Current # Shares" is less than "Target Number Shares"? – Eyes0ftheworld Jan 09 '18 at 22:31

0 Answers0