Google Apps Script - How can I get script to update destination MaxRows after copyTo()?

Question

At the end of a multi-sheet process, I end up with a sheet, named Query, containing a query in A1 and the results of that query, which vary in length (rows) from one usage to another. The end user must be able to manually edit those results, but should not be allowed to alter the query, so the Query sheet is protected. I've written a macro to copy the data from this sheet to another one, named Output, which is NOT protected, so the user can edit it as needed.
The macro performs two functions. First it copies the data from the Query sheet to the Output sheet, pasting only the values. This function works. Second, it deletes extra blank rows from below the data, leaving just one blank row to signal the user that the data is complete. This works when the data in the Query sheet is shorter, i.e. has fewer rows, than during the previous pass through the process, but fails if the Query sheet has increased in size since the pass.
Analysis shows that:

copyTo() will increase the number of rows in the target sheet if necessary to accommodate the incoming data
the value returned by getMaxRows() is not updated when this happens. As a result, the code that should delete trailing blank rows calculates a negative number for how many rows should be deleted, throwing an error.

The code, including several logging entries:

function Laststep() {
  var spreadsheet = SpreadsheetApp.getActive();      // Get this spreadsheet.
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Query'), true);
  var sheet = spreadsheet.getActiveSheet();         // Look at the Query sheet.
  sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).activate();   // Set the range to all the rows containing data.
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Output'));
  var outsheet = spreadsheet.getActiveSheet();
  outsheet.getRange(1,1).activate();              // Point to A1 on the Output sheet.
   var maxRows = outsheet.getMaxRows();          // Total rows in the sheet, before copyTo(). 
   Logger.log('maxRows before copy = ' + maxRows);
  spreadsheet.getRange('Query!A:F').copyTo(outsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
                                         // Copy values-only from Query to Output.
 
                                             // Delete trailing blank rows from Output:
  var lastRow = outsheet.getLastRow()+1;        // First empty row. Let the user see it.
   Logger.log('lastRow = ' + lastRow);
  var maxRows = outsheet.getMaxRows();          // Total rows in the sheet after copyTo(). 
   Logger.log('maxRows after copy= ' + maxRows);
   Logger.log('maxRows-lastRow = ' + (maxRows-lastRow));
  if (maxRows-lastRow != 0)                     // If data doesn't fill sheet
  {
     Logger.log('Deleting...');
    outsheet.deleteRows(lastRow, (maxRows-lastRow));    // Delete the excess rows.
  }
   Logger.log('Done.');
};

Sample of logger output from an error case:

maxRows before copy = 20  
lastRow = 911  
maxRows after copy= 20  
maxRows-lastRow = -891  
Deleting...  
Exception: Those rows are out of bounds.  
    at Laststep(macros:23:14)

Question 1: How can I get an accurate count of MaxRows after the execution of copyTo()?

Question 2: Is the failure to update MaxRows after copyTo() an Apps Script bug or just something I don't yet understand?

Google Apps Script - How can I get script to update destination MaxRows after copyTo()?

0 Answers0