CODEWITHSUNDEEP
validationgoogle-apps-script
Greg
Greg

Reputation: 1364

Date Validation with If/Then Function in Google Apps Script

Thanks already to Serge insas for his insight both here and here, which have been a godsend for me already. But...I'm having trouble tying everything together with date validation.

To clarify, I have a GAS intended to verify that the date in Column A is (a) more than seven days old and (b) not null. If both pass, the script determines the first empty row in Column G, and then pauses before completing various functions. The beginning of the script looks like...

function getStats() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName("Main");
var TITLE_ROW = 1;
var DATE_COL = 1;
var URL_COL = 4;
var sevendaysBefore = new Date(new Date().getTime()-7*24*60*60*1000);
if (DATE_COL != ''||(DATE_COL != null || DATE_COL< sevendaysBefore)) {
var end = sheet.getLastRow();
for( var i = 1; i < end; i++) {
var Gvals = sheet.getRange("G1:G").getValues();
var Glast = Gvals.filter(String).length;
var rowNum = TITLE_ROW+Glast;
var itemurl = sheet.getRange(rowNum,URL_COL).getValues();
Utilities.sleep(500);

...

I've clearly implemented something wrong, though, because the date validation doesn't work—the script appears to function as though the data in Column A doesn't matter. I'm sure I've done something incredibly idiotic, but I'm too ignorant to spot it on my own. So...anyone know what I've overlooked?

Upvotes: 1

Views: 7368

Answers (2)

Serge insas
Serge insas

Reputation: 46802

While the other answer is probably working (didn't test), its approach is very different from yours.

Below is code that follows the same logic as yours but works at the array level (to follow recommendations in Best practices).

I added a few comments to show the differences, hoping it will help you to understand how it works.

function getStats() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("Main");
  var Glast; // define the variable for later use
  var vals = sheet.getDataRange().getValues();// get all data in an array (do that before loop)
  var TITLE_ROW = 0;// use array index instead of real row numbers
  var DATE_COL = 0;// use array index instead of real column numbers
  var URL_COL = 3;// use array index instead of real column numbers
  var sevendaysBefore = new Date(new Date().getTime()-7*24*60*60*1000).getTime();// get native value in milliseconds to make comparison easier below
  for( var i = 1; i < vals.length; i++) { // start loop from Row 2 (=array index 1)
    if(vals[i][0]!='' && vals[i][0]!=null&&vals[i][0].getTime()<sevendaysBefore){continue};// use && instead of ||, we want ALL conditions to be true ( see !='' and !=null) 
    Glast = i; break ;// first occurrence of data meeting above condition (non null and date < 7 days before)
  } 
  var itemurl = vals[Glast][URL_COL];// get the value from the array
  Utilities.sleep(500);
  //...

Upvotes: 1

Joachin Joseph
Joachin Joseph

Reputation: 343

Mistake : You are hard coding DATE_COL = 1 and you are using this in if statement. It doesn't get the value of the cell. Also I am not getting your statement "date in Column A is (a) more than seven days old". Is that date is from a cell or you are iterating through all the cells in column A ?.

Below code will satisfy your need and I tested. Here as example I am checking date validation for cell R1C1(A1).

1)Get the date from cell. You can change it or Iterate the cells in column for date.
2) We have date.valueOf() method which returns the number of milliseconds since midnight 1970-01-01.
3) Validation : check the cell data is date and greater than 7 days

function compDate()
{

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getRange("A1"); //point1
  var date01 = new Date();
  var date02 = cell.getValue(); //point2
  var dateDiff = (date01.valueOf()-date02.valueOf())/(24*60*60*1000);

  if((isValidDate(date02)) == true && dateDiff > 7) //point3
  Logger.log("success");
}

//below function will return true if the arg is valid date and false if not.
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}

Upvotes: 1

Related Questions