Office Scripts – Sort sheets alphabetically

Office Scripts - Worksheets

In this post, we are covering how to sort sheets alphabetically using Office Scripts. Actually, the final code will be flexible enough to sort sheets in many different ways. I’ll share the basic script, along with the steps to create a more dynamic and robust solution. By the end, we will have a script that should meet most of your worksheet sorting requirements.

Since Office Scripts can be used with Power Automate, I will also give you a reusable script to use with your flows.

If you’re up for that, let’s get started.

Example Office Script

Let’s begin with a basic example. The code below sorts worksheets using the character code set.

function main(workbook: ExcelScript.Workbook) {

  //Script variables
  let wsArr = workbook.getWorksheets();
  let wsNameArr: string[] = [];

  //Get Sheet names into an array
  for (let i = 0; i < wsArr.length; i++) {
    wsNameArr.push(wsArr[i].getName())
  }

  //Sort the array of worksheet names
  wsNameArr.sort();

  //Move the worksheets to the order of the array
  for (let i = 0; i < wsNameArr.length; i++) {
    workbook.getWorksheet(wsNameArr[i]).setPosition(i);
  }

};

The code has 4 main sections:

  1. Creates the variables necessary to run the code:
    • An array of worksheets called wsArr
    • An empty array called wsNameArr, which will hold the worksheet names
  2. Loop through the wsArr array and add the worksheet names into the wsNameArr array created above
  3. Sort the wsNameArr array using the basic sort() method
  4. Loop through the wsNameArr and set the worksheet order to be the same as the array

In many circumstances, this simple code will work perfectly fine. However, there are a few issues we may come across:

  • Sorting numbers – All worksheet names are text strings meaning they are sorted character by character. If we had sheets named “1”, “2”, and “12”, they would be sorted as “1”, “12” and “2”. Which, if we wanted a numerical sort, will provide the wrong order for our needs.
  • Upper and lower case characters – Upper and lower case letters are treated differently when sorting in character code order. For example, numbers are first in the character set, followed by upper case and lower case. Therefore, if we want to sort alphabetically, we need to find a way to ignore the case.
  • Reverse order – Sometimes we want to display the items in reverse order.

Code enhancements

Let’s look at each of the enhancements and add them to our code to create a more usable script.

Reverse order

To get an array into reverse sorted order, it is a two-step process:

  1. Use the sort() method to get the array into natural sort order
  2. Use the reverse() method to reverse the order of the array

The necessary changes have been made to the code below in bold.

function main(workbook: ExcelScript.Workbook) {

  //Option variables
  let reverseSort = true

  //Script variables
  let wsArr = workbook.getWorksheets();
  let wsNameArr: string[] = [];

  //Get Sheet names into an array
  for (let i = 0; i < wsArr.length; i++) {
    wsNameArr.push(wsArr[i].getName())
  }

  //Sort the array of worksheet names
  wsNameArr.sort();

  //If reverseSort is true then reverse the order of the array
  if (reverseSort == true) {
    wsNameArr.reverse();
  }

  //Move the worksheets to the order of the array
  for (let i = 0; i < wsNameArr.length; i++) {
    workbook.getWorksheet(wsNameArr[i]).setPosition(i);
  }

};

To sort in natural order, set the reverseSort variable to false.

let reverseSort = false;

Or to sort in reverse order, set the reverseSort variable to true.

let reverseSort = true;

Upper and lower case characters

To get an alphabetical sort that treats upper and lower case characters the same, we need to ignore the case.

The approach we will take is this:

  1. Create a 2D array of worksheet names:
    • 1st dimension will hold the original sheet name
    • 2nd dimension will hold the upper case version of the name; This makes the case the same for all elements.
  2. Sort the array using the 2nd dimension
  3. Apply the sort order to the sheets

Because we are using a 2D array we need to use a more advanced version of the sort() method.

 wsNameArr.sort((a, b) => {
    if (a[1] > b[1]) return 1;
    if (a[1] < b[1]) return -1;
    return 0;
  });

This goes through each element in the array taking the value and the following value (a and b) and determines if:

  • a > b then return 1 – which means the second element (b) should come before the first element (a)
  • a < b then return -1 – which means the first element (a) should come before the second element (b)
  • a = b then return 0 – the values are the same and no change in position occurs

Our new script looks like this (changes in bold)

function main(workbook: ExcelScript.Workbook) {

  //Option variables
  let reverseSort = false;
  let ignoreCase = true;

  //Script variables
  let wsArr = workbook.getWorksheets();
  let wsNameArr: string[] = [];

  //Get Sheet names into an array
  //wsNameArr[0] = Actual Name (does not change)
  //wsNameArr[1] = Transformed Name (to perform transformations on)
  for (let i = 0; i < wsArr.length; i++) {
    wsNameArr.push([wsArr[i].getName(), wsArr[i].getName()])
  }

  //If ignoreCase then covert 2nd array value to upper case
  if (ignoreCase == true) {
    for (let i = 0; i < wsArr.length; i++) {
      wsNameArr[i][1] = wsNameArr[i][1].toUpperCase();
    }
  } 

  //Sort the array using the 2nd element of the wsNameArr
  wsNameArr.sort((a, b) => {
    if (a[1] > b[1]) return 1;
    if (a[1] < b[1]) return -1;
    return 0;
  });

  //If reverseSort is true then reverse the order of the array
  if (reverseSort == true) {
    wsNameArr.reverse();
  }

  //Move the worksheets to the order of the array
  for (let i = 0; i < wsNameArr.length; i++) {
    workbook.getWorksheet(wsNameArr[i][0]).setPosition(i);
  }

};

