How to get Google Sheets reorder based on points?-CodePudding

I need help with my table.

I need to reorder my table (range: DY23:DZ33) in points by descending order.

But when I run my script I get an error: Cannot read property 'range' of undefined at onEdit(Code:2:19)

The code:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() == "Test1" && range.getRow() >= 23 && range.getRow() <= 33 && range.getColumn() == 131) {
    sheet.getRange("DY23:EA33").sort({column: 131, ascending: true});
  }
}

The tabel: https://docs.google.com/spreadsheets/d/1v9oqRNfmsVvrpZmiolOIyyuGx-OALyIYPPOXsa5znRg/edit#gid=1473685733

NB! Please forgive me for my poor english.

CodePudding user response：

Issue and workaround:

I think that your error of Cannot read property 'range' of undefined at onEdit is due to that you might directly run the function onEdit. I think that this might be the reason for your current issue.
In your script, when the cells of "EA23:EA33" are edited, the script is run. But when I saw your sample Spreadsheet, the cells of "EA23:EA33" are the formulas. In this case, when the values by the formulas are changed, the function onEdit is not run. I think that this might be the reason for your 2nd issue.
And also, when you directly sort the cells of "EA23:EA33" using the sort method, the ranges of formulas in the cells are changed. By this, the values are changed. I think that this might be the reason for your 3rd issue.

When you want to run the script using the script editor, the button, and the custom menu, it is required to change the function without using the event object. So how about the following modification?

Modified script:

function sample() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test1");
  const range = sheet.getRange("EA23:EA33");
  const formulas = range.getFormulas();
  const mFormulas = formulas.map(([ea]) => [ea.replace(/=SUM\(([A-Z] )([0-9] )\ ([A-Z] )([0-9] )\)/i, "=SUM($$$1$$$2 $$$3$$$4)")]);
  if (JSON.stringify(formulas) != JSON.stringify(mFormulas)) range.setFormulas(mFormulas);
  SpreadsheetApp.flush();
  sheet.getRange("DY23:EA33").sort({ column: 131, ascending: true });
}

In this modified script, you can directly run this function with the script editor.
In this modified script, the formulas of cells of "EA23:EA33" are changed by the absolute reference. And the cells "DY23:EA33" are sorted. By this, the formulas can work by the sort.

References:

CodePudding user response：

Oh! It works! Thank you!

You're the boss!