Google Script Out of Range Error when attempting to transfer rows from one sheet to another

Question

I am attempting to create an auto-archiving function that takes the date of a sheet entry, the first column of each row, and compares it to the current date, and moves it to an archive sheet if the current date is n weeks from the entry. The code that I have works on some rows, but on some rows it returns "The coordinates of the source range are outside the dimensions of the sheet." error. Some rows of the spreadsheet have an entry in the last active column, while others are left blank, so I don't know if that could be where the error is originating from, but I tried to run the program without these columns in the range and I still got an error. Any help would be much appreciated! This is my function:

    function compareDates(){
      //change variable depending on how many weeks you want before archived
      var numWeeks = 2;

      //if sheet names are different change them here
      var formDataName = "Sheet1";
      var archiveName = "Archived";

      //gets date 2 weeks from current date
      var date = new Date()-(3600000*24*7*numWeeks);

      //gets sheet that form data is on
      var sheetForm = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(formDataName);
      //gets row & column numbers from form data sheet
      var columnNum = sheetForm.getLastColumn();
      var rowNum = sheetForm.getLastRow();

     //gets sheet that archive is on
     var sheetArchive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(archiveName);

  var rangeForm = sheetForm.getRange(2,1,rowNum,1);

  //iterates through rows
  for(var i = 2; i<=rowNum; i++){
    //gets cell with timestamp
    var cell = rangeForm.getCell(i,1).getValue();
    //compares timestamp to two weeks value
    if(cell.valueOf()<date.valueOf()){
        //gets outdated row
        var range = sheetForm.getRange(i,1,1,columnNum);
        //gets where the row needs to move in archive
        var tar = sheetArchive.getRange(sheetArchive.getLastRow()+1, 1, 1, columnNum);
        //moves row to archive
        range.copyTo(tar);
      //deletes row in form data sheet
      sheetForm.deleteRow(i);
    }
  }
  sheetArchive.sort(1, false);
}

Cooper Cooper · Accepted Answer · 2017-09-23T19:21:55

Try this:

 function compareDates()
 {
   var numWeeks = 2;
   var formDataName = "Sheet1";
   var archiveName = "Archived";
   var date = new Date().valueOf()-(numWeeks*7*24*60*60*1000);
   var sheetForm = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(formDataName);
   var columnNum = sheetForm.getLastColumn();
   var rowNum = sheetForm.getLastRow();
   var sheetArchive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(archiveName);
   var rangeForm = sheetForm.getRange(2,1,rowNum-1,1);
   var vA=rangeForm.getValues();
   for(var i=vA.length-1;i>=0;i--)
   {
     if(new Date(vA[i][0]).valueOf()<date.valueOf())
     {
       var range = sheetForm.getRange(i+2,1,1,columnNum);
       var tar = sheetArchive.getRange(sheetArchive.getLastRow()+1, 1, 1, columnNum);
       range.copyTo(tar);
       sheetForm.deleteRow(i + 2);
     }
   }
   sheetArchive.sort(1, false);
}

Google Script Out of Range Error when attempting to transfer rows from one sheet to another

1 Answers