Google-sheets – How to delete a row in a Google Sheet based on date

google sheets

I have a Google Sheet I'm using to geocode addresses, which shows where current road closures are located. In my table, I have a date field "StartDate" and a number field "Duration". "Duration" equals the number of days the road will remain closed after the "StartDate". I need a way to automatically delete the row when the duration of the closure has elapsed (i.e., StartDate plus Duration = new date when row will be automatically deleted). Is there an easy way to accomplish this delete? I am not a programmer, so any help would be charitable.

Best Answer

Directly accessing date cells in a spreadsheet from a script is kind of painful. I would add a helper column, for example C2 =value(A2+B2) if the columns A and B contain StartDate and Duration. This makes the entry in C an integer. Then a script can delete rows based on this value. Like this

function cleanup() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var values = sheet.getDataRange().getValues();
  var today = Date.now()/86400000 + 25569;
  for (var i = values.length - 1; i >= 0;  i--) {
    if (values[i][2] - today > 1) {
      sheet.deleteRow(i+1);
    }
  }  
}

Explanation: Date.now()/86400000 + 25569 is conversion from JavaScript timestamp to spreadsheet format. The loop traverses the sheet from bottom up, so that deletion of rows does not mess with enumeration of remaining rows. The comparison values[i][2] - today > 1 says "value in column C" is greater than today + 1, where +1 is a cushion meant to account for time zone discrepancies. (I presume you can afford to keep an expired task in the spreadsheet for a few hours.) If the column was E, you'd need values[i][4] there: enumeration begins with 0 in JavaScript.

One you have this function there, set a trigger (under Resources) to run it daily, or hourly if you prefer.

Related Topic