'How to sort sheets within a spreadsheet in chronological order by date in google apps script
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.
Solution 1:[1]
- 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 ofMM-dd-yyyy
are from earliest date to latest date.
- The 1st sheet is
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.
- Retrieve all sheets.
- Create an object array for sorting.
- The object includes the sheet object and the value converted from
MM-dd-yyyy
to the unix time.
- The object includes the sheet object and the value converted from
- Sort the object array with the converted values.
- 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("/");
Solution 2:[2]
I found this question by looking for a way to sort by sheet name (alphabetical order), so the following solution may be helpful for someone else too:
/**
* Sorts the sheets on alphabetical order.
*/
const sortSheets = () => {
const spreadsheet = SpreadsheetApp.getActive();
const sheets = spreadsheet.getSheets();
sheets.sort((a, b) => a.getSheetName().localeCompare(b.getSheetName()));
sheets.forEach((sheet, index) => {
spreadsheet.setActiveSheet(sheet);
spreadsheet.moveActiveSheet(index + 1);
});
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | |
Solution 2 | Raphael Setin |