Automatically sort data based on date

Platform discussions Special workflows & use cases
1

image
image1368×511 150 KB

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

Hello @veeraRVR !

Perhaps I am missing something, but is there any reason you use Excel and do not do this directly in monday?

You can use the WORKDAY formula there as well , and then create view where re-order date is always sorted.

Best,
Giannis, Implementation Consultant at thespelas.com