Google Apps Script - Send email by date in cell

Question

I looked around and pieces, but I can not assemble puzzles. I am trying to create a script that will run on a trigger configured for daily work. The trigger will be configured in the "Resources" section of the editor.

Basically I am looking for a script to capture a range of cells, determine the execution date to be filled in the column, corresponding to the current date. If it matches, then send an email. I started by sending email from a spreadsheet tutorial to Google. I added an if statement to check the date, but I lose it when comparing with dataRange. Anyone can help me fix this or point me in the direction of the research.

It looks like the script is working, but nothing is happening, and I believe that the "if (currentTime == dataRange)" DataRange does not match 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 am updating Srinik's offer and suggesting his offer in the code below. I tried to publish this a couple of times, so I'm not sure why he is not doing the previous 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 will appear, but I get the following w / error in the script editor. "Failed to send email: no recipient (line 23)." But he still sends emails. It should compare dates and send by email only if the date matches. Its sending email for each line. I shared a spreadsheet to see the code and spreadsheet settings. General table

UPDATED W / SERGE HELP CODE on the registrar and Utilities.formatDate .. Thank you!

 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 the working version of your code, I used Utilities.formatDate to create the strings of your dates, so you can choose what you are comparing (only days, hours? min?)

I commented on the mail call only for my tests, turned it back on 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) } } } 

do not forget to look at the magazines; -)

Answer 2

I would do it like this ...

Customize the table as follows: 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'); /// eg var spreadsheet = SpreadsheetApp.openById('0AkGlO9jJLGO8dDJad3VNTkhJcHR3UXlJSVRNTFJreWc'); var sheet = spreadsheet.getSheets()[0]; // gets the first sheet, ie 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(" [e-mail] ", "sendEmail script error", errorDetails.message); } } } } 

Trigger:

Because of the trigger, you will need to open the table using openById. To do this, in the code, replace "Enter a spreadsheet here from the spreadsheet URL." To find the key, open the Google docs table, the link "key = A0a0df ..." inserts the code. See an example.

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

Answer 3

Here you are comparing two different objects.

  if ( currentTime == dataRange) { 

currentTime is a Date object, while dataRange is a Range object - they will never be equal, and therefore nothing will happen. You can do it (DATE_COL is a column with 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 */ } }