Reputation: 25
Beginner trying to use scripts, please help!
I am needing a way to automatically sort multiple columns on different sheets in the same workbook. The current script I am using is only sorting one sheet.
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.');
}
Upvotes: 1
Views: 608
Reputation: 14537
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.');
}
It can be even simpler if the range is the same for all the sheets.
Upvotes: 1
Reputation: 9932
Here's a way to loop through all of them and sort. However, if your columns to sort on are different in each sheet, you'll have to specify that on each one.
//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);
}
Upvotes: 1