Pulling Date From Cell in Google Sheet Using Script App

Question

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, compare 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.

show us your attempts and what goes wrong

Zig Mandel
– Zig Mandel

2016-11-11 00:36:07 +00:00
Commented Nov 11, 2016 at 0:36 — Zig Mandel
– Zig Mandel, Commented Nov 11, 2016 at 0:36

Tanaike · Accepted Answer · 2016-11-11 01:07:16Z

4

If you want to compare 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
        };
    };
}

answered Nov 11, 2016 at 1:07

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

1 Comment

KayZak Over a year ago

Thank you. After tons of poking around, this is what I came up with. Sorry I didn't see your post before, K. Tanaike.

Collectives™ on Stack Overflow

Pulling Date From Cell in Google Sheet Using Script App

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related