CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
coachpacman
coachpacman

Reputation: 71

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

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

Upvotes: 1

Views: 2911

Answers (1)

Sachin K
Sachin K

Reputation: 394

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
          }
    }
 }

Upvotes: 1

Related Questions