In the script above, to apply the standard code number sort, set the ignoreCase variable to false.

let ignoreCase = false;

Or to sort in alphabetical order, regardless of case, set the ignoreCase variable to true.

let ignoreCase = true;
100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Sorting numbers

There is one more enhancement we want to make; the ability to sort numbers as numbers.

As stated above, all worksheet names are text strings, which are sorted on a left to right assessment. As a result, sheets named “1”, “2” and “12”, would be sorted as “1”, “12”, “2”. To sort these correctly we need to convert the text string into a number prior to sorting.

To convert a text string to a number, we can use the ParseInt() function for integers or ParseFloat() function for decimals.

In the code, we then evaluate the result of the ParseInt() or ParseFloat() functions to see if the result is not a number, using the isNaN() function.

Our script now contains the following (changes in bold):

function main(workbook: ExcelScript.Workbook) {

  //Option variables
  let reverseSort = false;
  let ignoreCase = true;
  let convertNumbers = true;

  //Script variables
  let wsArr = workbook.getWorksheets();
  let wsNameArr: string[] = [];

  //Get Sheet names into an array
  //wsNameArr[0] = Actual Name (does not change)
  //wsNameArr[1] = Transformed Name (to perform transformations on)
  for (let i = 0; i < wsArr.length; i++) {
    wsNameArr.push([wsArr[i].getName(), wsArr[i].getName()])
  }

  //If ignoreCase then covert value to upper case before sorting
  if (ignoreCase == true) {
    for (let i = 0; i < wsArr.length; i++) {
      wsNameArr[i][1] = wsNameArr[i][1].toUpperCase();
    }
  } 

  //If convertNumbers then convert anything which is a number to a number
  if (convertNumbers == true) {
    for (let i = 0; i < wsNameArr.length; i++) {
      if (isNaN(parseFloat(wsNameArr[i][1])) == false) {
        wsNameArr[i][1] = parseFloat(wsNameArr[i][1]);
      }
    }
  } 

  //Sort the array using the 2nd element of the wsNameArr
  wsNameArr.sort((a, b) => {
    if (a[1] > b[1]) return 1;
    if (a[1] < b[1]) return -1;
    return 0;
  });

  //If reverseSort is true then reverse the order of the array
  if (reverseSort == true) {
    wsNameArr.reverse();
  }

  //Move the worksheets to the order of the array
  for (let i = 0; i < wsNameArr.length; i++) {
    workbook.getWorksheet(wsNameArr[i][0]).setPosition(i);
  }

};

In the script above, to sort numbers as numbers, set the convertNumbers variable to true.

let convertNumbers = true;

Or to sort numbers as text, set the convertNumbers variable to false.

let convertNumbers = false;

Using the script

The final script in the subsection above contains all three enhancements which enable us to:

  • Reverse the order
  • Sort numbers
  • Ignore the case

These options should meet most requirements which we are likely to need. However, there will always be scenarios where somebody wants to sort by specific characters within the text string. While we have not covered this, the principles above should provide a good starting point for creating a script for that specific purpose.

Reusable Office Script for Power Automate

To use this Office Script within Power Automate we can make some further changes so that we pass the three options to the script at run-time. This will make our script completely reusable and can be called repeatedly but with different parameters.

To make a reusable code block for Power Automate we simply move the option variables into the function parameters (bold in the code below).

function main(workbook: ExcelScript.WorkbookreverseSort: boolean,
  ignoreCase: boolean, convertNumbers: boolean) {

  //Script variables
  let wsArr = workbook.getWorksheets();
  let wsNameArr: string[] = [];

  //Get Sheet names into an array
  //wsNameArr[0] = Actual Name (does not change)
  //wsNameArr[1] = Transformed Name (to perform transformations on)
  for (let i = 0; i < wsArr.length; i++) {
    wsNameArr.push([wsArr[i].getName(), wsArr[i].getName()])
  }

  //If ignoreCase then covert value to upper case before sorting
  if (ignoreCase == true) {
    for (let i = 0; i < wsArr.length; i++) {
      wsNameArr[i][1] = wsNameArr[i][1].toUpperCase();
    }
  }

  //If convertNumbers then convert anything which is a number to a number
  if (convertNumbers == true) {
    for (let i = 0; i < wsNameArr.length; i++) {
      if (isNaN(parseFloat(wsNameArr[i][1])) == false) {
        wsNameArr[i][1] = parseFloat(wsNameArr[i][1]);
      }
    }
  }

  //Sort the array using the 2nd element of the wsNameArr
  wsNameArr.sort((a, b) => {
    if (a[1] > b[1]) return 1;
    if (a[1] < b[1]) return -1;
    return 0;
  });

  //If reverseSort is true then reverse the order of the array
  if (reverseSort == true) {
    wsNameArr.reverse();
  }

  //Move the worksheets to the order of the array
  for (let i = 0; i < wsNameArr.length; i++) {
    workbook.getWorksheet(wsNameArr[i][0]).setPosition(i);
  }

};

To use the script above with Power Automate, the step will look similar to the following:

Power Automate step to sort worksheets

The Script Parameters passed in Power Automate are all Yes or No, but in our script they are boolean, which have true or false values.  Which is slightly confusing, but it’s easy enough to remember:

  • Yes = true
  • No = false

Related pages which may be useful to you are:



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *