Google Apps Script - deleting a row if today's date is past input date

Question

I'd like to make a script that compares the date within a row to today's date and delete that row if today's date is paste the date row.

This is my current script:

    function deleteRow1() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 1;  // First row of data to process
var numRows = sheet.getLastRow()-1;   // Number of rows to process
var dataRange = sheet.getRange(startRow, 2, numRows);
// Fetch values for each row in the Range.
var data = dataRange.getValues();

for (i=0;i<data.length;i++) {
  var row = data[i];
  var date = new Date();
  var sheetDate = new Date(row);
 var Sdate = Utilities.formatDate(date,'GMT+0200','yyyy:MM:dd')
 var SsheetDate = Utilities.formatDate(sheetDate,'GMT+0200', 'yyyy:MM:dd')
        if (Sdate > SsheetDate){
          sheet.deleteRow(i+2) //don't delete header
}
}
}

The ultimate goal is to compare dates in column C with today's date and to delete that particular row if today's date is greater.

Right now when I run the script, it deletes some rows, but not specifically the rows that are earlier than today's date.

The beginnings of this script was based off another StackOverflow post

score 1 · Accepted Answer · answered Apr 01 '15 at 14:41

when you start iteration from top to bottom deleting row in iteration alter the offset (actual row number) of next rows

above problem can be solved using start the iteration from bottom to top.

for (i=data.length-1;i>=0;i--)

here is the working code

function deleteRow1() 
{
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2;  // First row of data to process
    var numRows = sheet.getLastRow()-1;   // Number of rows to process
    var dataRange = sheet.getRange(startRow, 2, numRows);
    // Fetch values for each row in the Range.
    var data = dataRange.getValues();

    for (i=data.length-1;i>=0;i--) 
    {
          var row = data[i];
          var date = new Date();
          var sheetDate = new Date(row);
          var Sdate = Utilities.formatDate(date,'GMT+0200','yyyy:MM:dd')
          var SsheetDate = Utilities.formatDate(sheetDate,'GMT+0200', 'yyyy:MM:dd')
          if (Sdate.valueOf() > SsheetDate.valueOf())
          {
                sheet.deleteRow(i+2) //don't delete header
          }
    }
 }

Thanks, Sachin K! Your suggestion works for deleting rows, but when I add dates that are greater than today's date, the script will still delete rows. It seems like it isn't comparing the row date with today's date. Any ideas? — coachpacman, Apr 01 '15 at 15:54
i tried in spreadsheet which you given in question (refered link) https://docs.google.com/spreadsheets/d/1oSggeHpwcX3sNt0Yx1Vt8cPCI0RqKIZ-a4p0_JV5p9I/edit and it works. only thing is it is in mm/dd/yyyy rather than dd/mm/yyyy — Sachin K, Apr 01 '15 at 16:14

Google Apps Script - deleting a row if today's date is past input date

1 Answers1