I have soma data, starting from A10 to column M, until the 59th row.

I have some dates in column F10:F that are text strings, converted to official dates in column N (here the question with the process)

M3 is set to =NOW().

In cell N3 I have: =M3 14.

I want to delete all the rows, with a date in column N10:N that comes before [today 2 weeks] (so cell N3).

When I create a script in Apps Script, it doesn't run the if statement, but if I leave it in comments, it can go in the for loop and deletes the rows, so I'm pretty sure the problem is, again, date formatting.

In this question I ask: how do I compare the values of N10:N with N3, in order to delete all the rows that don't meet the condition if(datesNcol <= targetDate)? (in code is written as if (rowData[i] < flatArray))

I leave also a demo sheet with this problem explained in detail and two alternatives (getBackground condition and numeric days condition).

Attempts: This is a simplified code example:

const gen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Generatore');
const bVals = gen.getRange('B10:B').getValues();
const bFilt = bVals.filter(String);
const dataLastRow = bFilt.length;

function deleteExpired() {
  dateCorrette(); //ignore, formula that puts corrected dates from N10 to dataLastRow

  var dateCorrect = gen.getRange(10,14,dataLastRow,1).getValues();
  var targetDate = gen.getRange('N3').getValues();
  var flatArray = [].concat.apply([], targetDate);
      
  for (var i = dateCorrect.length - 1; i >= 0; i--) {

    var rowData = dateCorrect[i];

    if (rowData[i] < flatArray) {
      gen.deleteRow(i 10);
    }
  }
};

If run the script, nothing is deleted. If I //comment the if function and the closing bracket, it delets all the rows of the list one by one. I can't manage to meet that condition.

Right now, it logs this [Sun Jan 01 10:33:20 GMT-05:00 2023] as flatArray and this [Wed Dec 21 03:00:00 GMT-05:00 2022] as dateCorrect[49], so the first row to delete, that is the 50th (is correct for all the dateCorrect[i] dates).

I tried putting a getTime() method in the targetDate variable, but it only functions if there is the getValue() method, not getValues(), so I then don't know how to use getTime() method on rowData, which is based on dateCorrected[i], which have to use the getValues() method. And then it also doesn't accept the flatArray variable, that has to be commented out (or it logs [ ] for flatArray, not the corrected date)

I leave the other attempts in the demo sheet, because I want to prioritize this problem around the date and make it clear in my head.

Thanks for all the help.

DEMO SHEET, ITA Locale time

I don't know how the demo sheet works with Apps Script, I suggest to copy the code in a personal sheet

UPDATE:

I've also tried putting an extra column, with an IF built-in function that writes "del" if the function has to be deleted.

=IF(O10>14;"del";"")

And then

var boba = gen.getRange(10,16,bLast,1).getDisplayValues();
.
.
if (boba[i] == 'del')

This does the job. But I can't understand why the other methods don't work.

CodePudding user response：

Try this. It seems like you do a lot of things that aren't necessary. Unless I'm missing something.

A few notes. I typically do not use global variable, unless absolutely necessary. I don't create a variable for last row unless I have to use that value multiple times in my script. I use the method Sheet.getLastRow(). dataCorrect is a 2D array of 1 column so the second index can only be [0]. And getRange('N4') is a single cell so getValue() is good enough.

function deleteExpired() {
  const gen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Generatore');
  var dateCorrect = gen.getRange(10,14,gen.getLastRow()-9,1).getValues();
  var targetDate = gen.getRange('N3').getValue();
      
  for (var i = dateCorrect.length - 1; i >= 0; i--) {
    if (dataCorrect[i][0] < targetDate) {
      gen.deleteRow(i 10);
    }
  }
}

CodePudding user response：

Try this:

function delRows() {
  const ss = SpreadsheetApp.getActive();
  const gsh = ss.getSheetByName('Generatore');
  const colB = gsh.getRange('B10:B'   gsh.getLastRow()).getValues();
  var colN = gsh.getRange('N10:N'   gsh.getLastRow()).getValues();
  var tdv = new Date(new Date().getFullYear(), new Date().getMonth(), new Date().getDate()   14).valueOf();//current date   14
  let d = 0;
  colN.forEach((n, i) => {
    if (new Date(n).valueOf() < tdv) {
      gsh.deleteRow(i   10 - d  );
    }
  });
}