google-sheets

Automatically Sort Data in Google Sheets onEdit


I am trying to automatically sort my Google Sheet by date onEdit. This is the script I am using and it is working as I need it to. However, I have two more sheets in the same file I want this to work on, with the date column being in different columns between all of my sheets. Is there a way to adapt the following so it can automatically sort the data on multiple Sheets in the same file?

Thanks in advance for any help.

function onEdit(event){
  var w = event.source.getSheetByName("Flights");//Replace Flights with the name of the worksheet you want to sort in
  var c = w.getActiveCell();

  var srtby = 4; //Change this to the position of the col you want to sort by
  var t = "A2:Z"; // Change this to range of cells that you want to sort (don't include the column headings).

  if(c.getColumn() == srtby){   
    var range = w.getRange(t);
    range.sort( { column : srtby, ascending: true } );//change false to true if you want to sort in ascending order
  }
}

Solution

  • In your situation, how about the following modification using a JSON object?

    Modified script:

    Please set object in this function to your situation. "Flights": { srtby: 4, t: "A2:Z" } is from your script. "Sheet2": { srtby: 1, t: "A2:E" } is a sample value. Please set the sheet name as a key and srtby and t as the value.

    function onEdit(event) {
      // Please set this object to your situation.
      const object = {
        "Flights": { srtby: 4, t: "A2:Z" }, // This is from your script.
        "Sheet2": { srtby: 1, t: "A2:E" }, // This is a sample.
      };
    
      const sheet = event.source.getActiveSheet();
      const o = object[sheet.getSheetName()];
      if (!o) return;
      const { srtby, t } = o;
      if (event.range.columnStart == srtby) {
        var range = sheet.getRange(t);
        range.sort({ column: srtby, ascending: true });
      }
    }
    

    In this modification, when column ""D" of "Flights" sheet is edited, "A2:Z" is sorted by column "D". When column "A" of "Sheet2" is edited, "A2:E" is sorted by column "A".