CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Max Kimmel
Max Kimmel

Reputation: 21

Sending out emails based on date in Google Sheets Scripts

I'm trying to create a Script that will send an email for every cell that contains today's date. Here's what I have so far:

function email() {

   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheets()[0];
   var rowCounter = 1;
     var limit = sheet.getLastRow();

  // Fetch the estimated dates & Today's date
  var estimatedReturnDateRange = sheet.getRange("F:F"); 
    var estimatedReturnDate = estimatedReturnDateRange.getCell(rowCounter, 1);
  var todayDateRange = sheet.getRange("S1"); 
  var todayDate = todayDateRange.getValue();


  // Check totals sales
  for(i=1; i<=limit; i++){

  if (estimatedReturnDate = todayDate){
      // Fetch the email address
      var emailAddress = "[email protected]";

      // Send Alert Email.
      var message = estimatedReturnDate; // Second column
      var subject = 'Your Google Spreadsheet Alert';
      MailApp.sendEmail(emailAddress, subject, message);


  }
  rowCounter++;
  estimatedReturnDate = estimatedReturnDateRange.getCell(rowCounter, 1);

  }  
  rowCounter =1;
}

This is how I envision the logic of the script working:

estimatedReturnDate initially grabs the first cell in column F, which is a list of dates.

todayDate grabs cell S1, which contains today's date.

a for loop then loops through all rows of the sheet, and checks if estimatedReturnDate = todayDate. If it does, an email is sent that contains the Row Number that matched today's date.

Then, rowCounter is incremented, estimatedReturnDate is set to the next cell in the row, and the loop runs again.

The problem I'm having is that when I run this script, an email is sent out for each row in the sheet, regardless of whether estimatedReturnDate matches todayDate or not.

Does anyone know what would be causing this?

Upvotes: 1

Views: 1203

Answers (1)

Cooper
Cooper

Reputation: 64062

function email() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheets()[0];//This is always the left most sheet but not necessarily the same sheet depending how users move the sheets around.
  var vA=sheet.getRange(1,5,sheet.getLastRow(),1).getValues()
  var dt=new Date();
  var toda=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();//midnight yesterday
  var tmro=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()+1).valueOf();//midnight today
  for(var i=0;i<vA.length;i++){
    var dt=new Date(vA[i][0]).valueOf();//date from column5 of spreadsheet
    //dt is between midnight yesterday and midnight today
    if(dt>=toda && dt<=tmro){
      var emailAddress = "[email protected]";
      var message = Utilities.formatDate(dt, Session.getScriptTimeZone(), "E MMM dd, yyyy");
      var subject = 'Your Google Spreadsheet Alert';
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }  
}

Utilities.formatDate

Date Class

Upvotes: 2

Related Questions