How to move my columns with my rows when trying to Autosort by due date

I am trying to Autosort some data by range so it can make my life easier. I don't know anything about coding and have been asking my friends. The main problem is when I manually start I can see the due date changing and is getting sorted corrected but the column isn't moving with the data and this will create incorrect sorting. Is there any code I can add onto to allows columns to pretty much move with the columns. The video will give a better visual.

Custom Date

enter image description here

My Sheets

enter image description here

Video: How I usually Sort and what happens after I sort (Blue Highlight New Data)

https://go.screenpal.com/watch/cTnu1dn1lKP Warning: This will bring you to an external link

My code:

function autoSortByDueDate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Assignment Tracker');
  if(sheet == null) {
Logger.log('Sheet not found');
return;
  }

  var lastRow = sheet.getLastRow();
  Logger.log('LastRow:' + lastRow);

  if(lastRow <=19) {
Logger.log('No data to sort');
return;
  }

  var range = sheet.getRange(20, 17, lastRow - 19, 3); //replace 1 for 3 if something goes wrong
  Logger.log('Range:' + range.getA1Notation());

  SpreadsheetApp.flush();

  range.sort({column: 17, ascending: false});
  Logger.log('Sorting Completed');
}
// I think this is where it is going wrong
function onEdit(e) {
  var range = e.range;
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == 'Assignment Tracker' && range.getColumn() <= 17 && range.getCoumn() <= 19 && range.getRow() <=20){ 
  autoSortByDueDate();
  }

}

Solution

Google Apps script getRange()

With the video you have provided, you manually sort the specific data, then run the code. Here are few issues I have noticed which produced issues:

When you sort the data range you did not include all the rows. Instead, you did it twice, which ends with data not being sorted as a whole.

When you run the script, the script only gets the range of Column Q to S, then based on the sorting on the range of column Q, which results in the dates being sorted disregarding the other columns.

Your conditional statement in the onEdit(e) function has incorrect data ranges.

In order to rectify these issues I have modified your code and corrected the ranges.

Modified code:

function autoSortByDueDate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Assignment Tracker');
  if(sheet == null) {
Logger.log('Sheet not found');
return;
  }

  var lastRow = sheet.getLastRow();
  Logger.log('LastRow:' + lastRow);

  if(lastRow <=19) {
Logger.log('No data to sort');
return;
  }

  var range = sheet.getRange(20, 2, lastRow - 19, 22); 
  
  Logger.log('Range:' + range.getA1Notation());

  SpreadsheetApp.flush();
  range.sort({column: 17, ascending: false});
  
  
  Logger.log('Sorting Completed');


}

function onEdit(e) {
  var range = e.range;
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == 'Assignment Tracker' && range.getColumn() >= 17 && range.getColumn() <= 20 && range.getRow() >= 20){ 
  autoSortByDueDate();
  }
}

What I have improved:

The code now gets the range from column B to column W, starting from row 20 and below, allowing it to sort all the relevant data, and based on the sorting values in column Q (dates).
I have made some improvements on how the onEdit(e) function will trigger. It will now detect if you have made changes to the column Q to S, row 20 and below, then it will trigger the sorting of the whole data.

Steps to do in this updated code:

You do not have to manually sort the ranges, you just need to paste the updated code and paste it on your end.
After pasting the code, run the autoSortByDueDate() function, then see the results if it meets your requirements.
After you have validated that this works for you, run the onEdit(e) function.
Once you have run the onEdit(e) function, the autoSortByDueDate() function will now be triggered whenever you have made changes to your date column, or even added another row. The script now automates the sorting of your ranges.

If the ranges to be sorted do not meet your target data to be sorted, adjust the ranges accordingly by following the references below.

References: