CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Desmond Yjw
Desmond Yjw

Reputation: 43

GoogleSheet Cell Value Trigger

can anyone show me how to create a script to trigger email notification when a cell value (SP) met with the target value(target sp)?

SP Column Formula is as below:

function ASXLASTPRICE(asx_stock) {
  var tickervar = asx_stock.substring(asx_stock.indexOf(":") + 1);
  var url = "https://www.asx.com.au/asx/1/share/" + tickervar;
  var response = UrlFetchApp.fetch(url);
  var content = response.getContentText();
  Logger.log(content);
  var json = JSON.parse(content);
  var last_price = json["last_price"];
  return last_price;
}

Data Sample: enter image description here

Upvotes: 0

Views: 55

Answers (1)

Cooper
Cooper

Reputation: 64110

Sending an email when last price get's larger using timebased trigger

You can use the trigger event object to control when triggers run so that you are not running when stock exchange is closed.


     function asxLastPrice(e) {
      if (e['day-of-week'] < 6 && e.hour > 8 && e.hour < 3) {
        let lp = PropertiesService.getScriptProperties().getProperty('lastprice');
        var tickervar = asx_stock.substring(asx_stock.indexOf(":") + 1);
        var url = "https://www.asx.com.au/asx/1/share/" + tickervar;
        var response = UrlFetchApp.fetch(url);
        var content = response.getContentText();
        var json = JSON.parse(content);
        var last_price = json["last_price"];
        if (last_price > lp) {
          PropertiesService.getScriptProperties().setProperty('lastprice', last_price);
          GmailApp.sendEmail('recipient', 'subject', 'message');
        }
        return last_price;
      }
    }

    function createtrigger() {
      if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "ASXLASTPRICE").length == 0) {
        ScriptApp.newTrigger('ASXLASTPRICE').timeBased().everyMinutes(5).create();
      }
    }

Upvotes: 1

Related Questions