Skip to content

Instantly share code, notes, and snippets.

@mikebranski
Last active April 1, 2024 17:37
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);
}
@Friesian7
Copy link

For anyone struggling I did too. I am in college for computer science and found a different way!! First my thing is for an assignment tracker so my solution is based with that but should be able to work for anything. I was able to use part of a tutorial from a TikTok on adding a Macro to the spreadsheet. You can probably google a tutorial on a macro. Essentially for mine I started a macro record and sorted the data by the date. I saved this and it worked but I wanted more. I also wanted it to move it to the bottom when complete and wanted it to do this itself.

I went in to the macro and edited it to also sort the checkbox column. (probably could've done both when recording but I hadn't thought of it when I made the original and I'm good at coding.)

I then hit the little clock button that is named triggers and made a trigger for "on edit". I have so far had no problems.

If anyone needs more help I can certainly try (I am still in college so pretty busy but love working with Excel/Google sheets)

@Monishbs
Copy link

Monishbs commented Feb 4, 2022

TypeErro: Cannot read property 'getDataRange' of undefined
autosort @ Auto Sort Check List gs:63

Identified a Fix for the above error:

Change the code @ lines 61-64:
From this:
function autoSort(sheet) {
// Get the entire set of data for this sheet.
var range = sheet.getDataRange();

To this:
function autoSort() {
// Get the entire set of data for this sheet.
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getDataRange();

@prateekjain07
Copy link

Hi, Mike. Amazing script but there's an issue with it. The onOpen() trigger isn't working on opening a sheet. Do you have an idea why?

@jamesforpm
Copy link

Hi Mike, is there a way to edit this script so that when data is added to the bottom of a sheet (i.e., via Zapier) the last row is moved to the top of the list and then when the checkbox is marked as done, the corresponding row is moved to the bottom of the sheet? I'm trying to create a to-do list where to-do's are added by Zapier but appear at the top of the list when it's added and then moved to the bottom upon task completion.

@jonathansoltero
Copy link

jonathansoltero commented Mar 20, 2022

I figured out how to make this work for multiple columns, this is what it looks like, it works, however, once a new cell is added the whole row moves with it in a non-alphabetical order, and actually chops up the column into a few ascending blocks, pretty much throwing the whole point of this script out the window.

`
// The numeric index of the column you wish to keep auto-sorted. A = 1, B = 2,
// and so on.
var SORT_COLUMN_INDEX_1 = 1;
var SORT_COLUMN_INDEX_2 = 2;
var SORT_COLUMN_INDEX_3 = 3;
var SORT_COLUMN_INDEX_4 = 4;
var SORT_COLUMN_INDEX_5 = 5;
var SORT_COLUMN_INDEX_6 = 6;
// Whether to sort the data in ascending or descending order.
var ASCENDING = true;
// 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_1, ascending: ASCENDING}, {column: SORT_COLUMN_INDEX_2, ascending: ASCENDING}, {column: SORT_COLUMN_INDEX_3, ascending: ASCENDING}, {column: SORT_COLUMN_INDEX_4, ascending: ASCENDING}, {column: SORT_COLUMN_INDEX_5, ascending: ASCENDING}, {column: SORT_COLUMN_INDEX_6, 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 theyre
// sorting by; otherwise, we perform unnecessary additional sorts if
// the targeted sort column's data didn't change.
if (editedCell.getColumn() == SORT_COLUMN_INDEX_1, SORT_COLUMN_INDEX_2, SORT_COLUMN_INDEX_3) {
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);
    }
    `

Does anyone know how to prevent the whole row from moving with the filter?

@SeanSullivanPM
Copy link

SeanSullivanPM commented Apr 4, 2022

The coordinates of the range are outside the dimensions of the sheet. - Please advise.

@gdurham5
Copy link

I'm getting an error message: Cannot read property 'getDataRange' of undefined (line 63, file "Code")

Any idea on what to do? Thanks in advance...

@jodzeee
Copy link

jodzeee commented May 12, 2022

@gdurham5 - @AdamSteinfurth put up a YouTube video and there's some discussion there and a possible solution to that issue.
https://www.youtube.com/watch?v=EOcU_JO83Ss&ab_channel=ProlificOaktree

@BernardHerger
Copy link

I have the script. Dont know how to link it to the spreadsheet i have in google sheet

@jodzeee
Copy link

jodzeee commented Jun 7, 2022

@BernardHerger The instructions to install it are written right in the script

@BernardHerger
Copy link

BernardHerger commented Jun 7, 2022 via email

@BernardHerger
Copy link

BernardHerger commented Jun 7, 2022 via email

@jodzeee
Copy link

jodzeee commented Jun 8, 2022

@BernardHerger Did you try the fix listed here: https://gist.github.com/mikebranski/285b60aa5ec3da8638e5?permalink_comment_id=4053245#gistcomment-4053245

or the one listed in the comments on the YouTube video?

@BernardHerger
Copy link

I got it to work.

Now I need to add the same code to another colunm so it also auto sorts it. I have seen it work. Have the code but cannot get it to work.

Also,

I want to TimeStamp coth colunms if they are checked.

I put both codes, Auto Sort and Time Stamp in same script and only runs the last one i place.

Not sure if you can help with this or know how can?

Thx

@BernardHerger
Copy link

BernardHerger commented Jun 8, 2022 via email

@plastergraffiti
Copy link

I just added this script and changed lines 61-64, but I get the error "The coordinates of the range are outside the dimensions of the sheet." I have tried to change all areas where it says (NUMBER_OF_HEADER_ROWS, 0) to (NUMBER_OF_HEADER_ROWS,1) because I have one header row, but all that does is move the header row to the middle and all other rows stay in place. Thoughts on what I should be changing?

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);

@ZuluPilgrim
Copy link

Hi, I am also getting

TypeError: Cannot read property 'getDataRange' of null autoSort @ Code.gs:64

And can't find any pointers on what the problem might be.

@napoellis
Copy link

Hello I am trying to get it to auto update and for some reason it wont, also I added a few columns after having this script setup and now when I input scores it gives the scores to someone else rather than who I gave the points too in the Doc.

@Falcude
Copy link

Falcude commented Sep 9, 2022

Hello, I am trying to freeze an x number of columns but whatever code I put at the bottom of your script does not work! Please help!!

@lwerickson3
Copy link

lwerickson3 commented Dec 8, 2022

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

@sepmaint
Copy link

@mikebranski I am using this on a spreadsheet linked to google froms. Is there a way to trigger this script once the spread sheet recieves the data from that form?

@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