Arrange sheets by value Google Sheets

1
votes

Is there a function to reorder the tabs in Google Sheets? We'd like to arrange them on a specific value obtained on each of them, 

  /** ... */
  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++) {
    z = sheets[i].getRange(3, 5);
    z = z.getValue();

    sheetNameArray.push(z);
  }
  /** Use value in array to reorder sheets */
1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

You want to reorder the sheets in the opened spreadsheet using the sheet names in sheetNameArray. If my understanding is correct, how about this sample script? This sample script reorders sheets in the opened spreadsheet using moveActiveSheet().

Sample script :

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetNameArray = [];
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
  z = sheets[i].getRange(3, 5);
  z = z.getValue();
  sheetNameArray.push(z);
}
sheetNameArray.forEach(function(e, i){
  ss.setActiveSheet(ss.getSheetByName(e));
  ss.moveActiveSheet(i+1);
});

Note :

  • It supposes that your script can retrieve the sheet names to sheetNameArray.
  • The sheet names in sheetNameArray are required to be included in the opened spreadsheet.
  • The length of sheetNameArray is required to be the same with the length of var sheets = ss.getSheets();.

Reference :

If I misunderstand your question, I'm sorry.