How to copy the last cell from the specific Column-CodePudding

I have been trying to copy the last non empty cell from Sheet1 Column A and paste that cell into the last empty cell of the Sheet2 Column D.

But an error The coordinates of the target range are outside the dimensions of the sheet. is appearing. Any help will be appreciated.

function myFunction() {
  const sheetName1 = "sheet1";
  const sheetName2 = "sheet2";


  const sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName1);
  const sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName2);

  const lastRow1 = sheet1.getLastRow();
  const lastRow2 = sheet2.getLastRow();

  sheet1.getRange("A2:A"   lastRow1).copyTo(sheet2.getRange("D"   (lastRow2   1)));

}

CodePudding user response：

Solution:

This line is trying to copy multiple rows to a range that has exceeded the dimensions of the target sheet, which is not allowed:

sheet1.getRange("A2:A"   lastRow1).copyTo(sheet2.getRange("D"   (lastRow2   1)));

Since you are trying to copy the last non empty cell from Sheet1 Column A and paste that cell into the last empty cell of the Sheet2 Column D, you can use this code to get that row instead:

const lastRow1 = sheet1.getLastRow();

var values = sheet2.getRange('D:D').getValues();
for (i = values.length-1; i >= 0; i--) {
  if (values[i][0] == "") break;
}
const lastRow2 = i;

sheet1.getRange("A"   lastRow1).copyTo(sheet2.getRange("D"   (lastRow2   1));

Reference:

Class Range | copyTo()