I'm trying to make my spreadsheet AUTOMATICALLY sort the data based on DATE. I've attached a photo of the spreadsheet i am using.
You'll notice there is a WORKDAY formula in column G. At the moment, once i've updated the Dispensed Date, the Re-order Date updates according to the formula (based on Working Day arranged). I then use SORT RANGE A-Z for column G to give me earliest date first. I would desperately like to automate this bit - the SORTING RANGE bit - so that once i've updated Date Dispensed, the Re-Order Date is updated according to the formula and then automatically drops the entire row to the bottom of the list.
Where I am at the moment:
Using YouTube, I've managed to make the column Auto Sort but ONLY if I change the Re-Order date myself. It is not accepting sorting the date if i update Date Dispensed. Although it does update the Re-order date to the new date, however, it does not sort the data A-Z. It stays where it is. I hope I'm making some sense. This is the code/formula I've used based on YouTube:
function autoSort(e) {
const row = e.range.getRow();
const column = e.range.getColumn();
const ss = e.source;
const currentSheet = ss.getActiveSheet();
const currentSheetName = currentSheet.getSheetName();
if (!(currentSheetName === 'ORDERPAD' && column === 7 && row >= 2)) return;
const range = currentSheet.getRange(2, 1, currentSheet.getLastRow() - 1, 7);
range.sort({ column: 7, ascending: true });
}
function onEdit(e) {
autoSort(e);
}
Try this:
function onEdit(e) {
const sh = e.range.getSheet();
if (sh.getSheetName() == 'ORDERPAD' && e.range.columnStart > 5 && e.range.columnStart < 8 && e.range.rowStart > 1) {
sh.getRange(2,1,sh.getLastRow() - 1, 7).sort({column:7,ascending:true});
}
}