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

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) 
}

CodePudding user response：

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).

CodePudding user response：

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})
}

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.