How to delete all rows of a Google sheet if their corresponding column1 value is NOT a valid date?

Question

I would like to delete all rows of a Google sheet if their corresponding column1 value is NOT a valid date. Not sure how to fix this issue. Please check the attached image where I want to delete each of the three rows as their corresponding column1 value is not a Valid Date.

Please note that there are other rows of data with valid date in the format of YYYY-MM-DD at a Google sheets.

A very quick and dirty way is to create a new column and put the formula `=isDate()`. Then write a script to delete the rows for which the cells of this column are False. You can do the full process in a Google script. But the first one is easier I think. — Marios, Oct 16 '20 at 20:07
@Marios Not possible here to create a new column as there are triggers and other things associated. Google script is better, but how? — Tamjid Taha, Oct 16 '20 at 20:11
In your situation, the values of the column "A" are the date object? — Tanaike, Oct 16 '20 at 23:40

Marios · Accepted Answer · 2020-10-17T01:01:42.163

Explanation:

In your screenshot, 2238-01-03 is a valid date. In order to consider it as invalid you need to set up some manual rules. Since the column name is timestamp, I assume that all the future dates (tomorrow or later dates) are invalid dates.
Therefore, the following script will remove a row if the corresponding value in column A is either a valid future date or an invalid date ( e.g. hi).
The if condition to achieve that is:

dtObj>today || (!(dtVals[i] instanceof Date) && isNaN(dtVals[i]))

Solution:

function invalidDates() {
  
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('TempDataSet');
  const sz=sh.getRange('A:A').getValues().filter(String).length;
  const dtVals = sh.getRange('A2:A'+sz).getValues().flat();  
  const today = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), "yyyy-MM-dd");

for (let i = dtVals.length - 1; i >= 0; i--){
var dtObj = Utilities.formatDate(new Date(dtVals[i]), ss.getSpreadsheetTimeZone(), "yyyy-MM-dd");
  if (  dtObj>today || (!(dtVals[i] instanceof Date) && isNaN(dtVals[i])) ) {
    sh.deleteRow(i+2);
  }
}
}

How to delete all rows of a Google sheet if their corresponding column1 value is NOT a valid date?

1 Answers1

Explanation:

Solution:

Related: