onEdit function Sort multiple sheets / same spreadsheet-CodePudding

I am trying to create the same onEdit() function for multiple sheets where the function is only triggered when a value in Column C (3) for one of the sheets has been added.

Unfortunately I can't manage to get this script running by simply copying the script for each seperate sheet. Is there a way that I can use one single onEdit function for all sheets?

What I have so far for 1 specific sheet:

function onEdit(e) {
  multiSortColumns(e);
}
function multiSortColumns(e) {
  if (e.range.columnStart == 3 && e.range.getValue()!='') {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('FASHION NL');
    var range = sheet.getRange("A5:bY600");
    range.sort({column:11,ascending:true});
    ss.toast('Sort complete.');
  }
}

CodePudding user response：

About Is there a way that I can use one single onEdit function for all sheets?, in this case, when onEdit trigger is used, you want to use range.sort({column:11,ascending:true}); to all sheets? Or, onEdit trigger is used, you want to use range.sort({column:11,ascending:true}); to the active sheet, and you want to use this for all sheets?

In this answer, I would like to propose both patterns.

Pattern 1:

In this pattern, when onEdit trigger is used, range.sort({column:11,ascending:true}); is used for all sheets.

function onEdit(e) {
  multiSortColumns(e);
}

function multiSortColumns(e) {
  if (e.range.columnStart == 3 && e.range.getValue() != '') {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.getSheets().forEach(sheet => {
      var range = sheet.getRange("A5:bY600");
      range.sort({ column: 11, ascending: true });
    });
    ss.toast('Sort complete.');
  }
}

Pattern 2:

In this pattern, when onEdit trigger is used, range.sort({column:11,ascending:true}); is used to the active sheet. And, this script can be used for all sheets.

function onEdit(e) {
  multiSortColumns(e);
}

function multiSortColumns(e) {
  if (e.range.columnStart == 3 && e.range.getValue() != '') {
    var sheet = e.range.getSheet();
    var range = sheet.getRange("A5:bY600");
    range.sort({ column: 11, ascending: true });
    e.source.toast('Sort complete.');
  }
}

Note:

In this modified script, when the size (cells) of the sheet is smaller than "A5:bY600", an error might occur. Please be careful about this.

Added:

From your following reply,

However it seems to be targeted towards every activesheet. However, the activesheet needs to be limited to a specific range of sheets because I have some hidden sheets that do not require this script.

If you want to use this script to the specific sheets you expect, how about the following modification?

Modified script:

Please set your expected sheet names to var sheets = ["Sheet1", "Sheet2", , ,];. By this, the script is run to the inputted sheets.

function onEdit(e) {
  multiSortColumns(e);
}

function multiSortColumns(e) {
  if (e.range.columnStart == 3 && e.range.getValue() != '') {
    var sheets = ["Sheet1", "Sheet2", , ,]; // Please set your expected sheet names.
    var sheet = e.range.getSheet();
    if (sheets.includes(sheet.getSheetName())) {
      var range = sheet.getRange("A5:bY600");
      range.sort({ column: 11, ascending: true });
      e.source.toast('Sort complete.');
    }
  }
}