Google Apps Script - Send Email based on date in cell

Question

I've looked around and have bits and pieces but can't put the puzzle together. I'm attempting to create a script that will run on a trigger configured to run daily. The trigger will be setup under Resources option in the editor.

Basically I'm looking for the script to capture a range of cells, identify a due date, which will be populated in a column, match it to the current date. If it matches then send a email. I've started with the send a email from spreadsheet tutorial at Google. I've added in a if statement to check for the date but I'm losing it on the comparsion to dataRange. Anyone might help correct these or point me to in direction to research.

The script appears to run but nothing happens, which I believe is because "if (currentTime == dataRange)" The dataRange is not matching correctly??

Here is the code:

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  // First row of data to process
var numRows = 50;   // Number of rows to process
// Fetch the range of cells
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
// Fetch values for each row in the Range.
var data = dataRange.getValues();   
//Get todays date     
var currentTime = new Date();
var month = currentTime.getMonth() + 1;
var day = currentTime.getDate();
var year = currentTime.getFullYear();      
//Test column  for date due & match to current date
    if ( currentTime == dataRange) {
  for (i in data) {
var row = data[i];
var emailAddress = row[0];  // First column
var message = row[1];       // Second column
var subject = "Task Item Due";
MailApp.sendEmail(emailAddress, subject, message);

}
}
}

I'm updating the suggestion provided by Srik and providing his suggestion in the below code. I've attempted to post this a couple time so I'm not sure why its not making past peer review?

 function sendEmail() {

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

var dataRange = sheet.getRange(startRow, 1, numRows, 50);

// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Browser.msgBox(data)

for (i in data) {
var row = data[i];
var date = new Date();
var sheetDate = new Date(row[1]);

if (date.getDate() == sheetDate.getDate() && date.getMonth() == sheetDate.getMonth() && date.getFullYear() == sheetDate.getFullYear());
{
  var emailAddress = row[0];  // First column
  var message = row[2];       // Second column
  var subject = "Sending emails from a Spreadsheet";
  MailApp.sendEmail(emailAddress, subject, message);
 // Browser.msgBox(emailAddress)

}

}

}

The code appears to run but I'm getting the following error w/in the script editor. "Failed to send email: no recipient (line 23)". But it still sends emails. It should compare the dates and only send the email if the date matches. Its sending an email for every row. I've shared out the spreadsheet to see the code and how the spreadsheet is setup. Shared Spreadsheet

UPDATED CODE W/ SERGE HELP on the logger and Utilities.formatDate.. Thanks!!

function sendEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  // First row of data to process
var numRows = sheet.getLastRow()-1;   // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)

for (i in data) {
  var row = data[i];
  var date = new Date();
  date.setHours(0);
  date.setMinutes(0);
  date.setSeconds(0);
  //Logger.log(date);
  var sheetDate = new Date(row[2]);
 //Logger.log(sheetDate);
 var Sdate = Utilities.formatDate(date,'GMT+0200','yyyy:MM:dd')
 var SsheetDate = Utilities.formatDate(sheetDate,'GMT+0200', 'yyyy:MM:dd')
     Logger.log(Sdate+' =? '+SsheetDate)
        if (Sdate == SsheetDate){
          var emailAddress = row[0];  // First column
          var message = row[1];       // Second column
          var subject = "Your assigned task is due today." +message;
          MailApp.sendEmail(emailAddress, subject, message);
          //Logger.log('SENT :'+emailAddress+'  '+subject+'  '+message)
            }    
   }
  }

Answer 1

here is a working version of your code, I used Utilities.formatDate to make strings of your dates, so you can choose what you compare (only days, hours ? min ?)

I commented the mail call just for my tests, re-enable it when you need

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  Logger.log(data)

  for (i in data) {
    var row = data[i];
    var date = new Date();
    var sheetDate = new Date(row[1]);
    Sdate=Utilities.formatDate(date,'GMT+0200','yyyy:MM:dd')
    SsheetDate=Utilities.formatDate(sheetDate,'GMT+0200', 'yyyy:MM:dd')
    Logger.log(Sdate+' =? '+SsheetDate)
    if (Sdate == SsheetDate){
      var emailAddress = row[0];  // First column
      var message = row[2];       // Second column
      var subject = "Sending emails from a Spreadsheet";
//      MailApp.sendEmail(emailAddress, subject, message);
     Logger.log('SENT :'+emailAddress+'  '+subject+'  '+message)
    }    
  }
}

don't forget to look at the logs ;-)

Answer 2

I would do it this way ....

Setup the spreadsheet like this: https://docs.google.com/spreadsheet/ccc?key=0AkGlO9jJLGO8dDJad3VNTkhJcHR3UXlJSVRNTFJreWc

Change the code to:

function sendEmails() {
  var spreadsheet = SpreadsheetApp.openById('Type spreadsheet key here from spreadsheet URL');       
  /// e.g.  var spreadsheet = SpreadsheetApp.openById('0AkGlO9jJLGO8dDJad3VNTkhJcHR3UXlJSVRNTFJreWc');     

  var sheet = spreadsheet.getSheets()[0]; // gets the first sheet, i.e. sheet 0

  var range = sheet.getRange("B1"); 
  var dateString = new Date().toString();
  range.setValue(dateString);   // this makes all formulas recalculate

  var startRow = 4;  // First row of data to process
  var numRows = 50;   // Number of rows to process
  // Fetch the range of cells
  var dataRange = sheet.getRange(startRow, 1, numRows, 4)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();   

  for (i in data) {
    var row = data[i];
    if( row[3] == true) {
      var emailAddress = row[0];  // First column
      var message = row[1];       // Second column
      var subject = "Task Item Due";
      try {
          MailApp.sendEmail(emailAddress, subject, message);
      } catch(errorDetails) {
        // MailApp.sendEmail("[email protected]", "sendEmail script error", errorDetails.message);
      }

    }
  }
}

The trigger:

Because of the trigger, you will need to open the spreadsheet using openById. To do this, in the code replace 'Type spreadsheet key here from spreadsheet URL'. To find the key, open the google docs spreadsheet, the link has "key=A0a0df..." paste the code. See the example.

JavaScript: if you want to learn more about using Java Script, I recommend http://www.w3schools.com/js/default.asp

Answer 3

You are comparing two different objects here

    if ( currentTime == dataRange) {

currentTime is a Date object whereas dataRange is a Range object - they'll never be equal and so nothing happens. What you could do is (DATE_COL is the column having your date)

for ( var i in data ){
  var row = data[i] ; 
  var today = new Date(); 
  var date = new Date(row[DATE_COL]) ; 

  if (today.getDate() == date.getDate() && 
      today.getMonth() == date.getMonth() && 
      today.getYear() == date.getYear() {
    /* Your email code here */ 
  }
}