CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
nicola
nicola

Reputation: 5

The coordinates of the target range are outside the dimensions of the sheet

I use this code and while in the past it worked well, today I received this error message:

The coordinates of the target range are outside the dimensions of the sheet. (line 27, ___.gs)

My code is not too complex:

function onEdit() {
  var sheetNameToWatch1 = "Assign_Page";
  var columnNumberToWatch = 19;
  var valueToWatch = "Delivred";
  var sheetNameToMoveTheRowTo = "K_Delivery_Archive";
  var sheetNameToMoveTheRowTo2 = "Buffering";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();

  if (sheet.getName() == sheetNameToWatch1 && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, 22).copyTo(targetRange, {contentsOnly: true});

    var targetSheet2 = ss.getSheetByName(sheetNameToMoveTheRowTo2); 
    var targetRange2 = targetSheet2.getRange(targetSheet2.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, 22).copyTo(targetRange2, {contentsOnly: true});

    sheet.deleteRow(range.getRow()); // line 27

    var Sheet1 = ss.getSheetByName("Clients_Page");
    var Sheet2 = ss.getSheetByName("Assign_Page");
    var Sheet3 = ss.getSheetByName("Report");
    var Sheet4 = ss.getSheetByName("Delivery_Information");
    Sheet4.showSheet();
    Sheet1.hideSheet();
    Sheet2.hideSheet();
    Sheet3.hideSheet();
  }
}

What causes this error, and how can I resolve it?

Upvotes: 0

Views: 2154

Answers (1)

Wicket
Wicket

Reputation: 38160

It's very likely that the problem is due to

targetSheet2.getLastRow()+ 1

The proper solution will depend on several factors that are not mentioned by the OP, but here is a hint:

If the sheet has open ended formulas that return "" then the last row is the same as the maximum number of rows in the sheet and inserting more rows will not solve the problem.

Upvotes: 1

Related Questions