"The coordinates of the range are outside the dimensions of the sheet" error when sorting

Question

I am trying to create a script to sort a table on the current active sheet/tab but I am getting an error that I could not identify. I keep getting The coordinates of the range are outside the dimensions of the sheet.. My table has data in them from columnA to columnQ. Data in columnA are identification numbers and columnB to columnQ contain formulas. What am I doing wrong with my script?

var sortFirst = 7; //index of column to be sorted by; 1 = column A, 2 = column B, etc.
  var sortFirstAsc = true; //Set to false to sort descending

  var sortSecond = 8;
  var sortSecondAsc = true;

  var sortThird = 9;
  var sortThirdAsc = true;

  var activeSheet = SpreadsheetApp.getActiveSheet();
  var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
  range.sort([{column: sortFirst, ascending: sortFirstAsc}, {column: sortSecond, ascending: sortSecondAsc}, {column: sortThird, ascending: sortThirdAsc}]);

Answer 1

It's very likely that the error occurs on the line

var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());

It's very likely that the solution is to change it by

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

The above because the range start on row 2 and because it's very likely that sheet has content on its last row.

Related

• Google Sheets Script getLastRow() not returning all values
• Need help debugging Google Apps Script code intended to update hundreds of files in Google Drive

Answer 2

Here is the work around for this bug in Google Sheets: It simply adds a row to the end of the sheet whenever the lastrow is greater than or equal to maxrows. This is not done automatically from the scripting side (though hundreds of other behind-the-scenes functions are called by Google's system to emulate working on a spreadsheet).

let maxRows = mySheet.getMaxRows();
if(mySheet.getLastRow() >= maxRows) {mySheet.insertRowsAfter(maxRows,1);}

I hope this helps all those suffering from this problem when Sorting, Deduplicating, etc on Google Sheets.