How to send conditional email alert on google sheet?

Question

I'm setting up an email alert on my google sheet, in which if a value in one column goes below 2 I get an email alert on my mail. I have mulitple domains and it is very tedious to check the domains expiry date every day. I created a sheet in which I put up domain name, registrar,hosted, todays date, expiry date, Expiry remaining days. Now I want to create an alert on "Expiry remaining days column", so that when the remaining days is below 2 days it send an alert email to my list of email addresses.

What I have already done is getting an email for one cell value but what I want to do is get it on whole column or number of column values.

 function CheckSales() {
  // Fetch the values
  var domainexpireRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("domain list").getRange("F2"); 
  var domainexpire = domainexpireRange.getValues();

  // Check domain expiring
  if ( domainexpire < 2){
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Company emails").getRange("B2:B4");
    var emailAddress = emailRange.getValues();

    // Send Alert Email.
    var message = '2019 smtp Domain Expiring in two days. Please check the sheet domain sheet '; // Second column
    var subject = '2019 smtp Domain Expiring';
    MailApp.sendEmail(emailAddress, subject, message);

  }
}

I have a sheet which has multiple rows and columns, what I want to do is check column F and if any of the value on column F is less than 2 then send an alert email to my email addresses. My code is working fine if I copy paste the code to multiple times and change the "getRange("F2");" to F2,F3,....F100 . But it is not good way to put up a code. Can anyone tell me if I can edit some code and get the result.

I tried using "getRange("F2:F");" & getRange("F2:F54");but it doesn't worked out.

Thanks for your help.

Answer 1

Try using a for statement to loop through the whole sheet, the one below should work:

function checkSales() {

  // Fetch the values    
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("domain list");
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  // loop through each row starting at row 2
  for (var i = 1; i < data.length; i++) {
    var domainExpire = data[i][5];

    // Check domain expiring
    if (domainExpire < 2) {
      var emailRange = spreadsheet.getSheetByName("Company emails").getRange("B2:B4");
      var emailAddress = emailRange.getValues();

      // Send Alert Email.
      var message = '2019 smtp Domain Expiring in two days. Please check the sheet domain sheet '; // Second column
      var subject = '2019 smtp Domain Expiring';

      MailApp.sendEmail(emailAddress, subject, message); 
    }
  }
}

Answer 2

Try this:

function CheckSales() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('domain list');
  var domainexpireRange=sh.getRange(1,6,sh.getLastRow()-1,1);
  var domainexpire=domainexpireRange.getValues();
  for(var i=0;i<domainexpire.length;i++) {
    // Check domain expiring
    if (domainexpire[i][5]<2){
      var emailRange=ss.getSheetByName("Company emails").getRange("B2:B4");
      var emailAddress=emailRange.getValues().map(function(r){return r[0];}).join(',');
      // Send Alert Email.
      var dateString=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy");
      var message=Utilities.formatString('%s smtp Domain Expiring in two days. Please check the sheet domain sheet.',dateString);
      var subject=Utilities.formatString('%s smtp Domain Expiring',dateString);
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}