How to quickly re-arrange sheet order in Google Sheets?

Question

If I have a Google Sheets document (Workbook in Excel terms) with 20 sheets (Worksheet in Excel terms) and I want to rearrange the order of the sheets currently the only way I know to do this is to drag them horizontally.

This works fine when you have a few sheets, but with 20 or more sheets its very slow if I want to take sheet 20 and put it next to sheet 1. Ive also noticed that if you right click a sheet you have the option to move it left or right, but this only moves in one sheet "slot" at a time, so no quicker than the dragging method.

Is there a quicker way to do this than just dragging a sheet horizontally along the bottom bar?

Answer 1

You could use Google Apps Script to reorder the sheets. First you should activate the sheet to move, either manually or by the script, then use the moveActiveSheet(position) method of the Class Spreadsheet.

Example

This moves the active sheet to the position 1

function moveSheetToPos1(){
  SpreadsheetApp.getActiveSpreadsheet().moveActiveSheet(1);
}

Related

• Anyway to change the sheet order? (Stack Overflow)

Answer 2

Here is the Apps Script :

/* Credit: https://gist.github.com/chipoglesby/26fa70a35f0b420ffc23 */

function sortGoogleSheets() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Store all the worksheets in this array
  var sheetNameArray = [];
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
  }

  sheetNameArray.sort();

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

Answer 3

The answer by @UtkarshSharma will reorder sheet names based on the Javascript array sort() method - as if all sheet names were strings. This is fine if your sheet names are to be sorted in that order, but what if your defined sheet order is different. Some small adjustments to that script will allow sheets to be re-sorted in your defined order.

• Declare an array mysheetOrder containing your required sheet name sort order. This does not even have to identify every sheet in the spreadsheet, just those sheets that are important to appear in the first in the list of sheets (left from to right in the Sheets).
• Delete the temporary sheetNameArray
• Simply reorder the sheets based on mysheetOrder

---

function reordersheets() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // make a list of the sheets in your deferred sort order
  // it is NOT necessary to list every every sheet in the Spreadsheet, 
  // just the ones that are import to list from left to right
  var mysheetOrder = ["Sites","Site names","AA","AA names","BB","BB names"]
 
  // get the actual sheets
  var sheets = ss.getSheets()
 
  // Reorder the sheets.
  for( var j = 0; j < mysheetOrder.length; j++ ) {
    ss.setActiveSheet(ss.getSheetByName(mysheetOrder[j]));
    ss.moveActiveSheet(j + 1);
  }

}

---

Before

After

Answer 4

No, this is the only way to shuffle the individual sheets on the sheet bar.

_{Note: maybe this would be possible with a script which will randomly shuffle sheets' gids based on a pre-defined trigger (not sure if...)}

Answer 5

For anyone who is looking for a way to move a sheet to the far left or far right, or anywhere without adding a script to your sheet. I built an add-on to do so: https://workspace.google.com/marketplace/app/sheet_mover/797282136202