Delete Rows a day after the date has passed-CodePudding

I am still fairly new at script writing and am trying a write a script that deletes a row one day after the date which is in column L. The data is in range A133 to L117. I've gotten the code below from several different [sources][1] but it has several issues. The first being that I get the error message "cannot read property 'getRange' of Null, on line 3 even though I've describe the array I want it to pull data from.

The second issue is with the below code - I'm not sure it's right, and would appreciate any help getting it to work for my current need. Here is the code.

function DeleteOldEntries() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet 1");
var values = sheet.getRange(2,1,5,12);

var currentDate = new Date();//today

for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
if ((tempDate!=NaN) && (tempDate <= currentDate))
{
  sheet.deleteRow(i);
}//closes if
}//closes for loop
}//closes function

Any help would be greatly appreciated.

CodePudding user response：

For your first issue:

The first being that I get the error message "cannot read property 'getRange' of Null, on line 3 even though I've describe the array I want it to pull data from.

This error means your sheet variable is incorrect.

This could mean your sheet name is incorrect, or that there is no active spreadsheet. If you are running this in a script file not attached to a Sheets file, you will need to use SpreadsheetApp.openById() or SpreadsheetApp.openByUrl() in place of SpreadsheetApp.getActive().

On a side note, you are also not retrieving any values in values, rather, just declaring a range.

// var values = sheet.getRange(2,1,5,12);
var values = sheet.getRange(2,1,5,12).getValues();

For your second issue:

Comparing dates is not so cut and dry.

A possible solution to remove all rows that are not from today, would be to declare currentDate as a string with formatting similar to what you are using in your date columns use. As an example, if you're sheet dates look like (May-06 2022):

const values = sheet.getRange(2,1,5,12).getDisplayValues();

const currentDate = Utilities.formatDate(new Date(), `YOUR_TIMEZONE`, `MMM-dd YYY`)

You can then remove all rows in your sheet that's date does not match the current date, using the for loop you wrote:

  for (let i = lastrow; i > 2; i--) {
    if (values[i-1][2] !== currentDate) {
      sheet.deleteRow(i)
    }
  }

If you are looking to remove dates that are specifically 1 day old, you will have to use date comparison, which can be accomplished a few ways.

Here's one solution:

const currentDate = new Date();

for (let i = lastrow; i > 2; i--) {
  if (currentDate.getDate()-1 === values[i-1][2].getDate()) {
    sheet.deleteRow(i)
  }
}

Solutions

Date as String Method:

function DeleteOldEntries() {

  const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet 1");
  const values = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).getDisplayValues();

  const currentDate = Utilities.formatDate(new Date(), "YOUR_TIMEZONE", "m/d/yyyy");

  for (let i = values.length-1; i >= 0; i--) {
    if (values[i][11] !== currentDate) {
      sheet.deleteRow(i 2)
    }
  }

}

Date Comparison: (Strictly one day ago)

function DeleteOldEntries() {

  const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet 1");
  const values = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

  const currentDate = new Date();

  for (let i = values.length; i >= 0; i--) {
    if (currentDate.getDate()-1 === values[i][11].getDate()) {
      sheet.deleteRow(i 2);
    }
  }

}