getLastRow returns no result

Question

I have this simple code to copy transposed range to the last row of another Sheet starting from column 14

   function copyAllInRange(){
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var sheet = ss.getActiveSheet();
    var destsheet = ss.getSheetByName('Database')
    var destrow = destsheet.getLastRow()

    var rangeToCopy = sheet.getRange(53, 4, sheet.getMaxRows(), sheet.getRange('54:54').getLastColumn());
    rangeToCopy.copyTo(destsheet.getRange(destrow,14),SpreadsheetApp.CopyPasteType.PASTE_VALUES,true);

    }

It returns zero results as it is. If I change destrow from the formula to simple 2 (that is the last empty row now), it works fine. Why it does not return last row index in the destination sheet?

Anton, does your target sheet have data in it? Because if it does not, the getLastRow() will return 0, invalid row index for getRange() method. — Oleg Valter
yes, it has a header in the first row so should return 2. anyway, now it works — Anton Marchuk
Anton, thank you for clarifiyng - I've noticed that Cooper gave the right answer, glad to hear it works fine now! — Oleg Valter

Cooper Cooper · Accepted Answer · 2019-06-12T00:47:42

Try this:

function copyAllInRange(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var destsheet = ss.getSheetByName('Database');
  var destrow = destsheet.getLastRow();
  var rangeToCopy = sheet.getRange(53,4,sheet.getMaxRows()-52,sheet.getLastColumn()-3);//are really sure that you want to use getMaxRows() here cause that seems weird to me. 
  rangeToCopy.copyTo(destsheet.getRange(destrow + 1,14),SpreadsheetApp.CopyPasteType.PASTE_VALUES,true);
}

getLastRow returns no result

2 Answers