Script To Archive Sheet entries if older than 30 days-CodePudding

I am trying to write a script for sheets that will move all resolved support tickets older than 30 days to the archive sheet. It'll then delete the entries from the current support sheet. If the ticket does not have a completion date it should be skipped. Currently the script is not moving the all the correct cells. Sometimes it moves things that are newer than 30 days and other times it moves things that have null entries. Thanks in advance.

function moveAfterThirty() {
  const sh = SpreadsheetApp.getActive();
  const ss = sh.getSheetByName("Current Support Requests");  //requests
  const dest = sh.getSheetByName("Archived Support Requests"); //destination
  
  let data = ss.getDataRange().getValues();  //all values
  let timeRange = getFullColumn('C', 2).getValues(); //date values for the entire Requests Sheet
  var length = timeRange.length;

  var date = new Date(); //new date
  date.setDate(date.getDate() - 30);    //setting date to 30 days ago
  
  for (let i = 0; i < timeRange.length; i  ){
    var d1 = new Date(timeRange[i]); //setting d1 to the date that corresponds with the position in the array
    if (d1 < date)
    {       
      ss.getRange(i 1,1,1,14).moveTo(dest.getRange(dest.getLastRow() 1,1,1,14)); //goes to the row and moves all information
      ss.deleteRow(i 1); //deletes row
    }
    else //if no date of completion do nothing
    {
      
    }
  }
}

function getFullColumn(column, startIndex){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sheet.getSheetByName("Current Support Requests");
  var lastRow = ss.getLastRow();  
  return sheet.getRange(column startIndex ':' column lastRow)
}

I am expected it to purge all the entries that have been completed more than 30 days ago. So far it is giving me a hodgepodge of some things moved and other things not.

CodePudding user response：

As a guess. Try to change this line:

if (d1 < date)

with:

if (d1.getTime() < date.getTime())

CodePudding user response：

Move after 30 days

function moveAfterThirty() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const dsh = ss.getSheetByName("Sheet1");
  const dtv = new Date(new Date().getFullYear(), new Date().getMonth(), new Date().getDate() - 30).valueOf();
  let vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  var a = [];
  var d = 0;
  vs.forEach((r, i) => {
    let dv = new Date(r[2]).valueOf();
    if (dv < dtv) {
      a.push(r);
      sh.deleteRow(i   2 - d  )
    }
  });
  dsh.getRange(dsh.getLastRow()   1, 1, a.length, a[0].length).setValues(a);
}