0

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?

3

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

0

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...)

0

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

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

before

After

after

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.