Skip to content

Instantly share code, notes, and snippets.

@sco-tt
Last active August 5, 2022 05:44
Show Gist options
  • Save sco-tt/b3f07c1882ac698afc74 to your computer and use it in GitHub Desktop.
Save sco-tt/b3f07c1882ac698afc74 to your computer and use it in GitHub Desktop.
This Google Apps Script sorts two columns. Variables can be modified to define what two columns should be sorted, and whether they should be ascending or descending.
/** Build a menu item
From https://developers.google.com/apps-script/guides/menus#menus_for_add-ons_in_google_docs_or_sheets
**/
function onOpen(e) {
var menu = SpreadsheetApp.getUi().createMenu('Sort');
if (e && e.authMode == ScriptApp.AuthMode.NONE) {
// Add a normal menu item (works in all authorization modes).
menu.addItem('Sort Sheet', 'sort');
} else {
// Add a menu item based on properties (doesn't work in AuthMode.NONE).
var properties = PropertiesService.getDocumentProperties();
var workflowStarted = properties.getProperty('workflowStarted');
if (workflowStarted) {
menu.addItem('Sort Sheet', 'sort');
} else {
menu.addItem('Sort Sheet', 'sort');
}
menu.addToUi();
}
}
function sort() {
/** Variables for customization:
Each column to sort takes two variables:
1) the column index (i.e. column A has a colum index of 1
2) Sort Asecnding -- default is to sort ascending. Set to false to sort descending
**/
//Variable for column to sort first
var sortFirst = 2; //index of column to be sorted by; 1 = column A, 2 = column B, etc.
var sortFirstAsc = true; //Set to false to sort descending
//Variables for column to sort second
var sortSecond = 3;
var sortSecondAsc = false;
//Number of header rows
var headerRows = 1;
/** End Variables for customization**/
/** Begin sorting function **/
var activeSheet = SpreadsheetApp.getActiveSheet();
var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
var range = sheet.getRange(headerRows+1, 1, sheet.getMaxRows()-headerRows, sheet.getLastColumn());
range.sort([{column: sortFirst, ascending: sortFirstAsc}, {column: sortSecond, ascending: sortSecondAsc}]);
}
Copy link

ghost commented Mar 21, 2018

Works perfect for us. Thanks so much!

@Kwikiwi
Copy link

Kwikiwi commented Mar 22, 2019

Works great for a single sheet, but is there a way to have it run for all the sheets in the file? Or, ideally, all but the first one?

Copy link

ghost commented Apr 8, 2019

@Kwikiwi

To run through all the sheets in a file, I'd try something like this:

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheets = [Insert sheet names here ex. 'Main', 'Detail', etc.];  
for (var i = 0; i < sheets.length; i++){  
  var s = ss.getSheetByName(i);  
  Insert sort or whatever else you need here and it will loop through each sheet specified in the array 'sheets'  
}

@lebroncho
Copy link

On google script, it doesn't sort automatically after you type. You need to manually run the sort function just to sort it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment