Google sheets won't update automatically with onEdit function

Question

I was trying to make my google sheets document automatically sort on date (ascending). When I run the code by myself, it works but the onEdit function which would make it automatic, doesn't work.

I followed this Youtube video and changed the values to my document: https://www.youtube.com/watch?v=3EUI_PgxarA . A couple of days ago the onEdit function did work and there have been no further changes since.

This is my code:

function autoSort(){

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName("Toekomstplanning")
  const range = ws.getRange(5,1,ws.getLastRow()-1,6)

  range.sort({column: 6, ascending: true})
}

function onEdit(e){

  const row = e.range.getRow()
  const column = e.range.getColumn()

  if(!(column === 6 && row >= 5)) return 

  autoSort()
}

Answer 1

Your code seems to work ok? I would change your if statement as it's a bit bizarre. You also might want to account for executing if the edit is in a range that includes column 6 (vs. equaling).

Regardless, this works for this spreadsheet...

function autoSort() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Toekomstplanning");
  const range = ws.getRange(6, 1, ws.getLastRow() - 1, 6);
  range.sort({ column: 6, ascending: true })
}

function onEdit(e) {
  const zRange = e.range;
  const zRow = zRange.getRow();
  const zCol = zRange.getColumn();

  //runs code if range edited includes column 6 and row is greater or equal to 5.
  if (zCol <= 6 && zRange.getLastColumn() >= 6 & zRow >= 5) {
    autoSort();
  }
}

Answer 2

It won't behave any differently but it does make better use of the event object.

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Toekomstplanning" && e.range.columnStart == 6 && e.range.rowStart > 4) {
    sh.getRange(5, 1, sh.getLastRow() - 4, 6).sort({ column: 6, ascending: true })
  }
}

• Learn More