Arrange sheets by value Google Sheets

2019-08-02 01:52发布

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条回答
小情绪 Triste *
2楼-- · 2019-08-02 02:52

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.

查看更多
登录 后发表回答