CODEWITHSUNDEEP
sortinggoogle-apps-scriptgoogle-sheets
Carly Bateman
Carly Bateman

Reputation: 25

How to sort multiple automatically google sheets

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

Answers (2)

Yuri Khristich
Yuri Khristich

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

pgSystemTester
pgSystemTester

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

Related Questions