1

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.

1

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.

  • I'm surely going to try this – LOSTinNEWYORK Oct 9 '15 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.