CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsdelete-row
Quintin Sheppard
Quintin Sheppard

Reputation: 23

Delete row if column has date older than one month

I found this code from https://stackoverflow.com/users/6656050/jeremy-kahan and edited it a little. I need my sheet to delete any row when the entry in column L is older than one month, but keep the row if the cell in column L is empty i'll need some guidance. On request I can provide a copy of my sheet and show examples. It might seem easy it others but I feel at a disadvantage only being 16 and just learning java.

function DeleteOldEntries() {
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 currentDate = new Date();//today
var monthOld = Date.now() + -30*24*3600*1000; 
for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][11];// arrays are 0 indexed so row1 = values[0] and col12 = [11]
if  (tempDate <= (monthOld))
{
  sheet.deleteRow(i);
}

}

Upvotes: 1

Views: 2037

Answers (2)

Quintin Sheppard
Quintin Sheppard

Reputation: 23

function DeleteOldEntries() {
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 currentDate = new Date();//today
var monthOld = Date.now() + -30*24*3600*1000; //to change amount of days adjust "30"
for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][11];// arrays are 0 indexed so row1 = values[0] and col12 = [11]
if ((tempDate!="") && (tempDate <= (monthOld)))
{
  sheet.deleteRow(i);
}
}
}

Upvotes: 0

Ron Kloberdanz
Ron Kloberdanz

Reputation: 442

I'm going to provide some help, even though I'm going to get chastised for it. In the future, do some research, try to understand the code and make the code work for your purpose. If you can't, and Google doesn't help, THEN come on and ask a CLEAR question with all of the details. Most of the top dogs on here won't even look at your question if all of that isn't done. Also, be sure to use the correct tags on your questions. It won't even show up to the correct people without them.

Now to your answer:

You want to delete a row if the date in column L is > 30 days in the past. The code you provided is looking at column C for the date. So that is the first thing that needs to be changed. Second, the conditional if ((tempDate!=NaN) && (tempDate <= currentDate)) is checking to see if the cell is empty (NaN), or if the date is <= TODAY, NOT today - 30. So, you need a way to calculate what today - 30 days is: var monthOld = Date.now() + -30*24*3600*1000;, then you can compare that to the date in column L.

If you make those two changes, you get: 

function DeleteOldEntries() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Live Events");//assumes Live Events 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 currentDate = new Date();//today
var monthOld = Date.now() + -30*24*3600*1000; 
for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][11];// arrays are 0 indexed so row1 = 
values[0] and col12 = [11]
if ((tempDate!=NaN) && (tempDate <= (monthOld)))
{
  sheet.deleteRow(i);
}
}
}

This should do what you want. If you have any further questions, let me know. I'm happy to help.

Upvotes: 3

Related Questions