Check entire column if it contains certain date

28 views
Skip to first unread message

jmm0979

unread,
Sep 20, 2021, 8:24:17 PM9/20/21
to google-apps-sc...@googlegroups.com
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');
}
}
}


Screen Shot 2021-09-20 at 5.18.48 PM.png

Tanaike

unread,
Sep 20, 2021, 10:21:07 PM9/20/21
to Google Apps Script Community
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

unread,
Sep 21, 2021, 12:15:09 AM9/21/21
to google-apps-sc...@googlegroups.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.

image001.png

JMR...

unread,
Sep 28, 2021, 4:33:21 PM9/28/21
to Google Apps Script Community
Thank you both for your help and input. Just changing it to ".getDisplayValues()." did fix it.
Reply all
Reply to author
Forward
0 new messages