CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Tamjid Taha
Tamjid Taha

Reputation: 171

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

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.

Date Issue

Upvotes: 0

Views: 99

Answers (1)

Marios
Marios

Reputation: 27348

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);
  }
}
}

Related:

Detecting an "invalid date" Date instance in JavaScript

Upvotes: 3

Related Questions