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
CodePudding user response:
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:
CodePudding user response:
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 );
}
});
}