在Google sheet archiving宏中获取工作表的最后一行时出错

Question

我有一个在Google工作表后台运行的宏脚本，当“公式”选项卡中的行数超过10行时，它会将包含公式的选项卡存档（复制和粘贴值）到不包含公式的新存档选项卡。这使得工作表不会随着所有内置公式的行数增加而减慢。

宏几天前坏了，什么都没变，除了我在脚本编辑器中看到的这个新错误。

下面是返回我们正在存档的工作表的最后一行（带公式）的代码。错误信息告诉我这部分代码坏了。

var Direction=SpreadsheetApp.Direction;
var DailySLast =dailySheet.getRange("A"+(dailySheet.getLastRow()+1)).getNextDataCell(Direction.UP).getRow();

下面是指向的错误消息。getNextDataCell部分

Exception: Invalid argument
myFunction  @ Code.gs:8

知道为什么会这样吗？

下面是整个功能

function myFunction() {
   // Get handles to Daily and Archive sheets
  var dailySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Scorecard');
  var appendSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('[Archive] Scorecard');

  //get last row of the daily sheet
  var Direction=SpreadsheetApp.Direction;
  var DailySLast =dailySheet.getRange("A"+(dailySheet.getLastRow()+1)).getNextDataCell(Direction.UP).getRow();

  //Range of cells to be copied
  var CopyRange = "Scorecard!9:" + DailySLast;

  //Range of cells to be deleted
  var DelRange = "Scorecard!9:" + 2000;

  //get last row of the archive sheet
  var ArchiveSLast =appendSheet.getRange("A"+(appendSheet.getLastRow()+1)).getNextDataCell(Direction.UP).getRow();

  //copying destination range
  var destrange1 = "[Archive] Scorecard!" + (ArchiveSLast + 1) + ":" + (ArchiveSLast + DailySLast-8);
  var destrange=  dailySheet.getRange(destrange1);

  // Clear the contents only if Underwriting sheet has more than 10 rows of data

  if(DailySLast>10) {

  //copying the data
  var sourceDataValues = dailySheet.getRange(CopyRange).copyTo(destrange,SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  // Get the formulas from the sheet
  var formulas =  dailySheet.getRange(DelRange).getFormulas();

  // Delete the data from the daily sheet
  dailySheet.getRange(DelRange).clearContent();

  //Put the formulas back in the sheet
  dailySheet.getRange(DelRange).setFormulas(formulas)};

  //Browser.msgBox(DailySLast, Browser.Buttons.OK_CANCEL);
}

Answer 1

function testquestion() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const rg = sh.getRange(sh.getLastRow()+1,1);
  let r = rg.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(r),'Test');
}

如果电子表格底部没有可用的行，我会得到相同的结果

Execution log
3:44:46 PM  Notice  Execution started
3:44:46 PM  Error   
Exception: Invalid argument
testquestion    @ ag1.gs:5

在本例中，getLastRow（）1不存在，因为没有更多的行。

这样的事情可能会为您解决问题：

function testquestion() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  //add this section to prevent error
  let lr=sh.getLastRow();
  let mr=sh.getMaxRows();
  if(lr==mr) {
    sh.insertRowAfter(mr);
  }
  //end of section
  const rg = sh.getRange(sh.getLastRow()+1,1);
  let r = rg.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(r),'Test');
}

更新：

 function myFunction() {
      // Get handles to Daily and Archive sheets
      var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Scorecard');
      var appendSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('[Archive] Scorecard');
      //add this section to prevent error
      let lr = sh.getLastRow();
      let mr = sh.getMaxRows();
      if (lr == mr) {
        sh.insertRowAfter(mr);
      }
      //end of section
      //get last row of the daily sheet
      var Direction = SpreadsheetApp.Direction;
      var DailySLast = sh.getRange("A" + (sh.getLastRow() + 1)).getNextDataCell(Direction.UP).getRow();
    
      //Range of cells to be copied
      var CopyRange = "Scorecard!9:" + DailySLast;
    
      //Range of cells to be deleted
      var DelRange = "Scorecard!9:" + 2000;
    
      //get last row of the archive sheet
      var ArchiveSLast = appendSheet.getRange("A" + (appendSheet.getLastRow() + 1)).getNextDataCell(Direction.UP).getRow();
    
      //copying destination range
      var destrange1 = "[Archive] Scorecard!" + (ArchiveSLast + 1) + ":" + (ArchiveSLast + DailySLast - 8);
      var destrange = sh.getRange(destrange1);
    
      // Clear the contents only if Underwriting sheet has more than 10 rows of data
    
      if (DailySLast > 10) {
    
        //copying the data
        var sourceDataValues = sh.getRange(CopyRange).copyTo(destrange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    
        // Get the formulas from the sheet
        var formulas = sh.getRange(DelRange).getFormulas();
    
        // Delete the data from the daily sheet
        sh.getRange(DelRange).clearContent();
    
        //Put the formulas back in the sheet
        sh.getRange(DelRange).setFormulas(formulas)
      };