Deleting row from google sheets that are older than 30 days but date column is plain text format

Question

I have a Google Sheet that has entries with one of the Columns having a date when it's entered. I want the code to check if a date in Column E is older than 30 days and delete the row.

However, I have column E specifically formatted in plain text under the sheet options. The reason for doing so is I have a different script pull the data from the sheets as JSON and setting the column to plain text makes it show up as a string how I wanted in my JSON.

My code works if I format the column "E" in a date format.

Data is currently added as so "May 11th, 2021" whereas the closest date format in sheets is "May 11, 2021" without the "th" or "rd" after the dates but I would like to keep it how I have it if possible.

The code below works if Column E is formatted in date format but is there a way to get it to work as plain text format option which I currently have it set to?

Made a dummy Google Sheet for visual: https://docs.google.com/spreadsheets/d/1_156bLL03lFo9NdjE6KmrGiFJvYXkvReel_9znMwT4M/edit?usp=sharing

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("sheet1"); //assumes Sheet 1 is the name of the sheet
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var monthOld = new Date()
monthOld = new Date(monthOld.getTime()-30*3600000*24) //Set date 30 days in past from today
Logger.log(monthOld) // confirm I am getting date 30 days ago
for (i=lastrow;i>=2;i--) {
var tempDate = values[i-1][4];// arrays are 0 indexed so row2 = values[1] and colE = [4]
Logger.log(tempDate)
if (tempDate <= monthOld)
{
  sheet.deleteRow(i);
  Logger.log(`Row ${i} was deleted`);
} else {
  Logger.log(`Nothing was deleted`);
}
}
}

Answer 1

Try

var tempDate = new Date(values[i-1][4].replace(/(th|rd|st|nd)/gm,""));

Answer 2

Using the testbelow function as an intermediate function to pass the appropriate arguments to isOlderThan(). You pass the datestring in ds and the number of days in days. isOlderThan returns true or false based upon todays date.

function testbelow() {
  isOlderThan({ds:"Jul 30th, 2021",days:30})
}

function isOlderThan(obj) {
  const dA = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"]
  const dt = new Date();
  const dtv = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate() - obj.days).valueOf();
  let t = obj.ds.split(" ");
  let idtv = new Date(t[2],dA.indexOf(t[0]),parseInt(t[1])).valueOf();
  Logger.log(idtv < dtv);
  return idtv < dtv
}

Answer 3

Mike Steelson provided the line of code I needed to convert the plain text by parsing out the values that didn't apply and converting it to a date.

var tempDate = new Date(values[i-1][4].replace(/(th|rd|st|nd)/gm,""));