0

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.

  • ```rowData[i]``` inside your for loop is equal to ```dateCorrect[i][i]```, but ```dateCorrect``` itself is values in 2D array format contains only 1 column in each row according to ```gen.getRange(10,14,dataLastRow,1).getValues();```, which means... ```if (rowData[i] < flatArray)``` seem to be always testing ```if (undefined < flatArray)``` which... I think it will never be ```true```. – Ping Dec 18 '22 at 17:16
  • Thank you, I've put an [i] and it fact it logs null type. But from this infromation I can't go any further. I've tried another solution that I'm writing inside the question. – user20749937 Dec 18 '22 at 17:39

2 Answers2

0

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);
    }
  }
}
TheWizEd
  • 7,517
  • 2
  • 11
  • 19
0

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++);
    }
  });
}
Cooper
  • 59,616
  • 6
  • 23
  • 54