Skip to content

Instantly share code, notes, and snippets.

Embed
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);
}
@pgrafix
Copy link

pgrafix commented Jul 9, 2021

I have attempted to make the autosort function fire if ANY change to the sheet is made by changing the following:

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

to:

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

 autoSort(activeSheet);
}

However, it doesn't seem to be working. I need it to fire on any change due to the way Zapier deletes rows from Google Sheets - it really doesn't delete the data, rather it just blanks the data. Thus, I need the autosort to happen consistently to accommodate the lookup steps in my Zap or the wrong row will get deleted. Any help would be greatly appreciated.

@generaleverywhere
Copy link

generaleverywhere commented Jul 29, 2021

I'm facing problem for line 63, error !

any solution...?

@AdamSteinfurth
Copy link

AdamSteinfurth commented Aug 13, 2021

@mikebranski I am getting ready to have this code in a video on my YT Channel, Prolific Oaktree. I will give my viewers the link to this page and give you credit. Hope you're cool with that. LMK if you have questions. - Adam

@mikebranski
Copy link
Author

mikebranski commented Aug 13, 2021

@mikebranski I am getting ready to have this code in a video on my YT Channel, Prolific Oaktree. I will give my viewers the link to this page and give you credit. Hope you're cool with that. LMK if you have questions. - Adam

@AdamSteinfurth Hey, that sounds great. I appreciate the ping! Feel free to share a link when it's up. Love your channel's name, by the way. 🌳.

@AdamSteinfurth
Copy link

AdamSteinfurth commented Aug 13, 2021

@mikebranski I am getting ready to have this code in a video on my YT Channel, Prolific Oaktree. I will give my viewers the link to this page and give you credit. Hope you're cool with that. LMK if you have questions. - Adam

@AdamSteinfurth Hey, that sounds great. I appreciate the ping! Feel free to share a link when it's up. Love your channel's name, by the way. 🌳.

Here it is https://youtu.be/EOcU_JO83Ss. Welcome to internet stardom, my friend:)

@ac24365
Copy link

ac24365 commented Aug 17, 2021

@mikebranski Thanks for the wonderful script.
I want to run in on multiple workbooks. Instead of Copy& Paste individually, can we have a smarter way to achieve this? I tried using SpreadsheetApp.openByUrl but didnt manage. I have attached the code below. Can you help me with this?

/
 *
 * @author Mike Branski (@mikebranski)
 * @link https://gist.github.com/mikebranski/285b60aa5ec3da8638e5
 *
 * @OnlyCurrentDoc Limits the script to only accessing the current spreadsheet.
 */
var sheets = ("https://docs.google.com/spreadsheets/d/1********/, https://docs.google.com/spreadsheets/d/2******/");
   for(var i = 0; i < sheets.length; i++){
     var ss = SpreadsheetApp.openByUrl(sheets[i]);
      //code to do on all three sheets
      // 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.getActiveSpreadsheet().getSheetByName("Final Data");
        // 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.getActiveSpreadsheet().getSheetByName("Final Data");
        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);
      }
   }

@bkcurry
Copy link

bkcurry commented Aug 19, 2021

I am getting the same Error that multiple people above are getting:

TypeErro: Cannot read property 'getDataRange' of undefined
autosort @ Auto Sort Check List gs:63
Erro line 63 getdatarange of undfined-19-Aug-21

Please help tell me how to fix this issue

@bkcurry
Copy link

bkcurry commented Aug 19, 2021

Can anyone help with **

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

I have a look and am unable at this time to find a response to this question. However, it does appear that this has been an issue for several people in the above string??

@roadiecpq
Copy link

roadiecpq commented Aug 20, 2021

Changed line 92
activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Final Data");
Debug comes up with:
var range = sheet.getDataRange();

Variables:
local:
Sheet: undefined
range: undefined

@bkcurry
Copy link

bkcurry commented Aug 20, 2021

@bkcurry
Copy link

bkcurry commented Aug 25, 2021

@bkcurry
Copy link

bkcurry commented Aug 30, 2021

@shawnwyd
Copy link

shawnwyd commented Oct 1, 2021

Hey Mike,

I have this working in Google Sheets and although it works for my first Tab, it is not working in my second tab. In fact, an extra row appears. I have an attached video for reference.

@kimfinney22
Copy link

kimfinney22 commented Oct 19, 2021

How do I get rid of this auto sort function??? It is ruining my life right now!

@Friesian7
Copy link

Friesian7 commented Jan 31, 2022

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

prateekjain07 commented Feb 7, 2022

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

jamesforpm commented Feb 13, 2022

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

gdurham5 commented Apr 28, 2022

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

BernardHerger commented Jun 7, 2022

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

@BernardHerger
Copy link

BernardHerger commented Jun 7, 2022

@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

BernardHerger commented Jun 8, 2022

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

@plastergraffiti
Copy link

plastergraffiti commented Jun 13, 2022

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

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