Sort Tabs Alphabetically keeping Certain Tabs first and ignoring Hidden Tabs

Question

I have a file that includes a variety of reference sheets and sheets that are only used at the end of the academic year plus a tab/sheet for individual students. Students come and go so I need the student tabs to be alphabetized. I've tried combining different pieces from various searches. The only thing that works for sure is hiding the ending tabs (last section below).

What I would like is the following:

Tabs that stay in positions 1-4: "Active Special Programs", "Demographics","Master","Blank"
Student Tabs next that are alphabetized by tab name
Another list of tabs that will specifically need to stay at the end of the list and hidden (typically they stay hidden but sorting makes them viewable again)

2nd Attempt ***EDIT - I found the mistake and got this work. Just need to combine this with the ability to hide the ending tabs. ***

function sheetNames() { var ss = SpreadsheetApp.getActiveSpreadsheet(); // Store all the worksheets in this array var sheetNameArray = []; var sheets = ss.getSheets(); for (var i = 0; i < sheets.length; i++) { sheetNameArray.push(sheets[i].getName()); } sheetNameArray.sort(); // Reorder the sheets. for( var j = 0; j < sheets.length; j++ ) { ss.setActiveSheet(ss.getSheetByName(sheetNameArray[j])); ss.moveActiveSheet(j + 1); } ["Active Special Programs","Demographics","Master","Blank"].forEach((name,i)=>{ ss.setActiveSheet(ss.getSheetByName(name)); ss.moveActiveSheet(i + 1); }) // Define the names of the sheets you want to move to the end var sheetsToMove = ["Compliance Charts", "Course List", "Completions","Read Me!","Read Me!!!","Roster w/ Links","Summer Enrichment","Transcript Template Transfer","Course Numbers","Course Completion Response","Data Pull","Dates","Definitions","Exams"]; // Replace with your sheet names sheetsToMove.forEach(function(sheetName) { var sheet = ss.getSheetByName(sheetName); if (sheet) { // Move the sheet to the last position ss.setActiveSheet(sheet); ss.moveActiveSheet(ss.getSheets().length); } else { Logger.log("Sheet '" + sheetName + "' not found."); } }); }

Hiding the ending tabs EDIT - This part works as expected, would like to combine it with the alpha/sort function

function organizeAndHideTabs() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheetsToMoveAndHide = ["Compliance Charts", "Course List", "Completions","Read Me!","Read Me!!!","Roster w/ Links","Summer Enrichment","Transcript Template Transfer","Course Numbers","Course Completion Response","Data Pull","Dates","Definitions","Exams"]; // Replace with your sheet names // Move the specified sheets to the end sheetsToMoveAndHide.forEach(sheetName => { const sheet = ss.getSheetByName(sheetName); if (sheet) { ss.setActiveSheet(sheet); // Activate the sheet to be moved ss.moveActiveSheet(ss.getSheets().length); // Move to the last position } }); // Hide the specified sheets sheetsToMoveAndHide.forEach(sheetName => { const sheet = ss.getSheetByName(sheetName); if (sheet && !sheet.isSheetHidden()) { // Check if not already hidden sheet.hideSheet(); } }); }

And the problem / error with each of the attempts is....??? Please edit your post and include that information. — devlin carnate
– devlin carnate, Commented Sep 30 at 20:18
I found the error while documenting the errors of the original posted codes. However, I want to combine the two scripts so the ending tabs are hidden. Also, is there a way for the sorting to ignore hidden sheets? Once a student leaves the district, I typically hide their sheet. Thanks. — Charity
– Charity, Commented Oct 1 at 15:08
"is there a way for the sorting to ignore hidden sheets" — Please do not present new requirements after you have already received an answer. Comment the answers you've been given. Ask only one question per post. Post a new question instead. — doubleunary
– doubleunary, Commented Oct 1 at 15:25

doubleunary · Accepted Answer · 2025-10-01 15:41:48Z

Use Array.includes() and a custom compareFn, like this:

/** * Sorts tabs in the active spreadsheet alphabetically, * moving certain tabs to the left end of the tab bar, and * moving certain other tabs to the end and hiding them. * * @param {SpreadsheetApp.Spreadsheet} ss Optional. The spreadsheet where to sort tabs. Defaults to the active spreadsheet. */ function sortTabs(ss = SpreadsheetApp.getActive()) { // version 1.4, written by --Hyde, 1 October 2025 // - see https://stackoverflow.com/a/79779540/13045193 ss.toast('Sorting tabs...'); const moveToStart = ['Active Special Programs', 'Demographics', 'Master', 'Blank',]; const moveToEnd = ['Compliance Charts', 'Course List', 'Completions', 'Read Me!', 'Read Me!!!', 'Roster w/ Links', 'Summer Enrichment', 'Transcript Template Transfer', 'Course Numbers', 'Course Completion Response', 'Data Pull', 'Dates', 'Definitions', 'Exams',]; const _compare = (a, b) => a < b ? -1 : a > b ? 1 : 0; const _sort = (a, b) => { if (moveToStart.includes(a)) return moveToStart.includes(b) ? 0 : -1; if (moveToStart.includes(b)) return moveToStart.includes(a) ? 0 : -1; if (moveToEnd.includes(a)) return moveToEnd.includes(b) ? 0 : 1; if (moveToEnd.includes(b)) return moveToEnd.includes(a) ? 0 : 1; return _compare(a, b); } const active = ss.getActiveSheet(); ss.getSheets() .map(sheet => sheet.getName()) .sort(_sort) .forEach((sheetName, index) => { const sheet = ss.getSheetByName(sheetName); ss.setActiveSheet(sheet); ss.moveActiveSheet(index + 1); if (moveToEnd.includes(sheetName)) sheet.hideSheet(); }); ss.setActiveSheet(active); ss.toast('Done.'); }

See Array.includes() and Array.sort().

Collectives™ on Stack Overflow

Sort Tabs Alphabetically keeping Certain Tabs first and ignoring Hidden Tabs

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related