Delete rows based on the dates present in the adjacent rows

1
votes

I have dates in the fifth column of my sheet.

function deleteOldData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();
  var values = datarange.getValues();

  var currentDate = new Date();
  var daysago = new Date().setDate(currentDate.getDate() - 5);
  daysago = new Date(Date.parse(daysago))
  Logger.log(daysago)


  for (i=lastrow;i>=2;i--) {
var tempdate = sheet.getRange(i, 5).getValue();
if(tempdate < daysago)  {
      sheet.deleteRows(2, i);
      break;
    }
  }
}

I am using this code to delete the rows that have dates lesser than 5 days ago from today. It should delete only those rows that has dates less than 5 days ago from today but it is deleting all the rows expect the headers which i have omitted in the code.

Can you guys please help.

the date in my sheet looks like this:

7/1/2020 18:28:05
1
javascriptgoogle-apps-scriptgoogle-sheets

1 Answers

1
votes

Answer:

You need to use deleteRow() instead of deleteRows().

More Information:

As per the documentation:

deleteRows(rowPosition, howMany)

Deletes a number of rows starting at the given row position.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
// Rows start at "1" - this deletes the first two rows
sheet.deleteRows(1, 2);

So when you call sheet.deleteRows(2, i); you are deleting multiple rows, starting from row 2, and finishing i rows later - which could potentially be as high as lastrow.

Code Modification:

You also do not need to use the break keyword - this is going to stop the whole loop once the if condition is met the first time, and not continue to delete all rows where the day is more than five days ago.

I also cleaned up the function a little:

  • As you already get the data from the sheet and store it in values, you do not need to get the data again for tempdate - you can just assign it straight from the values array.
  • To make the date comparisons more precise, the exact timestamps can be extracted from the date objects rather than working with things like getDate() - 5 etc:
function deleteOldData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();
  var values = datarange.getValues();
  
  var currentDate = new Date().getTime();
  var daysago = (new Date().getTime() - 432000000);
    
  for (var i = lastrow; i > 1; i--) {
    var tempdate = values[i - 1][4];
 
    if((new Date(tempdate).getTime()) < daysago)  {
      sheet.deleteRow(i);
    }
  }
}

I hope this is helpful to you!

References: