Google Script Code will run but wont execute if statement

Question

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

Just glancing over - are you aware that the first getTime has no ()? This can be correct, but can also be a mistake. — Koenigsberg
– Koenigsberg, Commented May 12, 2020 at 18:48
@Mär If i add () to the first getTime it gives a "today.getTime is not a function" and if I remove () from the second one it completes but doesnt mark overview in the cell — kiik
– kiik, Commented May 12, 2020 at 18:59
moment is undefined. So is moment(). today is probably undefined as well. Try running in the debugger one step at a time and viewing the variables carefully. — Cooper
– Cooper, Commented May 12, 2020 at 19:03
@kiik as said, can be correct. Presumably it is a property then. Do you see the same issues with ==? All in all, as a general rule, if an if request based on comparison fails I'd try to read the comparison parameters. — Koenigsberg
– Koenigsberg, Commented May 12, 2020 at 19:09

Stykes · Accepted Answer · 2020-05-12 19:29:43Z

1

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.

answered May 12, 2020 at 19:29

Stykes

3441 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ale13 · Accepted Answer · 2020-05-13 18:15:16Z

0

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

JDBC Service Apps Script

edited May 13, 2020 at 18:15

answered May 13, 2020 at 11:27

ale13

6,1423 gold badges13 silver badges26 bronze badges

4 Comments

kiik Over a year ago

Thank you!! When I copy the code across it pastes overdue in cells with 7 days and under I just need it to paste overdue in cells that are exactly 7 days previous. if i use == or === instead of >= in the if statement it will run fine but not paste overdue in any of the cells.

ale13 Over a year ago

I understand now, then == is indeed the best option in this situation. Are any of the dates overdue actually? Can you debug by using console.log()? The code works for me and it will set the value of the cells needed to 'overdue'.

kiik Over a year ago

when I use that code with the == or === it does not mark any cells that have the relevant date as overdue leaves all blank. it only works when i use the < or > to determine days before or after but i need exact. Really strange......

ale13 Over a year ago

@kiik, if the dates coincide then the overdue will appear. Have you tried using console.log() to test this behavior?

Collectives™ on Stack Overflow

Google Script Code will run but wont execute if statement

2 Answers 2

Comments

Reference

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Reference

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related