CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Dan
Dan

Reputation: 35

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

I'm trying to sort a range that goes from the first cell of the 3rd row (C3) and goes until the last row and the seventh column.

No matter what I try I get the error "The coordinates of the range are outside the dimensions of the sheet"

Any idea where I'm going wrong? I've included my code below.

Thanks!

function onEdit(event){
  
  Utilities.sleep(900);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];  
  var range = sheet.getRange(3,1,sheet.getLastRow(),7);
  range.sort (7) 
}

Upvotes: 0

Views: 383

Answers (3)

Wicket
Wicket

Reputation: 38131

The problem occurs because your sheet has less rows than the value returned by sheet.getLastRow() plus the offset caused by the start row of the range, in this case 2.

One solution not requiring to change your code is to add more rows to the sheet but a most appropriate solution (already mentioned in the previous answers) is to replace sheet.getLastRow() by sheet.getLastRow() - 2.

Related

Upvotes: 0

Cooper
Cooper

Reputation: 64032

Try it this way:

function onEdit(e){
  const sh = e.range.getSheet();
  sh.getRange(3,1,sh.getLastRow() - 2,7).sort({column:7,ascending:true})
}

or this way:

function onEdit(e){
  const sh = e.range.getSheet();
  sh.getRange("A3:G" + String(sh.getLastRow())).sort({column:7,ascending:true})
}

Note: this function runs on all edits of all sheets. You should consider limiting access to unwanted sheets because not doing so will effect spreadsheet performance.

Really I can't think of any reason for using sleep in an onEdit function.

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9345

Try changing this line...

var range = sheet.getRange(3,1,sheet.getLastRow(),7);

to this...

var range = sheet.getRange(3,1,sheet.getLastRow()-2,7);

As your current line reads, you are asking to start on Row 3 and then extract the entire number of rows in the sheet. For instance, if your sheet only had 10 rows, you're asking to start at Row 3 and count 10 rows from that point, which is not possible.

You have to discount for the number of rows above your start range (which, in this case, is 2).

Upvotes: 1

Related Questions