How to sort sheets within a spreadsheet in chronological order by date in google apps script

1
votes

I am attempting to sort the sheets within my spreadsheet in chronological order. They are all dates in the same format (MM-dd-yyyy), but I am unsure of how to treat them as a date while sorting, or whether that is even the best approach.

I currently have copied code that sorts it alphabetically, which gets the MM-dd part ordered correctly generally, but the years are not in order.

function sortSheets () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNameArray = [];
  var sheets = ss.getSheets();

  for (var i = 0; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
  }

  sheetNameArray.sort();

  for( var j = 0; j < sheets.length; j++ ) {
    ss.setActiveSheet(ss.getSheetByName(sheetNameArray[j]));
    ss.moveActiveSheet(j + 1);
  }
  ss.setActiveSheet(ss.getSheetByName("GUI"));
  ss.moveActiveSheet(1);
}

This is currently what my code looks like, but again it is just meant to alphabetize rather than sort chronologically. The results that I hope to receive would be the tabs being in order, 1 being "GUI", and 2 onward sorted from earliest date to latest date.

1
sortinggoogle-apps-scriptgoogle-sheets

1 Answers

0
votes
  • You want to sort the sheets in a Spreadsheet.
  • The format of sheet name is MM-dd-yyyy.
  • There is a sheet with the sheet name of GUI.
  • There is several sheets with the sheet name of MM-dd-yyyy.
  • You want to sort the sheets as follows.
    • The 1st sheet is GUI. The sheets of MM-dd-yyyy are from earliest date to latest date.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

In this modification, I used the following flow.

  1. Retrieve all sheets.
  2. Create an object array for sorting.
    • The object includes the sheet object and the value converted from MM-dd-yyyy to the unix time.
  3. Sort the object array with the converted values.
  4. Rearrange the sheets using the sorted array.

Modified script:

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 != "GUI") {
      var ar = sheetName.split("-");
      date = new Date(ar[2], ar[0], ar[1]).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);
  });
}

References:

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

From your shared Spreadsheet, it was found that the format of the sheet name is not MM-dd-yyyy. That was MM/dd/yyyy. In this case, please modify above script as follows.

From:

var ar = sheetName.split("-");

To:

var ar = sheetName.split("/");