Code to delete outdated entries on a google sheet

Question

I found this code on here which should work perfectly for me. Was just hoping someone could change the code to delete entries that have dates that are 2 weeks old or older. So if the script were to run today, it would delete any rows that are October 26th or older.

    function DeleteOldEntries() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("MASTER");
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var currentDate = new Date();//today

for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
if ((tempDate!=NaN) && (tempDate <= currentDate))
{
  sheet.deleteRow(i);
}//closes if
}//closes for loop
}//closes function

score 0 · Answer 1 · answered Nov 09 '21 at 23:52

I believe your goal is as follows.

From your script and question, you want to delete the rows when the date of column "C" is before 2 weeks from today.

In this case, how about the following modification? In your script, when the value of column "C" is the date object, you are comparing the date object.

From:

var currentDate = new Date();//today

for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
if ((tempDate!=NaN) && (tempDate <= currentDate))
{
  sheet.deleteRow(i);
}//closes if
}//closes for loop
}//closes function

To:

  var currentDate = new Date();

  currentDate.setDate(currentDate.getDate() - 14); // Added: This means before 2 weeks from today.
  var d = currentDate.getTime(); // Added
  for (i = lastrow; i >= 3; i--) {
    var tempDate = values[i - 1][2];
    if ((tempDate != NaN) && (tempDate.getTime() <= d)) { // Modified
      sheet.deleteRow(i);
    }
  }
}

References:

score 0 · Accepted Answer · answered Nov 10 '21 at 00:01

Deleting Rows in a forEach loop

function DeleteOldEntries() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("MASTER");
  const sr = 3;//guessing data start on row 3
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).getValues();
  let d = 0;//delete counter
  const dtv = new Date(new Date().getFullYear(), new Date().getMonth(), new Date().getDate() - 15).valueOf();
  vs.forEach((r, i) => {
    let cdt = new Date(r[2]);//assume date is in column 3
    let cdtv = new Date(cdt.getFullYear(), cdt.getMonth(), cdt.getDate()).valueOf();
    if (cdtv < dtv) {
      sh.deleRow(i + sr - d++);
    }
  });
}

Date.valueOf()

Code to delete outdated entries on a google sheet

2 Answers2

From:

To:

References:

Deleting Rows in a forEach loop