I am trying to create a script to sort a table on the current active sheet/tab but I am getting an error that I could not identify.

Any help is appreciated!

function onEdit(e) {
  
  var sht = e.source.getActiveSheet();
  var arr_sht = ["sheet 1","sheet 2","sheet 3","sheet 4"]
  var sht_name = sht.getName();

  if (!arr_sht.includes(sht_name)){return;};

  var c = e.range.getColumn();
  if(c !== 1) {return;}
  var r = e.range.getRow();
  if (r<12) {return;}
  if (e.value !== 'next step'){return};

  var rng = SpreadsheetApp.getActiveSheet().getDataRange();
  rng = rng.offset(11,0,rng.getNumRows()-1);
  rng.sort({column:1,ascending:true})


}

CodePudding user response：

Use Sheet.getRange(), like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
        'It runs automatically when you hand edit the spreadsheet. '
        'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  const sheetRegex = /^(sheet 1|sheet 2|sheet 3|sheet 4)$/i;
  const rowStart = 11;
  const columnStart = 1;
  let sheet;
  if (e.value !== 'next step'
    || e.range.rowStart < rowStart
    || e.range.columnStart !== columnStart
    || !(sheet = e.range.getSheet()).getName().match(sheetRegex)) {
    return;
  }
  const table = sheet.getRange(rowStart, columnStart, sheet.getLastRow() - rowStart   1, sheet.getLastColumn() - columnStart   1);
  table.sort({ column: columnStart, ascending: true })
}

See these onEdit(e) optimization tips.

CodePudding user response：

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  const names = ["sheet 1","sheet 2","sheet 3","sheet 4"]
  const idx = names.indexOf(sh.getName());
  if(~idx && e.range.columnStart == 1 && e.range.rowStart < 12 && e.value == 'next step') {
    sh.getDataRange().sort({column:1,ascending:true})
  }
}