Home > database >  Google App Script: AutoSort Function with range on selected sheet
Google App Script: AutoSort Function with range on selected sheet

Time:06-18

The result I am trying to fulfil is to autosort the whole row based on date in an ascending order including the new entry. Right now I am only able to apply to 1 of the GSheet tab, I am trying to apply to other selected sheets tab but couldn't find a way. Any solution please? Thank you!

SHEET_NAME = "Sheet1";
SORT_DATA_RANGE = "A2:Z";
SORT_ORDER = [
{column: 3, ascending: true}, // 3 = column number, sort by ascending order  
// {column: 3, ascending: false}, // 3 = column number, sort by decending order  
];


//Note pop-up appear when Sorting is completed
function onEdit(e){
  multiSortColumns();
}
function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
  ss.toast('Sort action executed.');
}

CodePudding user response:

To sort all sheets by specified range and options, try:

function multiSortColumns() {
  
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()

  spreadsheet.getSheets()
             .forEach(sheet => sheet.getRange(SORT_DATA_RANGE).sort(SORT_ORDER))

  spreadsheet.toast('Sort action executed.')

}

To sort specific sheets, try:

function multiSortColumns() {
  
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const targetSheets = [`Sheet1`, `Sheet2`, `Sheet3`]

  targetSheets.forEach(sheetName => {
    spreadsheet.getSheetByName(sheetName)
               .getRange(SORT_DATA_RANGE)
               .sort(SORT_ORDER)
  })

  spreadsheet.toast('Sort action executed.')

}
  • Related