GAS script for Daily Stock Price Alert with Google Sheets

Question

I found a great article with a GAS script for a stock alert system in Google Sheets. It has inspired me to build something more extensive including alerts for daily % moves but that's beyond the point.

The author recommends applying a time-based trigger so the receiver gets a real time email alert when the stock hits the predefined target price. The script works great, but has one problem: it will send an email during every cycle as long as the condition holds true. In other words, you will be bombarded with emails if the trigger is set to every minute.

Here's the basis script.

    function myFunction() {
      
      var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
      
      var values = spreadSheet.getDataRange().getValues();
      values.shift();
      
      var emails = ['[email protected]'];
      
      for (stock in values) {
        
        // Grabs the array we want to access
        var stockArray = values[stock];   
        
        // The first item in the array is the ticker
        var stockTicker = stockArray[0];  
        
        // The CURRENT price of the stock
        var stockCurrent = stockArray[1]; 
        
        // Mathematical symbol to compare the prices (>,<)
        var stockSymbol = stockArray[2];  
        
        // The TARGET price of the stock 
        var stockTarget = stockArray[3];  
        
        // If we want to use the less than symbol
        if ( stockSymbol === '<' ) {
          
          // If current price is less than target
          if ( stockCurrent < stockTarget) {
            // Do something
          }
        }
        
        // If we want to use the greater than symbol
        if ( stockSymbol === '>' ) {
          
          // If current price is greater than target
          if ( stockCurrent > stockTarget) {
            // Do something
          }
        }   
      } 
    }

I want to receive the alert only once (per day). This author solved a similar issue by adding the following function after an 'else' condition:

function createTriggerCheckAgain() {
      ScriptApp.newTrigger('myFunction')
      .timeBased()
      .after(60 * 60 * 24 * 1000) // one day cycle time
      .create();}
      }

I tried to add this function to the original script but unfortunately I still get an alert email every time the script runs.

To check if the script works in real time I've added an email function. Putting it all together gives the following. Here's a link to the spreadsheet.

function myFunction() {
  
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  
  var values = spreadSheet.getDataRange().getValues();
  values.shift();
  
  var emails = ['[email protected]'];
  
  for (stock in values) {
    
    // Grabs the array we want to access
    var stockArray = values[stock];   
    
    // The first item in the array is the ticker
    var stockTicker = stockArray[0];  
    
    // The CURRENT price of the stock
    var stockCurrent = stockArray[1]; 
    
    // Mathematical symbol to compare the prices (>,<)
    var stockSymbol = stockArray[2];  
    
    // The TARGET price of the stock 
    var stockTarget = stockArray[3];  
        
    // Emails
    var emailSubject = 'PRICE ALERT';
    var emailBody = 'your stock '+stockTicker+' has moved to its target price';
    
    // If we want to use the less than symbol
    if ( stockSymbol === '<' ) {
      
      // If current price is less than target
      if ( stockCurrent < stockTarget) {
        
        for (email in emails) {
          MailApp.sendEmail(emails[email], emailSubject, '', {
            htmlBody: emailBody
          });
        }
        
      }
      else {
        function createTriggerCheckAgain() {
          ScriptApp.newTrigger('myFunction')
          .timeBased()
          .after(60 * 60 * 24 * 1000)
          .create();
    }
  }
    }
    
    // If we want to use the greater than symbol
    if ( stockSymbol === '>' ) {
      
      // If current price is greater than target
      if ( stockCurrent > stockTarget) {
        
        for (email in emails) {
          MailApp.sendEmail(emails[email], emailSubject, '', {
            htmlBody: emailBody
          });
        }
        
      }
    }
   else {
    function createTriggerCheckAgain() {
      ScriptApp.newTrigger('myFunction')
      .timeBased()
      .after(60 * 60 * 24 * 1000)
      .create();
    }
  } 
  }
  
}

Any tips/help would be much appreciated.

Btw, a commenter on the original article suggested a different route:

// Named range could be get spreadsheet-wise
var notified_range = spread_sheet.getRangeByName('Notified?');
var notified_values = notified_range.getValues();
...
// We don't want to spam ourselves
var isNotified = notified_values[+stock+1][0];
...
if ( stockSymbol === '<' ) {
if ( stockCurrent < stockTarget && isNotified != true) {
for (email in emails) {
MailApp.sendEmail(emails[email], emailSubject, '', {
htmlBody: emailBody
});
}
// GetCell index starts from 1, not 0. So we +2
notified_range.getCell(stock+2, 1).setValue(true);
}
}

I've tried to implement these in the original code. Also without success.

here's the spreadsheet: data + script docs.google.com/spreadsheets/d/… — Webko Wuite

NightEye NightEye · Accepted Answer · 2021-02-04T15:49:50

There was a bracket mismatch on your code and some redundant pieces I tried optimizing.

Can you check if this does what you want?

Code:

function myFunction() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var values = spreadSheet.getDataRange().getValues();
  values.shift();

  var stocks = [];
  var emails = ['[email protected]'];
  var emailSubject = 'PRICE ALERT';
  var emailBody = 'The following tickers reached its target price:';

  values.forEach(function (stockArray, index) {
    // ticker, CURRENT price, comparison symbol, TARGET price
    var [stockTicker, stockCurrent, stockSymbol, stockTarget, dates] = stockArray;
    
    var dateToday = convertDate(new Date());
    // Make string into array and removes all blank elements
    var dateArray = dates.toString().split(",").filter(date => date);
    // Properly format dates 
    dateArray.forEach(function (date, index){
      dateArray[index] = convertDate(new Date(date));
    });
    
    // If current price is <stockSymbol> than target and dateToday isn't in column E
    if (eval(stockCurrent + " " + stockSymbol + " " + stockTarget) && !dateArray.includes(dateToday)) {
      // Take note of the tickers so we can send in 1 email
      stocks.push(stockTicker);
      // Add dateToday to dateArray if date is new
      dateArray.push(dateToday);
      // Set dateArray as string as value to column E 
      spreadSheet.getRange("E" + (index + 2)).setValue(dateArray.join(","));
    }
  });

  // Only proceed to mail if we had a new valid ticker that satisfies the condition
  if(stocks.length > 0) {
    // Join all stocks in one email per user, send as 1 email
    emailBody = emailBody + "<br> - " + stocks.join("<br> - ");
    emails.forEach(function (email) {
      MailApp.sendEmail(email, emailSubject, '', {
        htmlBody: emailBody
      });
    });
  }

  // Create trigger after every run (will trigger every minute)
  createTrigger();
}

function convertDate(date){
  // Convert date to proper timezone
  return Utilities.formatDate(date, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "MM/dd/YYYY");
}

function createTrigger(){
  // Delete all existing triggers before creating one
  // Ensuring none will exist before creating trigger.
  var triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function (trigger){
    ScriptApp.deleteTrigger(trigger);
  });

  // Create trigger after every run which is per minute
  ScriptApp.newTrigger('myFunction')
    .timeBased()
    .after(60 * 1000)
    .create();
}

Sample Output:

Note:

If I'm not mistaken, price of these tickers can change any time but only once daily. So the code above the code above will only email you the first time the condition is satisfied that day.

Triggers here will not reach the limit since I always delete all triggers before adding one so we are sure that it won't fail due to reaching the limit of the triggers.

To test, just run the function once, and then let the trigger do it's job.

Also, you might run into issue due to quotas. For more information/details, read the resource below:

GAS script for Daily Stock Price Alert with Google Sheets

1 Answers

Resource/s: