Skip to content

Instantly share code, notes, and snippets.

@mikebranski
Last active March 28, 2024 15:47
Show Gist options
  • Save mikebranski/285b60aa5ec3da8638e5 to your computer and use it in GitHub Desktop.
Save mikebranski/285b60aa5ec3da8638e5 to your computer and use it in GitHub Desktop.
/**
* 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);
}
@Landshark67
Copy link

Landshark67 commented Feb 20, 2023 via email

@luisagradeless
Copy link

@mikebranski - is there a way to have this sorting feature only apply to the first sheet in my spreadsheet? It is currently sorting the other spreadsheets as well and I can't figure out how it make it only apply to the first one (where it is working beautifully).

Thanks!

I'd love to know the answer to this question too. I have multiple sheets in a google doc and it ordered all of my sheets

@mikebranski
Copy link
Author

@mikebranski - is there a way to have this sorting feature only apply to the first sheet in my spreadsheet? It is currently sorting the other spreadsheets as well and I can't figure out how it make it only apply to the first one (where it is working beautifully).

Thanks!

@lwerickson3 @luisagradeless It's been a long while since I've done any Apps Script work, but something like this might work (untested):

var ss = SpreadsheetApp.getActiveSpreadsheet();
var first_sheet = ss.getSheets()[0];
var active_sheet = ss.getActiveSheet();

if (first_sheet.getSheetId() === active_sheet.getSheetId()) {
    // do stuff
}

@toddwhitewater
Copy link

Love your script, but I have attached a ton of tabs to my original list and I only want the code active on one tab, is there a way to easily manage the script to only actively sort on tab by name?

@codytveit
Copy link

I used this one on my event planning spreadsheet to automatically sort my google form responses by date - it has worked beautifully! I am wondering if there is a way to sort by date & time. I have the date, event start time & event end time within different columns. Thanks!

@Ljrobles16
Copy link

Is there a way to instead put the "completed" at the bottom of the sheet, put it in a different sheet instead?

@hokhatvong
Copy link

Tọa độ của phạm vi nằm ngoài kích thước của trang tính. - Xin hãy tư vấn.

Please add a row at the end and do not fill in anything, the error will be fixed

@KG5KUF
Copy link

KG5KUF commented Feb 29, 2024

it is only working when i refresh the page, what am i doing wrong?

@lesyphus
Copy link

Hi! I've been trying to figure this out for about an hour--for some reason the code is not being triggered. When I run the script it goes through, but nothing is happening on the sheet itself. Please help!

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