-
-
Save bpmore/0337b4ac1fc9110975e70fc5df53203f to your computer and use it in GitHub Desktop.
| 1. Copy/Paste the information below to the clipboard | |
| 2. Open the spreadsheet whose sheets need to be alphabetised | |
| 3. Choose Tools > Script editor > Blank (this opens a new tab in the browser) | |
| 4. Press Control+A followed by Control+V copy and paste the script in | |
| 5. Press Control+S to save the script | |
| 6. Choose Run > sortSheets | |
| 7. Go back to the spreadsheet tab to view the new sorted tab order | |
| --Copy everything below this line-- | |
| 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); | |
| } | |
| } |
Is there a good way to sort sheets which do need to be sorted alphabetically but have dates listed at the end of the tab name by which a GoogleSheet needs to be organized? The location of the date does not change and we could make sure that all the dates are the same length.
//https://gist.github.com/bpmore/0337b4ac1fc9110975e70fc5df53203f
function sortSheets () {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetNameArray = [];
var sheetMap = {};
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
sheetNameArray.push(sheets[i].getName());
sheetMap[sheets[i].getName()] = sheets[i]
}
sheetNameArray.sort();
for( var j = 0; j < sheets.length; j++ ) {
//ss.setActiveSheet(ss.getSheetByName(sheetNameArray[j]));
ss.setActiveSheet(sheetMap[sheetNameArray[j]]);
ss.moveActiveSheet(j + 1);
}
}
//https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
//sort by name and color?
For Alphanumerical sorting can be used following function
...
var collator = new Intl.Collator(undefined, {numeric: true, sensitivity: 'base'});
sheetNameArray.sort(collator.compare);
...
Is there a way to sort tabs in different ways within one google spreadsheet file? I have a file with lots of tabs for different countries. I want to sort them alphabetically, but within regions. So for example sort all the countries that I have for Africa, then Europe, Asia, etc. Is there a way to do this?
thank you in advance!
anyway to sort them in descending alphabetical order?
Thank you!
anyway to sort them in descending alphabetical order?
Thank you!
Yes, should work with reverse().
sheetNameArray.sort().reverse();
This script doesn't work if your tabs begin with, or just have a date eg. 10/5/2020
Is there a way that this script can work with dates?
Thanks in advance.
Is there anyway to adapt this script to sort the sheets based on the value of a cell? For example I have a workbook with multiple sheets. There is a numerical value in cell P2 of every sheet. I wish to order the sheets in ascending order based on this value. I have been trying to adapt this script but keep banging my head on the wall. Any help would be appreciated!
It's not working for me. I copied the script verbatim, and when I run it within the script editor, I get execution started and execution completed notices, but the order of the sheet tabs doesn't change. How can I find out what's wrong?
Thank you!
perfect thank you!
That worked great - thank you. One follow up question. Is there a way to exclude tabs that you want to remain at the beginning? Or a way to freeze the current position of tabs?
Works like a charm. You made my day, thanks!
It's taking a white, but it's working! Thank you!
Excellent, thanks a lot
Awesome!
It's not working for me. I copied the script verbatim, and when I run it within the script editor, I get execution started and execution completed notices, but the order of the sheet tabs doesn't change. How can I find out what's wrong?
Did you ever get an answer for this? Mine is doing the same thing.
I also am looking for a unique solution. Your alphabetizer works like a dream for me and i thought i could figure out my own solution but every example of other examples were varied in their style. I understood all of them separately, but when i tried to combine them... the downward spiral.
I currently have about 60 sheets that I'm trying to organize by:
- The first five sheets will be in the same specific order every time. (I don't think i had any problem with that...)
- After that I would like the Blue tabs, then Orange, then Pink and finally Red, with...
- Each color group individually Alphabetized.
I had the first 5 sheets and two color groups working fine but i did something somewhere trying to wrap it up and destroyed al the progress i made. The more i tried to cobble it back together the worse it got until i finally realized i had spent hours just staring at the screen. I'm one of those stubborn people and I love to figure things out, but right now I'm that guy with just enough knowledge to be dangerous. I patiently await my opportunity to be guided to enlightenment.
Thank you!
anyway to sort them in descending alphabetical order?
Thank you!Yes, should work with reverse().
sheetNameArray.sort().reverse();
Thank you! Worked well