How to move row that is older than this date

Question

I have a script that is already works which moves the rows to another sheet if it's older than 90 days from today. but what I'm trying to work is to move the row if the date is older than previous month of every 20th.

E.g. If today is March 20th, then i want to move the rows that is older than January 20th.

I tried playing around with number but it didn't work for me. can i get a support here?

function moveRows90DaysOld() {
  var v=new Date().valueOf()-(1000 * 60 * 60 * 24 * 90);
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('MAIN');
  var tsh=ss.getSheetByName('Archive');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var d=0;
  for(var i=2;i<vA.length;i++) {
    if(new Date(vA[i][0]).valueOf()<v) {
      tsh.appendRow(vA[i]);
      sh.deleteRow(i+1-d++);
    }
  }              
}

score 2 · Answer 1 · answered Jan 05 '21 at 15:22

2

Simplest way to get the date 2 months earlier is subtracting 2 from getMonth using setMonth. Use setDate for the day as well.

var v = new Date();
v.setMonth(v.getMonth()-2); // 2 months earlier
v.setDate(20);              // 20th day

answered Jan 05 '21 at 15:22

NightEye

10,634
2
5
24

2

I was typing my answer when I saw yours. :) – Marios Jan 05 '21 at 15:28

score 1 · Accepted Answer · answered Jan 05 '21 at 15:24

Explanation:

There are two things I would like to mention.

You can get the 20th of the previous month like that:
```
var v = new Date(); // get today
v.setDate(20); // set the date to 20th
v.setMonth(v.getMonth() - 1); // set to the previous month (change to 2 if you want)
```
if you want to get the same day of the previous month as of today, then remove that line v.setDate(20) and set only the month.
Please read the Best Practices and don't use appendRow in a for loop. Store the values in an array and then set the values in one go.

Solution:

function moveRows() {
  // modified code
  var v = new Date(); // get today
  v.setDate(20); // set the date to 20th
  v.setMonth(v.getMonth() - 1); // set to the previous month
  
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('MAIN');
  var tsh=ss.getSheetByName('Archive');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var d=0;
  var data = [];
  for(var i=2;i<vA.length;i++) {
    if(new Date(vA[i][0]).valueOf()<v.valueOf()) {
      data.push(vA[i]); // modified code
      sh.deleteRow(i+1-d++);
    }   
  }
  
  // modified code
  if(data.length>0){
  tsh.getRange(tsh.getLastRow()+1,1,data.length,data[0].length).setValues(data);
  }
  
}

How to move row that is older than this date

2 Answers2

Explanation:

Solution: