3 Easy Ways to Sort Sheets in Google Sheets

Do you have a Google spreadsheet with a lot of sheets?

Finding a particular sheet can become tricky as the number of sheets increases. Keeping your sheets in order can make this task much quicker.

This article will show you 3 easy ways you can use to sort your sheets in Google Sheets.

  1. Click and drag sheets.
  2. Move sheets left or right.
  3. Sort sheets in ascending, descending or random order with apps scripts.

Make sure to get the example file to try these methods out on your own.

Now, let’s find out how sorting your sheets is done!

Sort Sheets Manually Click and Drag Method.

Sorting sheets manually is a drag, quite literally! 🤣

You can click and drag any sheet left or right to change where it appears in relation to other sheets within your spreadsheet document.

Left-click on and sheet and hold the mouse button while you drag the sheet either left or right. Release the mouse button to place the sheet in the new location.

This is a great method to use if you only need to change the order of one or two sheets.

You can use this method to order all your sheets into any custom order you need, it’s just going to take some time!

Sort Sheets Manually with the Move Righ and Left Command

There is another way to sort your sheets manually. You can use the commands found in the right-click menu.

There are two options here which allow you to move a sheet either one place to the left or one place to the right.

Follow these steps to move a sheet one place to the left or right.

  1. Right click on the sheet which you would like to move.
  2. Choose Move right or Move left from the options in the menu.

Note: This will only move the sheet one place over, so it’s not a very practical options when sorting all your sheets. It would be been better if the options were to move the sheet to the start or end of the sheet tabs.

Sort Sheets with an Apps Script

Google Sheets offers a wide range of functionality, covering almost any data processing need. However, it doesn’t have a command to sort sheets in alphabetical order.

As you add more sheets into your workbook, sorting them can become a critical task and time-consuming organizational task.

But Google Sheets does allow you to write custom functions in Apps Scripts that can be used directly from the main menu as if they were already built-in!

This can be used to build your own functionality to automate complex and repetitive tasks such as sorting your sheets. This way you don’t have to do it manually every time!

Go to the Tools menu and select the Script editor option to open the apps script editor.

function sortSheetsAsc() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheetNameArray = [];

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

function sortSheetsDesc() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheetNameArray = [];

  for (var i = 0; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
  }

  sheetNameArray.sort().reverse();

  for( var j = 0; j < sheets.length; j++ ) {
    ss.setActiveSheet(ss.getSheetByName(sheetNameArray[j]));
    ss.moveActiveSheet(j + 1);
  }
}

function sortSheetsRandom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheetNameArray = [];

  for (var i = 0; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
  }

  sheetNameArray.sort().sort(() => (Math.random() > .5) ? 1 : -1);;

  for( var j = 0; j < sheets.length; j++ ) {
    ss.setActiveSheet(ss.getSheetByName(sheetNameArray[j]));
    ss.moveActiveSheet(j);
  }
}

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Sort Sheets A ➜ Z', functionName: 'sortSheetsAsc'},
    {name: 'Sort Sheets Z ➜ A', functionName: 'sortSheetsDesc'},
    {name: 'Randomize Sheet Order 🎲', functionName: 'sortSheetsRandom'}
  ];
  spreadsheet.addMenu('Sheet Tools', menuItems);
}

Paste the above code into the editor and press the save icon.

This code has four functions.

  • sortSheetsAsc will sort all the sheets in ascending order from A to Z based on the sheet names.
  • sortSheetsDesc will sort all the sheets in descending order from Z to A based on the sheet names.
  • sortSheetsRandom will randomize the order of the sheets.
  • onOpen will automatically run every time you open this document. It will add a new menu item called Sheet Tools into the main menu which will allow you to run the other functions from the main UI.

Note: In order to see the Sheet Tools menu, you will need to refresh the Google Sheet.

This is a great way to automate the organization of your sheets! You could also increase navigational efficiency by inserting hyperlinks to important sheets for another productivity boost.

Sort Sheets in Ascending Order

Now sorting your sheets is as simple as selecting the Sheet Tools menu, then choosing Sort Sheets A to Z to run the sortSheetsAsc function.

The apps script will automatically start sorting your sheets in ascending order based on the sheet names!

It’s that easy!

This is so useful when you have a lot of sheets and need them sorted quickly. Just let Google handle it for you!

Sort Sheets in Descending Order

You can also sort sheets in descending order by selecting the Sheet Tools menu again and choosing Sort Sheets Z to A to run the sortSheetsDesc function.

It’s just so easy with Google Sheets Apps Scripts!

Sort Sheets in Random Order

Sorting in alphabetical order is great, but you can also use apps scripts to get rid of any order by sorting your sheets randomly.

Just select the Sheet Tools menu and choose Randomize Sheet Order from the list, then google will put your sheets into a random order!

This is a lot easier than adding a column of random numbers to your data and then sorting it in ascending order.

How cool is that?

Conclusions

There’s nothing more frustrating than opening up Google spreadsheet and finding it full of sheet tabs in an unordered state. This makes it hard to navigate.

By following these steps, you can go from a disorganized document to an orderly one in no time.

Apps scripts make it even easier to sort your sheets. With the ability to sort your sheets into alphabetical order or to randomizing them, you’ll be able to quickly reorganize your document so its easier for everyone to use!

Do you sort your sheets? Let me know in the comments below!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps, and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published.

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!