Send email from Google Sheet 2 weeks prior a date

Question

I am new to appscript, I would like to send a email to myself as reminder 2 weeks prior the employee next review. Please see below for the Sheet. https://docs.google.com/spreadsheets/d/1SHglnLMT7enbuubed06uUL8Iy7WGjsEasPZuTT74p8I/edit?usp=sharing

I would like to receive a email 2 weeks prior the employee next review (Column F). I would like to get a email that says the Employee name (Column A) has a review coming up.

Thank you!

I have tried to look a few post, but i couldnt find anything

Answer 1

SOLUTION:

function myFunction() {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var ss = sheet.getActiveSheet(); 

  var range = ss.getRange(2,1,sheet.getLastRow(), 6).getValues(); 
  var currdate = new Date(); 

  range.forEach(x => {

    var datediff = Math.floor(((x[5] - currdate)/3600000)/24)

    if(x[5]!= "" && datediff == 14 ){
      console.log(datediff);
      MailApp.sendEmail("ENTER EMAIL HERE","Review reminder for " + x[0], x[0] + " has a review coming up");
    }
  })

}

What this script does is on the range variable, it gets the data from row 2 column A to column F, and since it returns a 2 dimensional array of those values.

Using the forEach() method, the names are located on index x[0] and the next review date is on the x[5] index and then the condition x[5]!= "" && datediff == 14 evaluates that x[5] should not be blank, also the variable datediff does the computation for the review date minus the current date to comply with the condition set for the if condition.

SAMPLE TABLE:

NOTE: I changed F5 and F6 just to test if it works.

EMAIL OUTPUT:

Lastly, add it as a time-driven trigger so that the script will automatically run depending on the time period you set.