CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user14566169
user14566169

Reputation:

How to move row that is older than this date

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++);
    }
  }              
}

Upvotes: 0

Views: 205

Answers (2)

Marios
Marios

Reputation: 27348

Explanation:

There are two things I would like to mention.

  1. 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.

  2. 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);
  }
  
}

Upvotes: 1

NightEye
NightEye

Reputation: 11184

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

Upvotes: 2

Related Questions