CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Madhurjya Sharma
Madhurjya Sharma

Reputation: 25

Trying to match the date in the spreadsheet with today's date and send email

I have a spreadsheet which looks like this:

Date     |  Day  |  S1   | S2   |  S3   |  S4   |   S5   |  S6  |
-----------------------------------------------------------------       
14/04/20 |  Sun  |  P-1  | H-1  | E-1   | R-1   |  F-1   |  G-1 |
15/04/20 |  Mon  |  P-1  | H-1  | E-3   | R-1   |  F-2   |  G-2 |

Intention is to send an email the schedules (S1 till S6) on that particular date. The script would run on a particular time of the day. It will compare today's date with the date on the first column. if the date matches, the data on that row should be sent to my email address.

I have written the code as below:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 4; //the date starts from the fourth row
  var numRows = 170; //there are a total of 170 rows

  var dataRange = sheet.getRange(startRow, 1, numRows, 8);
  var data = dataRange.getValues();
  for (var i in data) {
    var row = data[i];
    var dateIter = row[0];
     if (dateIter.getDate() == today.getDate() && 
        dateIter.getMonth() == today.getMonth() && 
          dateIter.getFullYear() == today.getFullYear()) {
            var sub1 = row[2];
            var sub2 = row[3];
            var sub3 = row[4];
            var sub4 = row[5];
            var sub5 = row[6];
            var sub6 = row[7];
            var subject = 'Things you have to study today';
            var message = "You have to study today" + sub1 + ", " + sub2 + ", " + sub3 + ", " + sub4 + ", " + sub5 + ", and " + sub6 + ". All the best." ;
            break;
          }
  }
  var emailAddress = 'myemail goes here';
    MailApp.sendEmail(emailAddress, subject, message);
}

I am not sure about the date functions as I wrote in my code. I am getting an error as below:

TypeError: dateIter.getDate is not a function (line 11, file "Code")

I learnt about the date functions that I wrote here in this answer https://stackoverflow.com/a/14351783/9422511

What is the proper way of doing this?

Edit: Added what Michael has suggested. There is another error which is as shown below:

ReferenceError: today is not defined (line 12, file "Code")

Upvotes: 0

Views: 262

Answers (2)

Madhurjya Sharma
Madhurjya Sharma

Reputation: 25

I have solved the problem. I did the following:
1. Cleared the date column, re-formatted it as date. And entered the date again according to the date format in the column.
2.Modified the date portion as suggested by Michael Pearson.
3. 'Today' has been made a object as below:

var today = new Date();
  1. I was doing a mistake initially. The mailing portion of the code is put inside the loop itself. Here is the code:
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 4; 
  var numRows = 170; 

  var dataRange = sheet.getRange(startRow, 1, numRows, 8);
  var data = dataRange.getValues();

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

     if (date.getDate() == today.getDate() && 
        date.getMonth() == today.getMonth() && 
          date.getFullYear() == today.getFullYear()) {
            var sub1 = row[2];
            var sub2 = row[3];
            var sub3 = row[4];
            var sub4 = row[5];
            var sub5 = row[6];
            var sub6 = row[7];
            var subject = 'Things you have to study today';
            var message = "You have to study today" + sub1 + ", " + sub2 + ", " + sub3 + ", " + sub4 + ", " + sub5 + ", and " + sub6 + ". All the best." ;
            var emailAddress = 'email goes here';
            MailApp.sendEmail(emailAddress, subject, message);
            break;
            }
    }
}


Thanks for the suggestions.

Upvotes: 1

Michael Pearson
Michael Pearson

Reputation: 96

dateIter references a cell, and therefore does not have a getDate() function.

If the cell contains a string value, you could do something like : 

var date = new Date(row[0]);
if (date == today.getDate()

etc.

Upvotes: 0

Related Questions