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.
Table of Contents
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:
- 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
- Loop through the wsArr array and add the worksheet names into the wsNameArr array created above
- Sort the wsNameArr array using the basic sort() method
- 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:
- Use the sort() method to get the array into natural sort order
- 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:
- 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.
- Sort the array using the 2nd dimension
- 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;
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.Workbook, reverseSort: 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:
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:
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.