CODEWITHSUNDEEP
javascriptgoogle-sheets
user7289068
user7289068

Reputation:

Google Sheets - Delete Expired Rows Based On Date

I'm currently trying to make a script or literally anything that will be able to delete a row after the given date in Column C.

The site is a giveaway site so I need the rows/entries to delete themselves once the date specified on Column C is passed.

Eg: If one giveaway had an expiration date @ 20/13/2016, once the date reaches this date of 20/13/2016 it will delete the row. I am following the metric system of dd/mm/yy as a note.

I saw a question similar to this at Google Sheets - Script to delete date expired rows but the code won't work for my needs.

Here is the code that was used in the other question.

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

var currentDate = new Date();
var oneweekago = new Date();
oneweekago.setDate(currentDate.getDate() - 7);

for (i=lastrow;i>=2;i--) {
var tempdate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]

if(tempdate < oneweekago)  
{
  sheet.deleteRow(i);
}
}
}

If you could change it to work for my above needs it will be greatly appreciated!

Upvotes: 0

Views: 5946

Answers (1)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

Assuming your dates are in column C as stated, this should do it. The adjustment is just to the date to which we compare and to handle missing dates. I am also messing with the case on some names for readability.

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

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

Upvotes: 2

Related Questions