How to Sort Sheets and Create an Index (with Google Apps Script)

One thing I had to do recently was to sort a lot of tabs in a Google Sheets document and create an index.

The document had hundreds of tabs and it had gotten out of control — moving them around by hand to sort them alphabetically seemed very tedious. So I wrote a script to do it.

The second thing I wanted to do was to create an index with links to every tab. Another thing I could do automatically.

Finally, I decided to add them both to a menu!

Here’s how I did it all.

General disclaimer: I’m not actually a programmer, I’m a more traditional engineer; I tend to make things that are functional but not beautiful. If you have a more elegant suggestion, I would welcome a friendly discussion.

Google Apps Script code to Sort Sheets Alphabetically

The first function I created would sort all the sheets.

It works in three parts:

1. Getting all the names of the sheets
2. Sorting them
3. Setting their positions one by one

// Function to sort sheets alphabetically

function sortSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheetNames = [];
  for (var i = 0; i < sheets.length; i++) {
    sheetNames.push(sheets[i].getName());
  }
  sheetNames.sort();
  for (var i = 0; i < sheetNames.length; i++) {
    ss.setActiveSheet(ss.getSheetByName(sheetNames[i]));
    ss.moveActiveSheet(i + 1);
  }
}

Google Apps Script code to Create an Index

The second function is to create an index.

This function creates (or deletes then creates) a sheet called “Index”. Then it adds rows one by one – as long as they’re not the ‘Index’. tab.

function createIndexSheet() {
  var ss = SpreadsheetApp.getActive();
  var indexSheet = ss.getSheetByName('Index');
  if (indexSheet) {
    ss.deleteSheet(indexSheet);
  }
  indexSheet = ss.insertSheet('Index',0);
  
  var numSheets = ss.getNumSheets();
  var sheets = ss.getSheets();
  var sheetName, sheetId;
  
  for (var i = 0; i < numSheets; i++) {
    sheetName = sheets[i].getSheetName();
    sheetId = sheets[i].getSheetId();
    
    if (sheetName != 'Index') {
      indexSheet.appendRow(['=HYPERLINK("#gid='+sheetId+'", "'+sheetName+'")']);
    }
  }
}

This worked quite well!

GAS code to create a menu

Finally, I wanted to add the above functions to one menu.

function onOpen() {
  var sheet = SpreadsheetApp.getActive();
  var entries = [ {
    name : "Sort Sheets Alphabetically",
    functionName : "sortSheets"
  }, {
    name : "Create Index Sheet",
    functionName : "createIndexSheet"
  } ];
  sheet.addMenu("Sheetsnerd", entries);
}