Automatically Sort Data in Google Sheets

Learn how to use Apps Script to automatically sort our Google Sheets data as we enter new records.

#sort #auto #googlesheets

[alanis]

In case you don't find "Script Editor" under tools, it might be under extensions now.

[kevinsaint]

Thank you! The weight lifting coach at my school uses Google Sheets to input scores at the meets and he said his biggest gripe was that the totals don’t sort so the other teams know who is currently winning, etc. Used your functions and just had to change the ‘column’ condition inside the if statement from “column === max” to “column <= max” so it would sort the total column even if changes were made to the values in other columns.

Thanks again!

[RoseLK]

Hi, thanks a lot! Wondering if we can sort multiple columns by using Script editor? Same as when we choose Data > Sort Range > Advanced > Choose multiple columns

[AadityaDwivedi-oc]

This works if I am making the edits manually, but not when the data is pushed in through an automated Zap process. can anyone help me out here please?

[cmozify]

I've truly appreciated many of the videos you're shared. As a newbie to Apps Script, I attempted replicating to "autosort" only the active sheet but encountered this error:

>> TypeError: Cannot read property 'range' of undefined

All function errors including onEdit: gs:2 and gs:19

const range = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 7)

Only two things are different from your video example: 1) my table has data starting at Column 1 instead of Column 2 and 2) the active sheet is called "Stat Journal". What missteps have I done? Pointers and trouble-shooting tips would be appreciated!

(COPIED CODES)
function mySort(e){
const row = e.range.getRow()
const column = e.range.getColumn()
const ss = e.source
const currentSheet = ss.getActiveSheet()
const getSheetName = currentSheet.getSheetName()

if(!(currentSheetName === "Stat Journal" && column === 7 && row >= 2)) return

const range = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 7)

range.sort({column: 7, ascending: false})

}


function onEdit(e){

mySort(e)

}

[imputedrighteousnessminist]

FINALLY!!! ALL the other sites--except for one or two which weren't good at teaching IMO, kept mentioning the filtering, which is VERY annoying, since it's not dynamic. THANK YOU THANK YOU THANK YOU!!! WHAT A HUGE HELP!!! IT LOOKS AWESOME!!! :) :) :)

[lukemenzel]

I am having trouble once I get to timestamp 8:44. When I run the script it will sort the values how I want them to but when I input a new number into the last column it does not auto sort. What could I be doing wrong? Any feedback would be greatly appreciated.

[suprantes]

After seeing this tutorial 30++ times
I can do on my own
Thank you Sir

[Paul-lypw]

Brilliant video, thank you. I’ve been trying to make something similar work and failed until now.

Can I ask how you code an extra feature? I have a jobs list and there is a column for the status (new, assigned, completed) but also a priority column.
I would like to sort by status but within each status, I would like to sort by priority. how would i do that?

[sbhanuprakash]

This is awesome!!. Is it possible to sort the data with a specific date which we enter on any cell and it should sort ascending order according to the specified date. can this be done? It would be a great help for me.

[alifitroni]

Why it not work on my spreadsheet ?
TypeError: Cannot read property 'range' of undefined

[phamvanthienfplhcm]

You are a living god among n, a legend worthy of praise. What you've uploaded here, will echo into eternity!

[jayliu]

Very helpful, thank you! and can you plz tell me how to stop the script if I don't want the sheet to be sorted automatically?

[slalomdrags]

what if I have a function already in the last column that I want to auto sort? I tried auto sorting with the simple fx=SORT(range, sort_column, is_ascending) and it wouldn't do it because I already had a SUM function for that column from the 2 columns to the left of it. Any suggestions?

[ge]

Hello. Thank you very much for your explanation on this video. I have a question regarding to sort an entire row, if X cell have value "Y" or X cell have TRUE value from checkbox. So for this case, if it cell have crossed, the entire row will automatically sorted to the bottom. Could you please make the video for this problem? Thank you in advance

[GiladOfir]

Great video, should we copy the script from the video or there is easier way to get it? 😃

[austincrouse]

I continually receive an error indicating "Cannot read property 'range' of undefined." for line 2. Does anyone have any thoughts?

[imsankarb]

Thank you for the great tutorial! Is it possible to apply autosort on more than one tab in Google Sheets with different conditions?

[NTR-OTC]

Your tips are great, thank you. I have a question and a scenario and I don't explain myself well so please, hang in there with me while I try. I have two live feed cameras that track trains. We keep track of all trains that pass us on Google Spreadsheets on a very impressive-looking log. We know how long it takes to get from one point to another from one cam to the other. What I am thinking of is when we enter a time on one location, somehow the background color changes on the other location's time slot for 15 minutes to a light color. Then 5 minutes a darker shade, then 5 minutes a darker one. After 30 minutes, or whatever prescribed time, after the train was due, I was kind of hoping to make the background change to make it appear to be blinking. If I made myself clear, is something like this even possible and if so where would I go to learn how to do this?

[runic]

I have done my code exactly like yours except with values from my sheet in it and I'm getting error "Cannot read property 'range' of undefined". I cannot tell what I'm doing wrong. Here's my code. Help?

function autoSort(e){
const row = e.range.getRow()
const column = e.range.getColumn()
const ss = e.source
const currentSheet = ss.getActiveSheet()
const currentSheetName = currentSheet.getSheetName()

if(!(currentSheetName === "School Assignments" && column === 5 && row >= 2)) return

const range = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 6)

range.sort({column: 5, ascending: true })

}

function onEdit(e){

autoSort(e)
}