Google Apps Script Sort Tabs/Sheets - by Latest Date First and don't sort/show hidden sheets?

google-apps-script

I am trying to sort tabs by date, putting the newest date first; and I found this code that is helpful but seems to reverse when I use it. I'm not sure how to add to the code so that it doesn't open the hidden tabs; and I'd like to NOT have it sort a tab called 'Students' and leave it as the first tab.

The expected (hopeful) behavior is that it it leaves the 'Student' tab as is and sorts the remaining open tabs with the newest date first (consistently); and does not unhide the hidden tabs.

The actual behavior is it sorts all tabs, unhides those that are hidden - putting the latest tab first the first time I run it; then when I run it again, it reverses the order and the latest date is on the end (and it includes the 'Student' tab in the sort.

Here's a link If you would like to see an example file. Thank you.

Sample File

The code for the function:

   function sortSheets () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var obj = sheets.map(function(s) {
    var sheetName = s.getSheetName();
    var date = 0;
    if (sheetName != "Students") {
      var ar = sheetName.split("/");
      date = new Date(ar[4], ar[2], ar[2]).getTime();
    }
    return {sheet: s, converted: date};
  });
  obj.sort(function(a, b) {return a.converted > b.converted ? 1 : -1});
  obj.forEach(function(s, i) {
    ss.setActiveSheet(s.sheet);
    ss.moveActiveSheet(i + 1);
  });
}

Solution 1:

I believe your goal is as follows.

There are the following sheets in the Spreadsheet.

  Pre-K-F <--- hidden
  Pre-K <--- hidden
  KG <--- hidden
  Students
  2022/01/19 - Suzie  Q - G3 - T G3
  2021/09/30 - Suzie  Q - G3 - T G2
  2021/05/10 - Suzie  Q - G2 - T G2

You want to sort the sheets of 2022/01/19 - Suzie Q - G3 - T G3, 2021/09/30 - Suzie Q - G3 - T G2 and 2021/05/10 - Suzie Q - G2 - T G2 as the date of top letter.
In this case, you want to ignore the hidden sheets and "Students" sheet.

In order to achieve your goal, how about the following modification?

Modification points:

In your sheet name including the date is like 2022/01/19 - Suzie Q - G3 - T G3. But in your script, var ar = sheetName.split("/"); date = new Date(ar[4], ar[2], ar[2]).getTime(); is used for retrieving the date. In this case, new Date(ar[4], ar[2], ar[2]) returns Invalid Date.
In your script, the "Students" sheet and hidden sheets are also used for sorting the sheets.

When these points are reflected in your script, it becomes as follows.

Modified script:

function sortSheets () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var obj = sheets.reduce(function(ar, s) {
    var sheetName = s.getSheetName();
    var hide = s.isSheetHidden();
    if (sheetName != "Students" && !hide) {
      var date = new Date(sheetName.split(" - ")[0]).getTime();
      ar.push({name: sheetName, sheet: s, converted: date, hide})
    }
    return ar;
  }, []);
  var diff = sheets.length - obj.length;
  obj.sort(function(a, b) {return a.converted < b.converted ? 1 : -1});
  obj.forEach(function(s, i) {
    ss.setActiveSheet(s.sheet);
    ss.moveActiveSheet(i + 1 + diff);
  });
}

In this modification, both the hidden sheets and "Students" sheet were ignored.
The script for parsing the date from the sheet name was modified.

Note:

In this modified script, the sheets of top of sheet names are sorted as follows.

  Pre-K-F <--- hidden
  Pre-K <--- hidden
  KG <--- hidden
  Students
  2022/01/19 - Suzie  Q - G3 - T G3
  2021/09/30 - Suzie  Q - G3 - T G2
  2021/05/10 - Suzie  Q - G2 - T G2

If you want to sort the following order,
```
  Pre-K-F <--- hidden
  Pre-K <--- hidden
  KG <--- hidden
  Students
  2021/05/10 - Suzie  Q - G2 - T G2
  2021/09/30 - Suzie  Q - G3 - T G2
  2022/01/19 - Suzie  Q - G3 - T G3
```
- Please modify obj.sort(function(a, b) {return a.converted < b.converted ? 1 : -1}); to obj.sort(function(a, b) {return a.converted > b.converted ? 1 : -1});.

Reference:

reduce()