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}]);
}
@richseidner
Copy link

Hi sco-tt

I am Google Script newbie, and I ma able to use your sort code within the current Active sheet, but I can't figure out how to change your code to sort a separate sheet ("testsheet"), which is not the current active sheet. I've tried modifying your code, but to no avail. Your help will be greatly appreciated. Thanks. How do I change these lines?

var activeSheet = SpreadsheetApp.getActiveSheet();
var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);

Rich

@sco-tt
Copy link
Author

sco-tt commented Jun 27, 2017

@rich see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getSheetByName(String)

I would try something like:

var activeSheet = SpreadsheetApp.getActiveSheet();
var sheetName = "testsheet";
var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);

@sdirghalli
Copy link

@sco-tt

First off, your script works great, I've now got my extra sort menu and I can quickly sort the sheet without having to select all the data, then select "sort range" etc. Thank you!

I have two questions. First, as a teacher with many forms that generate spreadsheets, how/is it possible to have this script automatically applied to all my spreadsheets? Second if that's not possible (or even if it is) how can I modify the script so that the sorting is done automatically as the sheet is populated from the form?

Thanks for your help!

@MarcioCSantos
Copy link

An elegant script.Great!

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