CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
THRILLHOUSE
THRILLHOUSE

Reputation: 163

How to send email if cell date equals today

I am trying to create a Google Apps Script that works with a spreadsheet to send out an email if certain criteria is met. Specifically to send out an email if Column C equals today & column A equals false.

Link to the spreadsheet: https://docs.google.com/spreadsheets/d/1kULWOMtZaay6PcgF5XTwVoJnKPo7JSA_AK50J8RNYzk/edit?usp=sharing

I was able to set this up to that the spreadsheet handles most of the work. Column D checks for the date, and that column A is checked and then the script will send when column D reads TRUE. I am wondering if I can have the Google Apps Script check for today's date, instead of the spreadsheet. 

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 50;   // Number of rows to process
  var numOfColumns = sheet.getLastColumn();

  // Fetch the range of cells
  var dataRange = sheet.getRange(startRow, 1, numRows, numOfColumns);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues(); 

  var sendTrigger = "";

  var i = 0;
  for (i=0;i<data.length;i++) {
    var row = data[i];
    var emailAddress = row[4];  // fifth column
    var message = row[5];       // sixth column

    sendTrigger = row[3];

    if (sendTrigger == 1) {
      var subject = ("This is a test of the send email function");
      MailApp.sendEmail(emailAddress, subject, message);
    };
  };
};

I want the script to check column A and column C and send out an email if Column A equals FALSE and column C equals TODAY

Upvotes: 0

Views: 656

Answers (1)

Cooper
Cooper

Reputation: 64042

Try this:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 50;   // Number of rows to process
  var numOfColumns = sheet.getLastColumn();
  var dataRange = sheet.getRange(startRow, 1, numRows, numOfColumns);
  var data = dataRange.getValues(); 
  var sendTrigger = "";
  var dt=new Date();
  var dv=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  for (var i=0;i<data.length;i++) {
    var row = data[i];
    var emailAddress = row[4];  // fifth column
    var message = row[5];       // sixth column
    var d=new Date(row[2]);
    if (!row[0] && new Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf()==dv) {
      var subject = ("This is a test of the send email function");
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

Upvotes: 2

Related Questions