@namby

Как в гугл таблице упорядочить (сортировать) листы в алфавитном порядке автоматически?

Кто может помочь с автоматической сортировкой листов в гугл таблице. Нужен какой то скрипт.
Находил в интернете но он почему то не работает.
Вот этот скрипт
function copyAllSheetsToAnotherSpreadsheetInAlphabeticalOrder() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceName = ss.getName();
var sourceSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var targetName = "Sheets alphabetized - Copy of " + sourceName;
var sheetNumber, sourceSheet, sheetName;
var sheetAlphaArray = new Array();

// create a new empty target spreadsheet
var targetSpreadsheet = SpreadsheetApp.create(targetName);
var targetUrl = targetSpreadsheet.getUrl();

// iterate through all sheets in the source spreadsheet to collect their names and numbers
for( sheetNumber = 0; sheetNumber < sourceSheets.length; sheetNumber++) {
sheetAlphaArray[sheetNumber] = new Array(2);
sheetName = sourceSheets[sheetNumber].getName();
// we will sort the array by the sheet name so it needs to be the first element
sheetAlphaArray[sheetNumber][0] = sheetName;
// need to keep track of sheet numbers so that we can find the sheets
// in alphabetical order from the sourceSheets array, place it in the second element
sheetAlphaArray[sheetNumber][1] = sheetNumber;
}

// sort the sheet names array in ascending alphabetic order
sheetAlphaArray.sort();

// iterate through all sheets in the source spreadsheet by sheet name in alphabetic order
//
// new sheets are always added to the first position, so the sheets need to be added
// last sheet first, first sheet last, otherwise they would appear in reverse order
for( sheetNumber = sourceSheets.length - 1; sheetNumber >= 0; sheetNumber-- ) {

// copy next sheet in reverse alphabetical order from the source spreadsheet to target spreadsheet
sourceSheet = sourceSheets[ (sheetAlphaArray[sheetNumber][1]) ];
sourceSheet.copyTo(targetSpreadsheet);
}

// done, tell user where to find the new spreadsheet
Browser.msgBox("Spreadsheet copied with sheets in alphabetical order. " +
"Target name: " + targetName + ". " +
"Target URL: " + targetUrl ) + ".";
}
  • Вопрос задан
  • 56 просмотров
Решения вопроса 1
idShura
@idShura
Ссылка на github

1. Copy/Paste the information below to the clipboard
2. Open the spreadsheet whose sheets need to be alphabetised
3. Choose Tools > Script editor > Blank (this opens a new tab in the browser)
4. Press Control+A followed by Control+V copy and paste the script in
5. Press Control+S to save the script
6. Choose Run > sortSheets
7. Go back to the spreadsheet tab to view the new sorted tab order

--Copy everything below this line--
function sortSheets () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNameArray = [];
  var sheets = ss.getSheets();
   
  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);
  }
}
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы