Check entire column if it contains certain date

[jmm0979]

Hi,

I'd like to change the value in column C to "NEW" if the date is equal to "1/1/1970". However, when I run the script, it does not do anything. I even changed the value of "1/1/1970" to "Thu Jan 01 00:00:00 GMT-08:00 1970" but it's still the same. Can someone please help me with this? I appreciate it! TIA!

function checkAccounts() {

var sourceSheet_sheet = SpreadsheetApp.openById('SHEETID HERE').getSheetByName('SHEET1');

var dataVal = sourceSheet_sheet.getRange(2, 2, sourceSheet_sheet.getLastRow(), sourceSheet_sheet.getLastColumn()).getValues();

for (var i = 0; i < dataVal.length - 1; i++) {

var lastlogin = dataVal[i];

if (lastlogin == "1/1/1970") {

sourceSheet_sheet.getRange(i + 2, 3).setValue('NEW');

}

}

}

[Tanaike]

In your situation, I thought that when getDisplayValues() is used instead of getValues(), you can compare the values with "1/1/1970". But in this case, how about using TextFinder? The modified script is as follows. When TextFinder is used, also "1/1/1970" can be searched.

function checkAccounts() {

  var sourceSheet_sheet = SpreadsheetApp.openById('SHEETID HERE').getSheetByName('SHEET1');

  var dataVal = sourceSheet_sheet.getRange(2, 2, sourceSheet_sheet.getLastRow(), sourceSheet_sheet.getLastColumn()).getValues();

  var rangeList = sourceSheet_sheet.getRange(2, 2, sourceSheet_sheet.getLastRow(), 1).createTextFinder("1/1/1970").findAll().map(r => r.offset(0, 1).getA1Notation());

  if (rangeList.length > 0) {

    sourceSheet_sheet.getRangeList(rangeList).setValue("NEW");

  }

}

[cbmserv...@gmail.com]

The issue is that what is displayed for a Date value is a little different than what the object actually contains. So comparing it to a string will never work this way.

 

If you just want to check against the string “1/1/1970”, then instead of using getValues(), try using getDisplayValues().

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHL69DXSj1Fij45sj%2BUu-f2Wd3LF131PYQm_CSx%3DufuH%2BJVR%3Dg%40mail.gmail.com.

[JMR...]

Thank you both for your help and input. Just changing it to ".getDisplayValues()." did fix it.