CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
kiik
kiik

Reputation: 3

Google Script Code will run but wont execute if statement

Having an issue with this code, running on google scripts. It runs fine without any errors but will not mark the column with the overdue text. It is comparing two dates, in the end I want it to check the current date then if the inv_date is exactly 7 days before execute the if statement. Any help would be appreciated.




function onOpen() 
{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Invoice')
      .addItem('Mark Overdue', 'doOverdueCheck')
      .addToUi();
}

//does a check and inputs overdue if past date

{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Invoice')
      .addItem('Mark Overdue', 'doOverdueCheck')
      .addToUi();
}


function doOverdueCheck()
{
    var sheet = SpreadsheetApp.getActiveSheet();
    var data_range = sheet.getDataRange();
    var last_row = data_range.getLastRow();
    sheet.getRange('E:E').clearContent();
    var today = new Date();
    today.setHours(0, 0, 0, 0);
    today.getTime()-7*(24*3600*1000);
    for (var r = 2; r <= last_row; r++) {
        var inv_date = data_range.getCell(r, 4).getValue();
        if (today.getTime() == inv_date.getTime()) {
            sheet.getRange(r, 5).setValue("overdue");
        }
    }
}

function getOverDueInfo(row)
{
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');

  var values = sheet.getRange(row,1,row,6).getValues();
  var rec = values[0];

  var overdue = 
      {
        first_name:rec[0],
        last_name:rec[1],
        email:rec[2],
        due_date:rec[3],
        module_test:rec[5],

      };

   overdue.name = overdue.first_name +' '+ overdue.last_name;
   overdue.date_str = sheet.getRange(row,4).getDisplayValue();
   overdue.module = overdue.module_test;

   var due_date = new Date(overdue.due_date);
   due_date.setHours(0,0,0,0);
   var today = new Date();
   today.setHours(0,0,0,0);

   var difference_ms = Math.abs(today.getTime() - due_date.getTime() );

   overdue.num_days = Math.round(difference_ms/(24*60*60*1000) );

   return overdue;
}



//Send email Function

function sendEmail(row)
{
  var overdue = getOverDueInfo(row);

  var templ = HtmlService
      .createTemplateFromFile('client-email');

  templ.overdue = overdue;

  var message = templ.evaluate().getContent();

  MailApp.sendEmail({
    to: overdue.email,
    subject: "Your Trial Has Ended",
    htmlBody: message
  });

}

//End function

//Start send Overdue emails

function sendOverdueEmails()
{

  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');

  var data_range = sheet.getDataRange();
  var last_row = data_range.getLastRow();
  var today= new Date();
  today.setHours(0,0,0,0);

  for(var r=2;r<=last_row;r++)
  {
    var inv_date = data_range.getCell(r,4).getValue();
    inv_date.setHours(0,0,0,0);
    if(today > inv_date)
    {
      sendEmail(r);
    }
  }

}

//End send overdue emails

//Function onOpen

{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Invoice')
      .addItem('mark Overdue', 'doOverdueCheck')
      .addItem('send Emails', 'sendOverdueEmails')
      .addToUi();
}

//End onOpen

Upvotes: 0

Views: 99

Answers (2)

ale13
ale13

Reputation: 6062

When you input a date in the sheet, for example 05/05/2020, this is how the date actually looks like:

Tue May 05 2020 00:00:00 GMT+0200 (Central European Summer Time)

When you create a date and assign it the value of today, it will give you the current time, hence your instruction will never be executed, unless you execute the script at the 00:00:00 hour of the current day.

In order for the script to work, you can also use a simple instruction instead of the library and also add the setHours(0, 0, 0, 0) to the today variable before subtracting the days.

Also, since you are assigning to today the value of the date from 7 days before, you can also recheck your if condition in order for the overdue value to appear where needed.

function doOverdueCheck() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var data_range = sheet.getDataRange();
    var last_row = data_range.getLastRow();
    sheet.getRange('E:E').clearContent();
    var today = new Date();
    today.setHours(0, 0, 0, 0);
    today.getTime()-7*(24*3600*1000);
    for (var r = 2; r <= last_row; r++) {
        var inv_date = data_range.getCell(r, 4).getValue();
        if (today.getTime() == inv_date.getTime()) {
            sheet.getRange(r, 5).setValue("overdue");
        }
    }
}

Reference

Upvotes: 0

Stykes
Stykes

Reputation: 326

See comments below post about syntax errors.

The if statement is checking to see if it is 7 days late to the exact millisecond. inv_date.setHours(0,0,0,0) zeros out time for inv_date but the same is not true of today So as soon as that millisecond passes the statement will no longer be true.

Upvotes: 1

Related Questions