Google表格脚本:在电子表格中按字母顺序对表格进行排序 [英] Google Sheets Script: Sort sheets alphabetically within a spreadsheet

问题描述

我遇到了一个旧的帮助表格，其中共享了脚本使用javascript在电子表格中整理工作表.脚本如下:

I came across an old help form where a script is shared to organize sheets within a spreadsheet using javascript. The script is as follows:

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 ) + ".";
}

运行脚本后，似乎已按字母顺序对几页进行了排序，但在滚动浏览已排序的页面后，有一堆尚未排序.前15个已排序，其次是21个未排序.

After running the script several pages seem to be sorted in alphabetical order but after scrolling through the sorted pages there are a bunch which haven't been sorted. The first 15 are sorted followed by 21 unsorted.

推荐答案

我意识到问题出在JavaScript的.sort()函数与小写字母区别对待的大写单词上.所以我修改了:

I realized that the problem was related to JavaScript's .sort() function treating capitalized words differently from lowercase. So I modified:

sheetName = sourceSheets[sheetNumber].getName();

成为:

sheetName = sourceSheets[sheetNumber].getName().toLowerCase();

进行此更改后，新排序的电子表格似乎可以使所有内容井然有序.

After making this change the new sorted spreadsheet seemed to have everything in order.

这篇关于Google表格脚本:在电子表格中按字母顺序对表格进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！