Google Sheets – Auto-Sort 2 Columns within a Specific Range on Multiple Worksheets

1
votes

I have been looking for a way to script an auto-sort on a Google Sheet that does the following:

  • Only applies to a specific range (A35:I911), but on all of the worksheets within the main Google Sheet.
  • Sorts the A column (Date of Post) first and the B column (Time of Post) second.
  • Occurs on edit of any cell within that range (A35:I911).

I tried the following code, but it will not work properly:

   function onEdit(event) {
      var postDate = 1;
      var postTime = 2;
    
      var sheet = event.source.getActiveSheet();
      var tableRange = "A35:I911";
      var range = sheet.getRange(tableRange);
    
      range.sort([
        {column: postDate, ascending: true},
        {column: postTime, ascending: true}
      ]);
    }

Thank you in advance!

Here is a link to a redacted version of the Google Sheet: https://docs.google.com/spreadsheets/d/1NUy6aY-lEA66UFXg4Yx4mXsTgFvj5g2Dhv4jToYsQnw/edit?usp=sharing)

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

Modification points:

  • Although from your question, unfortunately, I cannot understand about but it will not work properly, when I saw your sample Spreadsheet, it seems that the max row is 910 at the sheet "March 2021". I thought that this might be the reason of your issue of it.
  • When you want to run the script when the cells "A35:I911" are edited, in your situation, I thought that when the cells "A35:B911" are edited, running the script might be suitable. Because even when the cells "C35:I911" are edited, the sort is not run.
  • And, from your sample script, I think that you might not want to run the script when the sheet "Overview" is edited.

When above points are reflected to your script, it becomes as follows.

Modified script:

function onEdit(event) {
  var range = event.range;
  var sheet = event.source.getActiveSheet();
  if (sheet.getSheetName() == "Overview" || range.rowStart < 35 || range.rowStart >= 911 || range.columnStart > 2) return;
  var postDate = 1;
  var postTime = 2;
  var tableRange = sheet.getMaxRows() < 911 ? "A35:I" + sheet.getLastRow() : "A35:I911";
  var range = sheet.getRange(tableRange);
  range.sort([
    {column: postDate, ascending: true},
    {column: postTime, ascending: true}
  ]);
}
  • When you want to run the script, please edit the cells "A35:I911" in the sheet except for "Overview".
  • When you want to run the script when the cells "A35:I911", please modify range.columnStart > 9.

References: