如何排序多个自动谷歌表

Question

初学者尝试使用脚本，请帮助!

我需要一种方法来自动排序在同一工作簿不同的工作表上的多列。我目前使用的脚本只排序一个表。

const SHEET_NAME = "History";
const SORT_DATA_RANGE = "A2:Y";
const SORT_ORDER = [
{column: 25, ascending: true},
{column: 17, ascending: true}, 
{column: 1, ascending: true}
];
function HistorySort(e){
multiSortColumns();
}
function multiSortColumns(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(SHEET_NAME);
var range = sheet.getRange(SORT_DATA_RANGE + sheet.getLastRow()); // change here
range.sort(SORT_ORDER);
ss.toast('Sort complete.');
}

Answer 1

function sort_sheets() {
var sorts = [
{sheet: 'Sheet1', range:'a2:d', cols: [1, 2, 3]},
{sheet: 'Sheet2', range:'b2:f', cols: [2, 3]},
{sheet: 'Sheet3', range:'a2:e', cols: [4, 5]},
];
var ss = SpreadsheetApp.getActiveSpreadsheet();
for (var sort of sorts) {
var sheet = ss.getSheetByName(sort.sheet);
var range = sheet.getRange(sort.range + sheet.getLastRow());
var sort_order = [];
for (var col of sort.cols) {
sort_order.push({column: col, ascending: true})
}
range.sort(sort_order);
}
ss.toast('Sort complete.');
}

如果所有表的范围相同，则可以更简单。

Answer 2

下面是一种循环遍历并排序的方法。但是，如果要排序的列在每个工作表中是不同的，则必须在每个工作表中指定。

//SHEET_NAME = "History";
var SORT_DATA_RANGE = "A2:Y";
var SORT_ORDER = [
{column: 25, ascending: true},
{column: 17, ascending: true}, 
{column: 1, ascending: true}
];
function sortAllSheets() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var allSheets = ss.getSheets();
allSheets.forEach(multiSortColumns);
ss.toast('Sort complete.');
}

function multiSortColumns(sheet){
//you could put some exceptions in here for sheets you might want to skip
var range = sheet.getRange(SORT_DATA_RANGE + sheet.getLastRow()); // change here
range.sort(SORT_ORDER);
}