Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
/**
* This Google Sheets script keeps data in the specified column sorted any time
* the data changes.
*
* After much research, there wasn't an easy way to automatically keep a column
* sorted in Google Sheets, and creating a second sheet to act as a "view" to
* my primary one in order to achieve that was not an option. Instead, I
* created a script that watches for when a cell is edited and triggers
* an auto sort.
*
* To Install:
* 1. Open your Google Sheet.
* 2. Navigate to Tools > Script editor…
* 3. Copy and paste this script in the editor.
* 4. Change the three constants at the start of the code below to reflect
* your preferences.
* - Note: My goal is to move these settings to a GUI and have this script
* be installable as an add-on.
* 5. Give the script a name (e.g. "Keep Data Sorted") and hit save.
*
* To Use:
* Simply edit your Google Sheet like normal. Any time you edit data in your
* sort column (specified in `SORT_COLUMN_INDEX`), the script will re-sort
* your rows.
*
* If you are having trouble getting it to work, try the following in order:
* 1. Reload your spreadsheet.
* 2. Open the script editor (Tools > Script editor…), click the "Select
* function" dropdown, choose `onInstall`, and hit Debug (the bug icon
* that precedes the dropdown).
* 3. If that doesn't work, reach out via GitHub (link below) and ask for
* help. You may also find that others have run into the same issue
* and have already posted a solution.
*
* @author Mike Branski (@mikebranski)
* @link https://gist.github.com/mikebranski/285b60aa5ec3da8638e5
*
* @OnlyCurrentDoc Limits the script to only accessing the current spreadsheet.
*/
// The numeric index of the column you wish to keep auto-sorted. A = 1, B = 2,
// and so on.
var SORT_COLUMN_INDEX = 2;
// Whether to sort the data in ascending or descending order.
var ASCENDING = false;
// If you have header rows in your sheet, specify how many to exclude them from
// the sort.
var NUMBER_OF_HEADER_ROWS = 1;
// No need to edit anything below this line for general use.
// Make an improvement? Ping me on GitHub and let me know!
// Keep track of the active sheet.
var activeSheet;
/**
* Automatically sorts on the pre-defined column.
*
* @param {Sheet} sheet The sheet to sort.
*/
function autoSort(sheet) {
// Get the entire set of data for this sheet.
var range = sheet.getDataRange();
// Then, if there are any header rows, offset our range to remove them from
// it; otherwise, they will end up being sorted as well.
if (NUMBER_OF_HEADER_ROWS > 0) {
// Setting the second parameter of offset() to 0 to prevents it from
// shifting any columns. Note that row headers wouldn't make much
// sense here, but this is where you would modify it if you
// wanted support for those as well.
range = range.offset(NUMBER_OF_HEADER_ROWS, 0);
}
// Perform the actual sort.
range.sort( {
column: SORT_COLUMN_INDEX,
ascending: ASCENDING
} );
}
/**
* Triggers when a sheet is edited, and calls the auto sort function if the
* edited cell is in the column we're looking to sort.
*
* @param {Object} event The triggering event.
*/
function onEdit(event) {
var editedCell;
// Update the active sheet in case it changed.
activeSheet = SpreadsheetApp.getActiveSheet();
// Get the cell that was just modified.
editedCell = activeSheet.getActiveCell();
// Only trigger a re-sort if the user edited data in the column they're
// sorting by; otherwise, we perform unnecessary additional sorts if
// the targeted sort column's data didn't change.
if (editedCell.getColumn() == SORT_COLUMN_INDEX) {
autoSort(activeSheet);
}
}
/**
* Runs when the sheet is opened.
*
* @param {Object} event The triggering event.
*/
function onOpen(event) {
activeSheet = SpreadsheetApp.getActiveSheet();
autoSort(activeSheet);
}
/**
* Runs when the add-on is installed; calls onOpen() to ensure any initializion
* work is done immediately.
*
* @param {Object} event The triggering event.
*/
function onInstall(event) {
onOpen(event);
}
@mix3d
mix3d commented Feb 11, 2016

all the other SO forum posts never worked. This saves the day!

@doublevv

Hi Mike,

Your amazing script used to be working like a charm but suddendly i keep having an error "Service error: Spreadsheets" when I run onInstall function. Do you have any clue on what's not working?

Thanks a lot

@dmsig85
dmsig85 commented Jun 24, 2016

Is there a way to sort by a column then another?

I want the data in column 4 in order then of that result the data in column 10 sorted.

var SORT_COLUMN_INDEX = 4 (then 10);

Thanks in advance.

@JohnCRT
JohnCRT commented Aug 25, 2016

I have the same question as dmsig85, normally I have to sort 2 columns on a sheet by using DATA\Sort range then select "Data has header now" I select the header in "sort by" and select "Z -> A" then "then by" next header then "A -> Z".
The idea is to sort by date on the first selection, then sort by name on the second option. Is there a script I could add to yours?
Thank you!

@cheekygit

I also get "Service error: Spreadsheets". Hm... what to do

@BriceMilano

Would it be possible to also have this resort when a new row is entered? Not just when an existing cell is edited? (IE if this was pulling in information from Typeform via Zapier?

@kiel100
kiel100 commented Sep 7, 2016

Waiting for your answeeeerr pleaseee :)

@mikebranski
Owner

Whoa, I never received notifications for any of these comments!

@doublevv, @cheekygit That sounds like a permissions issue with your sheet or a service error of some kind from Google. Either way I just checked the script I'm using this on and it's still sorting and saving fine.

@dmsig85, @JohnCRT No idea on sorting by multiple columns, unfortunately. I haven't tried doing that, but I would just try calling range.sort() a second time with your second column.

@BriceMilano This was my first Google Script, but I'd imaging there's an event for when a new row is added. Check Google's documentation, but if one exists it should be able to be added to this script to work how you'd like.

@mix3d So glad you found it useful!

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