Is there a way to keep certain sheets in a Google Doc continuously sorted by the first column, which contains timestamps? Although I have a script that works when I hard-code the range, it doesn't seem to function properly when using getLastRow() and getLastColumn() to define the range. The code snippet below shows my attempt, but uncommenting it causes the code to fail. Any ideas on what might be causing this issue?
var sheet = event.source.getActiveSheet();
var editedCell = sheet.getActiveCell();
var columnToSortBy = 1;
var tableRange = "A2:Z99";
if(editedCell.getColumn() == columnToSortBy && /^Form\sResponses/.test(sheet.getSheetName())){
var range = sheet.getRange(tableRange);
/* WHY DOESN'T THIS WORK -- var range = sheet.getRange(2,1,sheet.getLastRow(),
sheet.getLastColumn()-1); */
range.sort({column: columnToSortBy , ascending:false});
};
};