S
S
Salgerd2020-10-26 18:16:36
Google Apps Script
Salgerd, 2020-10-26 18:16:36

Is it possible to make a working script to sort data in a table?

Help fix the script!

There is a table that needs sorting in descending order in column E , that is, I will change the values, and the entire row should take its place according to the serial number that is in column A (it should NOT change), that is, the entire row from B to Z must be sorted.

https: //docs.google.com/spreadsheets/d/1iPl4-OEvhU ...

function onEdit(e) {
const eventCell = e.range;
if (eventCell.getColumn() == 5 && eventCell.getNumColumns() == 1 && eventCell.getNumRows() == 1 && eventCell.getRow() > 2) {
const sheet = eventCell.getSheet();
const lastRow = sheet.getRange('E3').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
sheet.getRange(3, 2, lastRow - 2, 13).sort({column: 5, ascending: false});
};//
} // onEdit event


Here is a script, but it works somehow clumsily...

And I would also like to make the same values ​​in column D highlighted in the same color!

Help, please, good people!

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Maxim Stoyanov, 2020-11-11
@stomaks

1) "The script that I mentioned in the question swears (TypeError: Cannot read property 'range' of undefined)" everything is correct, it should be so when you run the script manually. The variable e that you pass to the function only contains data when the simple onEdit trigger fires, but when manually triggered by this function, it will be undefined.
Add the following stub to the beginning of the script (so you could run the script manually):

e = e || {
    range: SpreadsheetApp.getActiveRange()
  };

But then authorization in a script will be necessary.
Use a setup trigger on a change in a table.
2) Do you have a working script that doesn't work, I can't figure it out?
function onEdit(e) {
  e = e || {
    range: SpreadsheetApp.getActiveRange()
  };
  
  const range = e.range;
  
  if (range.getColumn() !== 5)
    return;
  
  if (range.getRow() < 3)
    return;
  
  const sheet = range.getSheet();
  
  const frozen_rows = 2;
  
  const last_row = sheet
  .getRange('E'+(frozen_rows+1))
  .getNextDataCell(SpreadsheetApp.Direction.DOWN)
  .getRow();
  
  sheet
  .getRange(frozen_rows+1, 2, last_row - frozen_rows, 13)
  .sort({column: 5, ascending: false});
}

---
Maxim Stoyanov (stomaks), developer of Google Apps Script .
g-apps-script.com
stomaks.me

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question