As a disclaimer, I am rather new to programming concepts. If you really wanted to be my best friend, the more specific the explanation the better. But any help is appreciated.

My very small tiny business uses QuickBooks for our payroll timekeeping, however for logistical purposes, we have our employees use a google spreadsheet to enter in their times.

I want to write a script that checks the first column for the date twice a day (as per the trigger I have set up). If the date is equal to or greater than today's date, I need the script to also check a cell a few columns down in the same row to see if the cell has been filled out. If it is 0 or null, I want to send a notification.

Please see the picture below for reference.

Test

So far, this is what I have:

function values2() 
{
    var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getSheetByName("TimeSheet"),
      range = sheet.getDataRange(),
      values = range.getValues(),
      indx, logToday, today, body,
      recipients = "[email protected]",
      subject = "Timesheet Notification" + ss.getName();

//Finds current date and creates string. I would prefer to have a numeric value associated to the current date, as well as a numeric value associated with the date for each row.
  var CurrentDate = new Date();
  Logger.log(CurrentDate);
      logToday = Logger.getLog();
      indx = logToday.indexOf("INFO");
      today = logToday.substring(indx + 6, indx + 16);

//I'm having things sent to my email for test purposes
  MailApp.sendEmail(recipients, subject, today);  
//Clears Log
  Logger.clear();
  for (var r=1; r<values.length; r++) 
  {
  var row = values[r],
      date = row[0],
      price = row[1],
      units = row[2],
      total = row[3],
      name = row[4];

   Logger.log(date);  
   Logger.log(price);
   Logger.log(units),
   Logger.log(total),
   Logger.log(name);
  }

  body = Logger.getLog();
  MailApp.sendEmail(recipients, subject, body);
}

At this point, I want to be able to catch each date from the first column, pare it to today's date, check another cell in the same row, then move on to the next date in the first column. I've played around with IF/Else statments, but to no avail.

As a disclaimer, I am rather new to programming concepts. If you really wanted to be my best friend, the more specific the explanation the better. But any help is appreciated.

My very small tiny business uses QuickBooks for our payroll timekeeping, however for logistical purposes, we have our employees use a google spreadsheet to enter in their times.

I want to write a script that checks the first column for the date twice a day (as per the trigger I have set up). If the date is equal to or greater than today's date, I need the script to also check a cell a few columns down in the same row to see if the cell has been filled out. If it is 0 or null, I want to send a notification.

Please see the picture below for reference.

Test

So far, this is what I have:

function values2() 
{
    var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getSheetByName("TimeSheet"),
      range = sheet.getDataRange(),
      values = range.getValues(),
      indx, logToday, today, body,
      recipients = "[email protected]",
      subject = "Timesheet Notification" + ss.getName();

//Finds current date and creates string. I would prefer to have a numeric value associated to the current date, as well as a numeric value associated with the date for each row.
  var CurrentDate = new Date();
  Logger.log(CurrentDate);
      logToday = Logger.getLog();
      indx = logToday.indexOf("INFO");
      today = logToday.substring(indx + 6, indx + 16);

//I'm having things sent to my email for test purposes
  MailApp.sendEmail(recipients, subject, today);  
//Clears Log
  Logger.clear();
  for (var r=1; r<values.length; r++) 
  {
  var row = values[r],
      date = row[0],
      price = row[1],
      units = row[2],
      total = row[3],
      name = row[4];

   Logger.log(date);  
   Logger.log(price);
   Logger.log(units),
   Logger.log(total),
   Logger.log(name);
  }

  body = Logger.getLog();
  MailApp.sendEmail(recipients, subject, body);
}

At this point, I want to be able to catch each date from the first column, pare it to today's date, check another cell in the same row, then move on to the next date in the first column. I've played around with IF/Else statments, but to no avail.

• javascript
• loops
• google-apps-script
• google-sheets

• show us your attempts and what goes wrong – Zig Mandel Commented Nov 11, 2016 at 0:36

1 Answer 1

If you want to pare date elements of first column included empty cells to today, how about following script?

function values2() 
{
    var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getSheetByName("TimeSheet"),
      range = sheet.getDataRange(),
      values = range.getValues(),
      indx, logToday, today, body,
      recipients = "[email protected]",
      subject = "Timesheet Notification" + ss.getName();

    var lR = sheet.getLastRow();
    var dateinfo = sheet.getRange('a1').offset(0, 0, lR, 1).getValues();
    var today = new Date();
    var y0 = today.getFullYear();
    var m0 = today.getMonth() + 1;
    var d0 = today.getDate();

    for (var i=0; i<dateinfo.length; i++){
        x = Date.parse(dateinfo[i]);
        var date = new Date(x);
        var y = date.getFullYear();
        var m = date.getMonth() + 1;
        var d = date.getDate();
        if (y0 === y && m0 === m && d0 === d) {
            Logger.log("ok:" + i) // same date
        } else {
            Logger.log("error:" + i)  // difference date
        };
    };
}