Create a gist now

Instantly share code, notes, and snippets.

Embed
/**
* 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

This comment has been minimized.

Show comment
Hide comment
@mix3d

mix3d Feb 11, 2016

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

mix3d commented Feb 11, 2016

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

@doublevv

This comment has been minimized.

Show comment
Hide comment
@doublevv

doublevv Jun 23, 2016

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

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

This comment has been minimized.

Show comment
Hide comment
@dmsig85

dmsig85 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.

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

This comment has been minimized.

Show comment
Hide comment
@JohnCRT

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

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

This comment has been minimized.

Show comment
Hide comment
@cheekygit

cheekygit Aug 31, 2016

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

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

@BriceMilano

This comment has been minimized.

Show comment
Hide comment
@BriceMilano

BriceMilano Sep 1, 2016

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?

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

This comment has been minimized.

Show comment
Hide comment
@kiel100

kiel100 Sep 7, 2016

Waiting for your answeeeerr pleaseee :)

kiel100 commented Sep 7, 2016

Waiting for your answeeeerr pleaseee :)

@mikebranski

This comment has been minimized.

Show comment
Hide comment
@mikebranski

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

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!

@IanAtkin

This comment has been minimized.

Show comment
Hide comment
@IanAtkin

IanAtkin May 27, 2017

@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, @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},
} );
@hannahroyce

This comment has been minimized.

Show comment
Hide comment
@hannahroyce

hannahroyce Jun 1, 2017

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

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

@andrewkmin

This comment has been minimized.

Show comment
Hide comment
@andrewkmin

andrewkmin Jul 10, 2017

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.

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.

@bsebastian86

This comment has been minimized.

Show comment
Hide comment
@bsebastian86

bsebastian86 Aug 2, 2017

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.

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

This comment has been minimized.

Show comment
Hide comment
@addrum

addrum 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

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

This comment has been minimized.

Show comment
Hide comment
@mrbenjaminryder

mrbenjaminryder Aug 23, 2017

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?

mrbenjaminryder commented Aug 23, 2017

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

This comment has been minimized.

Show comment
Hide comment
@eengebruiker

eengebruiker Sep 9, 2017

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é

eengebruiker commented Sep 9, 2017

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é

@stereosympathy

This comment has been minimized.

Show comment
Hide comment
@stereosympathy

stereosympathy Sep 12, 2017

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

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

This comment has been minimized.

Show comment
Hide comment
@dukeblue2017

dukeblue2017 Sep 13, 2017

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.

dukeblue2017 commented Sep 13, 2017

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.

@sparzatka

This comment has been minimized.

Show comment
Hide comment
@sparzatka

sparzatka Sep 18, 2017

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.

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

This comment has been minimized.

Show comment
Hide comment
@sdirghalli

sdirghalli Sep 21, 2017

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!

sdirghalli commented Sep 21, 2017

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!

@sdirghalli

This comment has been minimized.

Show comment
Hide comment
@sdirghalli

sdirghalli Sep 23, 2017

@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

@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

@mikebranski

This comment has been minimized.

Show comment
Hide comment
@mikebranski

mikebranski Oct 4, 2017

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

Owner

mikebranski commented Oct 4, 2017

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

@Engacker

This comment has been minimized.

Show comment
Hide comment
@Engacker

Engacker Oct 19, 2017

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

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

@peterhartree

This comment has been minimized.

Show comment
Hide comment
@peterhartree

peterhartree Oct 20, 2017

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

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

@ivanlinares

This comment has been minimized.

Show comment
Hide comment
@ivanlinares

ivanlinares Oct 25, 2017

To sort by 2 or more columns follow this: brackets are important, and define the variables on top, or specify the column number directly.

 // Perform the actual sort.
  range.sort([{column: SORT_COLUMN_INDEX, ascending: ASCENDING}, {column: SORT_COLUMN_INDEX2, ascending: ASCENDING}
             ]);
}

To sort by 2 or more columns follow this: brackets are important, and define the variables on top, or specify the column number directly.

 // Perform the actual sort.
  range.sort([{column: SORT_COLUMN_INDEX, ascending: ASCENDING}, {column: SORT_COLUMN_INDEX2, ascending: ASCENDING}
             ]);
}
@TVtrojans

This comment has been minimized.

Show comment
Hide comment
@TVtrojans

TVtrojans Oct 31, 2017

Ok, so weird question, I have been struggling with this one for a couple days now.
I need to do an auto sort on 4 separate sections of 6the same sheet, but only sorting by different columns. Columns G and N contain a formula that determines a percentage and I need to sort 4 columns of 7 rows by the percentage in their last column. The sheet has to stay divided in 4 "blocks" of data.
Example I need to sort columns A-G rows 7-13 by column G then rows 19-25 also by column G. Column's I - N need to be sorted by Column N also in Rows 7-13 and then 19 - 25.

Ok, so weird question, I have been struggling with this one for a couple days now.
I need to do an auto sort on 4 separate sections of 6the same sheet, but only sorting by different columns. Columns G and N contain a formula that determines a percentage and I need to sort 4 columns of 7 rows by the percentage in their last column. The sheet has to stay divided in 4 "blocks" of data.
Example I need to sort columns A-G rows 7-13 by column G then rows 19-25 also by column G. Column's I - N need to be sorted by Column N also in Rows 7-13 and then 19 - 25.

@adityarao310

This comment has been minimized.

Show comment
Hide comment
@adityarao310

adityarao310 Nov 20, 2017

Nicely done @mikebranski
Thanks - this is a life saver! I am genuinely tempted to make a UI for this :)

Nicely done @mikebranski
Thanks - this is a life saver! I am genuinely tempted to make a UI for this :)

@adityarao310

This comment has been minimized.

Show comment
Hide comment
@adityarao310

adityarao310 Nov 21, 2017

Btw for everyone who is asking on how to do sorting on a specific sheet only, change this from line 26
Do the same for range if you want it only on range in a sheet. The properties for this can be found here https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename

Here X is the sheet name you want
var required_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("X"); var range = required_sheet.getDataRange();

Btw for everyone who is asking on how to do sorting on a specific sheet only, change this from line 26
Do the same for range if you want it only on range in a sheet. The properties for this can be found here https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename

Here X is the sheet name you want
var required_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("X"); var range = required_sheet.getDataRange();

@VitalyPitt

This comment has been minimized.

Show comment
Hide comment
@VitalyPitt

VitalyPitt Dec 13, 2017

Well, it sorts by the numeric value in a cell, not by the time modified. Is there any way to sort by the time modified?

Well, it sorts by the numeric value in a cell, not by the time modified. Is there any way to sort by the time modified?

@ricekrispy408

This comment has been minimized.

Show comment
Hide comment
@ricekrispy408

ricekrispy408 Dec 13, 2017

@adityarao310 are you sure you meant line 26? thanks

@adityarao310 are you sure you meant line 26? thanks

@ThomasGHenry

This comment has been minimized.

Show comment
Hide comment
@ThomasGHenry

ThomasGHenry Jan 7, 2018

Thank you!!

Thank you!!

@DugalMcCrow

This comment has been minimized.

Show comment
Hide comment
@DugalMcCrow

DugalMcCrow Jan 17, 2018

Thank you - this has really helped me with a Google Sheets Gradebook I have been adapting to collate marks from multiple Google Forms Quizzes.

Thank you - this has really helped me with a Google Sheets Gradebook I have been adapting to collate marks from multiple Google Forms Quizzes.

@lucalo4

This comment has been minimized.

Show comment
Hide comment
@lucalo4

lucalo4 Feb 6, 2018

Thank you very much, it is really helpful.
Is there any way to use different variables for column_index, ascending and header, for different sheets?
For example: Sheet1 --> 2, false, 1 // Sheet2 --> 16, true, 3 // Sheet3 --> 4, true, 2

Thank you in advance

lucalo4 commented Feb 6, 2018

Thank you very much, it is really helpful.
Is there any way to use different variables for column_index, ascending and header, for different sheets?
For example: Sheet1 --> 2, false, 1 // Sheet2 --> 16, true, 3 // Sheet3 --> 4, true, 2

Thank you in advance

@remedcu

This comment has been minimized.

Show comment
Hide comment
@remedcu

remedcu Feb 21, 2018

Will this work if the data to be sorted is text/string?

remedcu commented Feb 21, 2018

Will this work if the data to be sorted is text/string?

@mikebranski

This comment has been minimized.

Show comment
Hide comment
@mikebranski

mikebranski Feb 22, 2018

@VitalyPitt That's not something I've tried doing, but let me know if it works!

@lucalo4 I don't see why you couldn't do that. You can add more variables (or a mapping) and switch which gets used based on the current sheet.

@remedcu Yeah, no reason it shouldn't work with strings. It's just calling the range.sort method, which is, I believe, what gets called when you manually sort using the Sheets UI, and that works on string values.

Owner

mikebranski commented Feb 22, 2018

@VitalyPitt That's not something I've tried doing, but let me know if it works!

@lucalo4 I don't see why you couldn't do that. You can add more variables (or a mapping) and switch which gets used based on the current sheet.

@remedcu Yeah, no reason it shouldn't work with strings. It's just calling the range.sort method, which is, I believe, what gets called when you manually sort using the Sheets UI, and that works on string values.

@lucalo4

This comment has been minimized.

Show comment
Hide comment
@lucalo4

lucalo4 Mar 14, 2018

Thanks mike, here is my question:
I have a spreadsheet with some financial stocks information. Thanks to googlefinance the prices refresh every 20min.
In the same spreadsheet I have 4 different sheets, each for a different fund.

Your auto sort function performs really good for the main sheet. Because everytime I open the spreadsheet, the price values refresh and the auto sort column function good.
The problem is, that when I change the sheet, it does not auto sort the columns. It only works if I go to the script-->oninstall-->debug. But this is a little anoying. Do you know, if the code can be changed to sort the columns everytime a sheet is opened?

Thank you in advance. Regards from Switzerland.
Luca

lucalo4 commented Mar 14, 2018

Thanks mike, here is my question:
I have a spreadsheet with some financial stocks information. Thanks to googlefinance the prices refresh every 20min.
In the same spreadsheet I have 4 different sheets, each for a different fund.

Your auto sort function performs really good for the main sheet. Because everytime I open the spreadsheet, the price values refresh and the auto sort column function good.
The problem is, that when I change the sheet, it does not auto sort the columns. It only works if I go to the script-->oninstall-->debug. But this is a little anoying. Do you know, if the code can be changed to sort the columns everytime a sheet is opened?

Thank you in advance. Regards from Switzerland.
Luca

@ameredith

This comment has been minimized.

Show comment
Hide comment
@ameredith

ameredith Apr 3, 2018

Thank you! This was super helpful!!

Thank you! This was super helpful!!

@nycaur

This comment has been minimized.

Show comment
Hide comment
@nycaur

nycaur Apr 9, 2018

Thanks a lot...very helpful indeed.
However, it says some errors on line 23 - though script seems to still execute and sort... I've incl. a link to my test sheet- editable....pls. help debug this error (I dont know scripting..)
LINK of SHEET https://docs.google.com/spreadsheets/d/1RO42cURGe85bfV7XlTVrsOlcqb_EWi_pD74B05EUg6A/edit?usp=sharing
Desired outscome is that as date is edited (col 1) and tasks added (col 2) the script shd. auto sort on date by ascending order. And is there a way to incorporate sheet name, because this workbook will likely have multiple sheets and I only want to run this script on this 1 sheet named currently "Sheet2".

While currently I've used an add-in for emailing this sheet to myself each day when due date is active...but if someone can suggest a modified script which can also send email to a customizable email address (or the one in Col 3)- so it shd. send email (1 or 3 days - customizable) before when date is due (in Col 1) and also paste in body of email all the rows (descriptions from col 2) where Col 1 date is equal or less than the current date- wud be great. Thanks.

nycaur commented Apr 9, 2018

Thanks a lot...very helpful indeed.
However, it says some errors on line 23 - though script seems to still execute and sort... I've incl. a link to my test sheet- editable....pls. help debug this error (I dont know scripting..)
LINK of SHEET https://docs.google.com/spreadsheets/d/1RO42cURGe85bfV7XlTVrsOlcqb_EWi_pD74B05EUg6A/edit?usp=sharing
Desired outscome is that as date is edited (col 1) and tasks added (col 2) the script shd. auto sort on date by ascending order. And is there a way to incorporate sheet name, because this workbook will likely have multiple sheets and I only want to run this script on this 1 sheet named currently "Sheet2".

While currently I've used an add-in for emailing this sheet to myself each day when due date is active...but if someone can suggest a modified script which can also send email to a customizable email address (or the one in Col 3)- so it shd. send email (1 or 3 days - customizable) before when date is due (in Col 1) and also paste in body of email all the rows (descriptions from col 2) where Col 1 date is equal or less than the current date- wud be great. Thanks.

@CoryHSmit

This comment has been minimized.

Show comment
Hide comment
@CoryHSmit

CoryHSmit May 8, 2018

Thanks for your work on this script! Have you got any ideas on how to make it sort rows based on values generated by a formula?

Your script could potentially save me loads of time - I run my project management using sheets and often need to group stuff based on status.

Here's what I've done and what I want to do:
I've got +200 entries in my sheet. Each entry starts with a status (i.e. Ideas, Backlog, To Do, In Progress etc). To help keep the groups sorted together when statuses change but without sorting alphabetically, I made a formula to spit out a value for each status code. Here it is:

=if(A6=0, , IFS(A6="Ideas", 1, A6="Backlog", 2, A6="To do", 3, A6="Sprint", 4, A6="In Progress", 5, A6="Review", 6, A6="Done", 7))

Each time I change the value in A6 (or any other row), say from Ideas to In Progress, column B6 then changes from 1 to 5. This lets me sort items quickly without having to drag them around (and disappear into the space-time vortex that is dragging in sheets).

I found your script to help me manage the info live without having to go through the process of creating a filter to exclude the headers then hitting sort A-Z.

However, the script doesn't appear to work when there values to be sorted are created by a formula. Any chance of working around this? Sheets seems to be able to do it, albeit a schlep.

Thanks in advance!

Thanks for your work on this script! Have you got any ideas on how to make it sort rows based on values generated by a formula?

Your script could potentially save me loads of time - I run my project management using sheets and often need to group stuff based on status.

Here's what I've done and what I want to do:
I've got +200 entries in my sheet. Each entry starts with a status (i.e. Ideas, Backlog, To Do, In Progress etc). To help keep the groups sorted together when statuses change but without sorting alphabetically, I made a formula to spit out a value for each status code. Here it is:

=if(A6=0, , IFS(A6="Ideas", 1, A6="Backlog", 2, A6="To do", 3, A6="Sprint", 4, A6="In Progress", 5, A6="Review", 6, A6="Done", 7))

Each time I change the value in A6 (or any other row), say from Ideas to In Progress, column B6 then changes from 1 to 5. This lets me sort items quickly without having to drag them around (and disappear into the space-time vortex that is dragging in sheets).

I found your script to help me manage the info live without having to go through the process of creating a filter to exclude the headers then hitting sort A-Z.

However, the script doesn't appear to work when there values to be sorted are created by a formula. Any chance of working around this? Sheets seems to be able to do it, albeit a schlep.

Thanks in advance!

@mgutscher1

This comment has been minimized.

Show comment
Hide comment
@mgutscher1

mgutscher1 May 15, 2018

Hello
How can you change the onedit function to a time trigger
function onEdit(event) {
var editedCell;
thanks

Hello
How can you change the onedit function to a time trigger
function onEdit(event) {
var editedCell;
thanks

@taylorsaysso

This comment has been minimized.

Show comment
Hide comment
@taylorsaysso

taylorsaysso Jun 8, 2018

@mikebranski , thanks for the code.

I've followed (I think) the modifications that you and @IanAtkin suggested to allow sorting of multiple columns, but keep receiving an error when saving:

Invalid property ID. (line 77, "Code")

Line 77 is the first line calling out column 4 in the code snippet, below. No other changes were made to the original script.

// Perform the actual sort.
range.sort( {
{column: 4, ascending: true},
{column: 5, ascending: true},
{column: 7, ascending: true},
{column: 6, ascending: true},
} );
}

What is this noob missing that is keeping the script from running properly? My sheet is a simple database of real estate parcels that I want to organize by City, Neighborhood, Street, and Street Number. Manually re-sorting after each batch of new entries (via a Google Form) is a time killer for me. Also, I want coworkers who are tech illiterate to be able to add a parcel from the form and then view the associated sheet already sorted.

Thanks for any help you might be able to offer.

taylorsaysso commented Jun 8, 2018

@mikebranski , thanks for the code.

I've followed (I think) the modifications that you and @IanAtkin suggested to allow sorting of multiple columns, but keep receiving an error when saving:

Invalid property ID. (line 77, "Code")

Line 77 is the first line calling out column 4 in the code snippet, below. No other changes were made to the original script.

// Perform the actual sort.
range.sort( {
{column: 4, ascending: true},
{column: 5, ascending: true},
{column: 7, ascending: true},
{column: 6, ascending: true},
} );
}

What is this noob missing that is keeping the script from running properly? My sheet is a simple database of real estate parcels that I want to organize by City, Neighborhood, Street, and Street Number. Manually re-sorting after each batch of new entries (via a Google Form) is a time killer for me. Also, I want coworkers who are tech illiterate to be able to add a parcel from the form and then view the associated sheet already sorted.

Thanks for any help you might be able to offer.

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