尝试将行从一个工作表传输到另一个工作表时出现Google脚本超出范围错误

Question

我正在尝试创建一个自动归档函数，该函数获取工作表条目的日期，每行的第一列，并将其与当前日期进行比较，如果当前日期距离条目有n周，则将其移动到归档工作表。我拥有的代码适用于某些行，但在某些行上它返回“源范围的坐标超出了工作表的维度。”错误。电子表格的某些行在最后一个活动列中有一个条目，而其他行则留空，所以我不知道这是否可能是错误的来源，但我尝试在没有这些列的范围内运行程序，我仍然得到了一个错误。非常感谢任何帮助！这是我的功能：

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

Answer 1

尝试以下操作：

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