CODEWITHSUNDEEP
javascriptgoogle-apps-script
osherez
osherez

Reputation: 99

Google App Script function for dates

I have a script that sends an email notification when a certain rule has resulted true/false.

Phase 1 : In my example I'm looking to send an Email 2 days before a "Task" is going to end

Phase 2 : I want to make an event In calendar for that 'task' as soon as it reaches that time frame as well. ( here I don't know what to do)

Iv managed to make this code, with the help of some tuts online, that sends the email to me, but this was accomplished with using numbers and I'm looking to use DATES so the difference between dates will give the result. any Idea how can I do this ?

//Script that sends mail to your Gmail if certain amount is greater or less 
//than the amount you specify

function myFunction() {
  
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet().getActiveSheet();
  var cell = ss.getRange(2, 1).getValue(); // getRange - Change to your cell
  var cell2 = ss.getRange(2, 2).getValue();
  
  if(cell2-cell< 2){
   
    MailApp.sendEmail("[email protected]", "Hooray", "2 Days left bro")
    
  } 
  
  
}

google sheet - SpreadSheet

Upvotes: 0

Views: 87

Answers (1)

Ritesh Nair
Ritesh Nair

Reputation: 3355

Try the below script...

function dateDiff() { 
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet().getActiveSheet();
  var data = ss.getDataRange().getValues();        // getRange - Change to your cell

  for(var i=1;i<data.length;i++){ //start from 1, in order to skip the header
     var cell1 =  data[i][0];
     var cell2 =  data[i][1];
     cell1.setHours(0,0,0,0);
     cell2.setHours(0,0,0,0);

     var timeDiff = Math.abs(cell2.getTime() - cell1.getTime());
     var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24)); 

     if(diffDays<=2){
         MailApp.sendEmail("[email protected]", "Hooray", "2 Days left bro")
      } 
   }
}

Upvotes: 1

Related Questions