Sending an email each time a condition is met from a Spreadsheet in Apps script-CodePudding

I'm trying to write some code in Apps Script that triggers an email each time a condition is fulfilled in a Spreadsheet.

This spreadsheet contains the age of different transgenic lines of fish (the ages of the fish are automatically updated in the spreadsheet) and each of these transgenic lines has an associated caretaker with an email address. My idea is to trigger an automatic email using Apps script that is sent to the assigned caretaker each time one of these transgenic lines becomes older than 2 years old. However, I haven't been able to make it work yet. I'm not really sure which part of my code is preventing it from working properly.

Below I attach an example of how the spreadsheet would look like, as well as an example of the code that I've been trying to use (I'm a beginner when it comes to coding, so it's possible that there are many basic errors in it):

function fishalert() {
  var subject = 'Fish aging alert';
  var years = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("C2:C10").getValues();
  if (years > 2){
    for(r=2;r<20;r  ){
      var name = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(r,1).getValue();
      var emailaddress = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(r,4).getValue();
      var message = 'Line '   name   ' is more than 2 years old';
      MailApp.sendEmail(emailaddress, subject, message);
    }
  }
}

CodePudding user response：

Sending Email when conditions are met by sampling once a day

function fishalert(e) {
  if (e['day-of-week'] < 6) {//sends emails mon through fri between 9 and 10 in the morning
    const ss = SpreadsheetApp.getActive();
    const sh = ss.getSheetByName('Sheet1');
    const vs = sh.getRange(2, 1, sh.getLastRow() - 1, 4).getValues();
    vs.forEach(r => {
      if (r[2] > 2) {
        MailApp.sendEmail(r[3], 'Fish aging alert', `Line ${r[0]} is more than 2 years old`);
      }
    });
  }
}

Run this once:

function createTimeBasedTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == 'fishalert').length == 0) {
    ScriptApp.newTrigger('fishalert').timeBased().everyDays(1).atHour(9).create();
  }
}

For future enhancements

you will probably only want to send these emails on a less frequent schedule and probably only once when the threshold is achieved and you'll probably want to collect one email for each unique email address. But this is an answer to your current question