CODEWITHSUNDEEP
google-apps-script
Michael Elebiju
Michael Elebiju

Reputation: 37

Sending Emails a day before a specified date based on a list of dates

I have a table in google sheets with several columns. I need to send an email reminder a day before dates specified in one of the columns.

My idea of how it should work:

Take date column Loop through each date and subtract 1 day from it to get the reminder date. Filter through by comparing "today's" date with the reminder date Send emails with all rows that meet the filter criteria

I have tried the following:

function StatusAlert() {

const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("On hold procedure");

const lr = ws.getLastRow();
var data = ws.getRange(2, 2, lr, 8).getDisplayValues();

var today = new Date();
var exclusiondate = ws.getRange(2,8,lr).getDisplayValues();

for (var i = 0; i < data.length; i++) {

var alertdate = new Date(exclusiondate[I] - 1)

}

I'm struggling with referencing the date column and then applying the date math to it. Also, I am not quite sure that I needed the second getDisplayValues call (I was trying to reference the date column I need specifically).

Because Even if I manage to apply the change to every row based off of the second getDisplayValues call, I won't be able to go onto the next step of filtering the entire table and sending the relevant rows via email.

So ideally I would like to be able to reference the specific date column from the entire range, and apply date math to it.

Does anyone have any idea how I can best proceed on this?

Regards,

Michael

Upvotes: 0

Views: 197

Answers (2)

Cooper
Cooper

Reputation: 64042

Try this:

function StatusAlert() {
  const ss=SpreadsheetApp.getActive();
  const ws=ss.getSheetByName("On hold procedure");
  var data=ws.getRange(2,2,ws.getLastRow()-1,8).getValues();
  var today=new Date();//add this `valueOf()` if you want to compare with another date value
  var xd=ws.getRange(2,8,ws.getLastRow()-1).getValues();
  for (var i=0;i<data.length;i++) {
    var dt=new Date(xd[i][0]);
    var alertdate=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()-1);//subtract off one day
  }
}

If you want to compare today and alertdate then I would use valueOf() for both of them and then they are just numbers.

Upvotes: 1

Stykes
Stykes

Reputation: 326

I would refer you to this post. I would use getValues instead of getDisplayValues because it returns a more reliable value. For example, if you change the formatting of your date in the spreadsheet, it will break the code.

Lastly, JavaScript is case sensitive do not capitalize "I" in the last line of code.

Upvotes: 0

Related Questions