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 commented Feb 11, 2016

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

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 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 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!

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

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 commented Sep 7, 2016

Waiting for your answeeeerr pleaseee :)

Owner

mikebranski commented Mar 13, 2017

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!

@mikebranski, @dmsig85, @JohnCRT

Sorting multiple columns is possible, but calling this function more than once won't produce the desired results. It would only result in the table being sorted according to the last call.

range.sort( {
  column: SORT_COLUMN_INDEX,
  ascending: ASCENDING
} );

To sort on multiple columns would require a specialized function, such as this one.

range.sort( {
  {column: SORT_COLUMN_INDEX_1, ascending: ASCENDING},
  {column: SORT_COLUMN_INDEX_2, ascending: ASCENDING},
} );

@mikebranski

Thanks for this script!
Is there a way to specify which sheet should be sorted? My team shares a spreadsheet doc with multiple sheets within it for task management, and I don't want to mess with anyone else's sheets incase they have their own way of doing things.

Maybe I'm not running the script correctly, but is it possible to have the sorting take place only after that row is de-selected? Currently what's happening is I'm adding a row of data from left to right (the left-most cell being the one that I edit first, and the one that the sorting is anchored upon) and by the time I get to the last cell I want to edit/add to, the row gets sorted and "disappears" for another part of the sheet.

Hey,

I am getting an error when setting the column to be sorted to 5 (E). But my sheet has the timestamp column in E, so I need it sorted there.

The coordinates of the range are outside the dimensions of the sheet.

addrum commented Aug 10, 2017

fyi, for those interested in only sorting a range rather than the whole sheet, change:
var range = sheet.getDataRange();
to
var range = sheet.getRange("A1:B");
or your equivalent range

mrbenjaminryder commented Aug 23, 2017 edited

I've tried this on a very simple sheet and it works fine, but on something more complicated (with formulas and different sheets) it doesn't appear to work.
@mikebranski Is there any way you'd be able to give me a hand if I send you a link to the sheet in question?

eengebruiker commented Sep 9, 2017 edited

If 'SORT_COLUMN_INDEX' is in anyway the result of a formula, changes are not detected and the spreadsheet gets not (re)sorted. I can trigger the sorting function by editing the formula (netto not changing anything) but I would like to avoid this action.

So when I type numbers in 'SORT_COLUMN_INDEX' or produce them with a formula the sorting function works. But when the formula's automatically do their work (producing numbers in the column), it does not work.

Is there a solution for this problem?

André

Wondering how I could edit this to only look at 1 specific sheet instead of the activesheet? I only want to sort 1 sheet of a multi-sheet spreadsheet. Seems like it would have something to do with the getactivesheet() function, and changing this to the name of the sheet I would like the sort to apply to. any help would be appreciated. Thanks!!

dukeblue2017 commented Sep 13, 2017 edited

Thanks so much! It would be nice if there were an easy way to trigger the function other than just editing a cell. I Have formulae entered, so the cells are updating automatically, and therefore not triggering the sort.

This is great. Thank you for your contribution. Is there a way to have this sort script work by having to click on a button versus having the sort auto triggered by a cell entry? I am new to Google scripting so I apologize if what I'm asking for is something simple. Thank you again.

sdirghalli commented Sep 21, 2017 edited

Hello, I am sure my issues stem from the fact that I know very little about scripts...but I am trying to use one to automatically sort the contents of a GSheet that is populated from a GForm. I have copied the entire text of the script that mikebranski posted, but as that only accounts for the sorting of a single column, I have replaced the following portion:
range.sort( {
column: SORT_COLUMN_INDEX,
ascending: ASCENDING
} );
}

With this updated information from @IanAtkin:
range.sort( {
{column: SORT_COLUMN_INDEX_1, ascending: ASCENDING},
{column: SORT_COLUMN_INDEX_2, ascending: ASCENDING},
} );

With my desired sort criteria here is the actual edit I made to the script:
range.sort( {
{column: 4, ascending= true},
{column: 3, ascending= true},
{column: 2, ascending= true},
} );
}

But not matter what I input (I have one headder row and want to sort first by column B, then by Column A (ultimately I want to sort by three columns, but I'll get this working first) I get an error when trying to save the script. Any help that any of you can give will be greatly appreciated!

@sparzatka this post has a script that will create an additional menu item called "Sort" that will sort according to your specifications, but only when you click on the menu item, not automatically. The script is set up to sort according to multiple columns, but it'd be easy to modify it to sort only one if that's what you want. (full disclosure, I'm not the author of the script!)

https://gist.github.com/sco-tt/b3f07c1882ac698afc74

Owner

mikebranski commented Oct 4, 2017 edited

@IanAtkin You're right. I had the need for a dual sort and looked into it this week. I may play around with it some more and update the script.

@andrewkmin Totally get what you're saying. My original use case for this script wasn't as affected by this issue, but it's something I want to address now. I'm going to see if I can add a blur event listener when the targeted columns are modified and only sort then.

@dukeblue2017 You can modify which cell(s) triggers the sort, but as I said above I'd like to make that more flexible.

@sdirghalli You're receiving an error because you have invalid syntax.

// This:
range.sort( {
{column: 4, ascending= true},
{column: 3, ascending= true},
{column: 2, ascending= true},
} );
}

// Should be this:
range.sort( {
{column: 4, ascending: true},
{column: 3, ascending: true},
{column: 2, ascending: true},
} );
}

Thanks for the feedback everyone!

TypeError: Cannot call method "getDataRange" of undefined. (line 63, file "Code")

@mikebranski Thanks for this script, very helpful.

One issue: if NUMBER_OF_HEADER_ROWS is greater than 0, when I run the script I get this error:

The coordinates of the range are outside the dimensions of the sheet.

I fixed this by updating line 72 to the following:

range = range.offset(NUMBER_OF_HEADER_ROWS, 0, (range.getNumRows() - NUMBER_OF_HEADER_ROWS));

(cc @bsebastian86)

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