How to sort multiple automatically google sheets

Question

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

Are they all the same parameters for sorting?

pgSystemTester
– pgSystemTester

2021-08-28 00:01:42 +00:00
Commented Aug 28, 2021 at 0:01 — pgSystemTester
– pgSystemTester, Commented Aug 28, 2021 at 0:01
No, they are not.

Carly Bateman
– Carly Bateman

2021-08-28 00:05:48 +00:00
Commented Aug 28, 2021 at 0:05 — Carly Bateman
– Carly Bateman, Commented Aug 28, 2021 at 0:05

Yuri Khristich · Accepted Answer · 2021-08-28 10:55:59Z

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

It can be even simpler if the range is the same for all the sheets.

edited Aug 28, 2021 at 10:55

answered Aug 28, 2021 at 6:16

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pgSystemTester · Accepted Answer · 2021-08-28 00:07:20Z

1

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);
}

answered Aug 28, 2021 at 0:07

pgSystemTester

10.1k2 gold badges28 silver badges59 bronze badges

2 Comments

Carly Bateman Over a year ago

Yes, different sheets have different columns to sort. I see how the code you posted would work if all columns on all sheets were the same, however, since this is not the case, how/what would I need to change?

Cooper Over a year ago

If I wanted to do this I would store all of the settings for all of the Spreadsheet/Sheets in one Spreadsheet and then read that table to to create an object of settings for each sheet in a spreadsheet. Obviously, this will take a lot of time and effort organizing it and maintaining it so I wouldn't want to do it for nothing unless I wanted to do it for myself and I don't want to do it for myself.

Collectives™ on Stack Overflow

How to sort multiple automatically google sheets

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related