Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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}]);
}
@raymondkent

This comment has been minimized.

Copy link

@raymondkent raymondkent commented Jul 15, 2016

Thank you so much.

This is perfect scrip and works exactly as I wanted.

I was even able to figure out how to add another sort option with a sort process that has three sort criteria due to the excellent documentation within your script.

Thank you

@sco-tt

This comment has been minimized.

Copy link
Owner Author

@sco-tt sco-tt commented Aug 24, 2016

@raymondkent Very happy to hear that you found this useful!

@AnandMoram

This comment has been minimized.

Copy link

@AnandMoram AnandMoram commented Oct 8, 2016

Thanks based on this made sort for 7 cols & working fine

function MySort() {

var ColFirst = 1;
var ColSecnd = 8;
var ColThird = 7;
var ColForth = 9;
var ColFifth = 10;
var ColSixth = 4;
var ColSvnth = 13;
var HdrRows = 1;
// here "Progress" is the sheet I'm using
var sheet = SpreadsheetApp.getActive().getSheetByName("Progress");
var dataRange = sheet.getRange(HdrRows+1, 1, sheet.getMaxRows()-HdrRows, sheet.getLastColumn());

dataRange.sort([
{column: ColFirst, ascending: true},
{column: ColSecnd, ascending: true},
{column: ColThird, ascending: true},
{column: ColForth, ascending: false},
{column: ColFifth, ascending: true},
{column: ColSixth, ascending: true},
{column: ColSvnth, ascending: true},
]);

}

@richseidner

This comment has been minimized.

Copy link

@richseidner richseidner commented Oct 12, 2016

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

This comment has been minimized.

Copy link
Owner Author

@sco-tt 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

This comment has been minimized.

Copy link

@sdirghalli sdirghalli commented Sep 21, 2017

@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

This comment has been minimized.

Copy link

@MarcioCSantos MarcioCSantos commented Feb 17, 2018

An elegant script.Great!

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Mar 21, 2018

Works perfect for us. Thanks so much!

@Kwikiwi

This comment has been minimized.

Copy link

@Kwikiwi 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?

@ghost

This comment has been minimized.

Copy link

@ghost 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'  
}
@jecfarolan360

This comment has been minimized.

Copy link

@jecfarolan360 jecfarolan360 commented Feb 5, 2021

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