N
N
namby2021-09-22 10:27:40
Google Sheets
namby, 2021-09-22 10:27:40

How to arrange (sort) sheets alphabetically in Google spreadsheet automatically?

Who can help with the automatic sorting of sheets in a Google spreadsheet. Some kind of script is needed.
Found on the Internet but for some reason it does not work.
Here is the script
function copyAllSheetsToAnotherSpreadsheetInAlphabeticalOrder() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceName = ss.getName();
var sourceSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var targetName = "Sheets alphabetized - Copy of " + sourceName;
var sheetNumber, sourceSheet, sheetName;
var sheetAlphaArray = new Array();

// create a new empty target spreadsheet
var targetSpreadsheet = SpreadsheetApp.create(targetName);
var targetUrl = targetSpreadsheet.getUrl();

// iterate through all sheets in the source spreadsheet to collect their names and numbers
for( sheetNumber = 0; sheetNumber < sourceSheets.length; sheetNumber++) {
sheetAlphaArray[sheetNumber] = new Array(2);
sheetName = sourceSheets[sheetNumber].getName();
// we will sort the array by the sheet name so it needs to be the first element
sheetAlphaArray[sheetNumber][0] = sheetName;
// need to keep track of sheet numbers so that we can find the sheets
// in alphabetical order from the sourceSheets array, place it in the second element
sheetAlphaArray[sheetNumber][1] = sheetNumber;
}

// sort the sheet names array in ascending alphabetic order
sheetAlphaArray.sort();

// iterate through all sheets in the source spreadsheet by sheet name in alphabetic order
//
// new sheets are always added to the first position, so the sheets need to be added
// last sheet first, first sheet last, otherwise they would appear in reverse order
for( sheetNumber = sourceSheets.length - 1; sheetNumber >= 0; sheetNumber-- ) {

// copy next sheet in reverse alphabetical order from the source spreadsheet to target spreadsheet
sourceSheet = sourceSheets[ (sheetAlphaArray[sheetNumber][ one]) ];
sourceSheet.copyTo(targetSpreadsheet);
}

// done, tell user where to find the new spreadsheet
Browser.msgBox("Spreadsheet copied with sheets in alphabetical order. " +
"Target name: " + targetName + ". " +
"Target URL: " + targetUrl ) + ".";
}

Answer the question

In order to leave comments, you need to log in

1 answer(s)
I
idShura, 2021-09-22
@namby

Link to github

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);
  }
}

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question