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
Yes, you can. Instead of stopping at one column description, you may keep going. Exemple: range.sort( [ {column: 3, ascending: true}, // sorted by column 3 {column: 4, ascending: true}, // then sorted by column 4 {column: 1, ascending: true}, // then sorted by column 1 {column: 6, ascending: true} // then sorted by column 6 ])
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) }
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following: In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar). Click on Add Trigger. Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit". I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk (shoutout to ChatGPT)
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.
🥰AWESOME THANKS! Can you please show one where it sorts like this, but it is ordered in a specific way for example: On edit of Column C, it orders the data : NorthEast, South, West, MidWest ?
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following: In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar). Click on Add Trigger. Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit". I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk (shoutout to ChatGPT)
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
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following: In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar). Click on Add Trigger. Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit". I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following: In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar). Click on Add Trigger. Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit". I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk
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) }
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following: In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar). Click on Add Trigger. Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit". I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk (shoutout to ChatGPT)
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?
Simple and useful ! ...... Question : Your videos using Bootstrap, Materialize and Metro UI are really unique on RUclips, may be anytime soon we can see more videos ? Maybe userforms for searching and displaying the results inside the userform ? userforms with data validation ? Also more of map(), reduce(), filter() ..... Thanks.
@@ExcelGoogleSheets Thanks for the reply. I've seen most of them ! what I mean is if you are not going to make other videos with more examples about that.
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?
it works, when entering manually, which is great. But doesn't when I append using API, can you advise why? Probably need some other function, than onEdit(). Now I fixed it. Had to create a custom trigger running this sort function on 'onChange' event. Works like a charm, thanks for this tutorial!
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
@@ExcelGoogleSheets if there were several other pages that I wanted to do a similar thing for, would I have to create an entirely different script? Having trouble replicating it. In the other sheets, I can’t even use the sort filter for some reason. You can see it sort for a split second and then it goes back to the way it was immediately
Hello, thank you very much for your tips, they were of great help. A doubt: After performing the auto sort, I have a column in which, after being filled, the entire line is protected. How could I unite these two functionalities since, after being protected, the line numbers do not change in Protected Pages and Intervals. I would like after performing the auto sort and protection, the protected lines would adjust themselves as new data were inserted.
i'm having an issue where it's only checking the first number, but the code looks exactly the same but also something weird: i enter 11.12, and 10.98, but 11.12 goes to the top, yet i have the sort set to ascending: true however the sorter has functionality, it is just doing the wrong thing
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?
@@ExcelGoogleSheets i've been struggling learning javascript. Do you have any tip? Learning javascript so far is pushing me into learning html and css and i'm not intereted in developing websites, I'm more into self-service usage.
Thank you! Great Video. Nevertheless, I'm having a problem when I copy and paste new data at the end of the sheet. The onEdit() doesn't refresh. It seems to work only when I type new data in the desired cell. Any suggestions? Thank you again.
Hello! Thank you very much. Is it possible to do this on different sheets. In this case the script runs on "My Sheet". Lets say that I have another sheet that is called "Adams Sheet" is it possible to have to different scripts running at the same time on edit? I am not getting to work. Would appreciate an answer. Thank you
ruclips.net/video/VBzjyQYICNw/видео.html it actually works, even tho you get error message, just do something in your sorting row and the function will kick in
Thank you so much. Now I know how to automatically sort my google sheet. But how about if there are more than 1 conditions? say, I want to autosort by SALES, then by CATEGORY then by SALES REP. Is it possible? Thanks in advance
Awesome, content, didn't know I could do this. Do you have a tutorial on sorting by a specific row? What is its not the last column. I want to sort a list by number of hospital beds. But I don't want to change the total count at the bottom of the page. I want to sort the list of hospital names, by the Highest number of Hospital Bed count. Is there a video, I could watch to figure this out from? Please and thank you for your time.
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.
hi thanks for the tutorial, I am trying to implement it with a doPost method where I add rows to the sheet but it does not recognize me as an event that causes the values to be ordered according to the column I choose, it only works when I edit a value of that column on the sheet, how could I do?
Is there a way to make this work for multiple work sheets within a spreadsheet? I've tried making a script for each sheet however it only auto sorts the sheet with the most recent executed script. I'm super green to writing scripts... thanks for the help!
This is really great, but I am getting an error: 'TypeError: Cannot read property 'range' of undefined' This is my code: function autoSort(e){ const row = e.range.getRow() const column = e.range.getColumn() const ss = e.source const currentSheet = ss.getActiveSheet() const currentSheetName = currentSheet.getSheetByName() if(!(currentSheetName === "Phone Sheet" && column === 1 && row>=2)) return const range = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,6) range.sort({column: 1, ascending: true}) } function onEdit(e){ autoSort(e) }
Entered this function autoSort(){ const ss = SpreadsheetApp.getActiveSpreadsheet() const ws = ss.getSheetByName('TRANSACTIONS') const range = ws.getRange(1,2,ws.getLastRow()-1,7) range.sort({column: 1, ascending: true}) } got Exception: Cell Reference out of range... I am sorting by column 1 by a date which I will enter last as you indicated using the last column instead. What is the issue here?
How would I do this if I was using text instead of numbers. I’m wanting to mark certain things as “active” and “inactive” with the inactive items auto sorted to the bottom.
If you could post the script on the description also, it would be very nice and simple. Then we use your explanation to update the script according to our data
Edit: the script is working, but I don't know why this error keeps showing when trying to run the script. I am trying to run the script but this error appears: Error TypeError: Cannot read property 'range' of undefined autoSort @ Code.gs:2 Double checked many times and can't find the problem! Help please!
You can't manually run this function, because it can't work without an "event". It needs to automatically run when you make changes on the spreadsheet.
Thank you! So quick question: my sheet is now auto-sorting when I manually add a new row, but data that populates from google form responses does not automatically get sorted. Anyone know how to fix this?
This video was great! I've seem to run into an issue however. I'm sending form entries via Gravity Forms to my Google Sheet through a zapier integration, and when it adds/updates a row, including the trigger column (Entry Date) that I am trying to sort by, it doesn't seem to trigger as an edit? If I manually change it or manually add a row to the sheet, it works no problem. Anyone have any thoughts?
Check if whatever system you use for automation has a callback or webhook implementation. Your question has more to do with your automation system than Google Sheets.
Hey brother, this all works great for me, no issues, But is there a way for me to pick & choose which 'sheets' I want this added to? For example I have "XYZ Group of Sheets" which contains Sheet1, Sheet2, Sheet3 within the "XYZ Group of Sheets". Say I wanted to add this script formula to Sheet1 & Sheet3, is this possible?
It didn't work for me. I'm using Appsheet to add a row to a google sheet and for some reason the script does not activate. It works perfect when I add a row manually. Any suggestions?
The auto-sort script is great but only sorts for my account, the account Owner. The Sheet is shared with three other Editors. Auto sort does not work for them at all. Any insights would be greatly appreciated. Thank you for the great tutorial!!
Hey, I have tried this but it does not work for me. In my case I need to Auto-Sort the data if the sheet changes in anyway. The data is entered by another worksheet (from google forms). Any idea how to fix that? If I enter anything manually it work. GREAT TUTORIAL
Thanks for the help video, seems far too complicated for such a simple goal to make it sort them automatically. You would think they should of made this easier in the first place though as if you've sorted once should have the extra option to make this automatic, would think most users would want automatic as default. Oh well I'll look for an alternative.
Hey there , i would need to edit a data sheet that has no amount but instead of “completed” “Pending” “ongoing”. How to i edit those in the Script Editor.
hi! quick question, how can i add this function to a different sheet on the same file, though it has a different number of columns. do i just repeat the same program?
Thank you for your knowledge sharing. one step further for slicker appearance and better fuctionality would be to have a separate line only for input and when finish input automatically sort in the table.
very nice vid! I have two spreadsheets, I put the data in the first one and based on criteria data will be shown on the second spreadsheet, I was wondering if there was a script (I currently do it with filters manually) that I could auto hide blank rows on the second sheet and as soon as they get data they turn unhide. Thank you
Hello - is there anyway to change the function so it sorts only when the active spreadsheet is edited, rather than when any tab within the sheet is edited?
Hi there, I'm trying to organize a Google Spreadsheet by date, earliest to latest. I followed along this video but not sure how your's is moving altogether. The date will move but the row will not move with the corresponding info. Any idea how to fix this? Thanks!
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!!! :) :) :)
I'm following all of your steps but still getting this error "TypeError: Cannot read property 'range' of undefined autoSort @ Code.gs:2". What did I missed? The tutorial is great and clear tho! My failure makes no sense XD
I had the same issue but here's what I figured out. Short answer: If you copied the code exactly how he has it at the end you did everything correct. Even though the range error code comes up just hit save and when you edit the spreadsheet it will work. Long Answer: The reason the error code comes up is because the whole script only gathers information from the spreadsheet when there is an edit made on the spreadsheet. When you hit "Run" to try and test it out you did not make any edits on the spreadsheet. So since no edit was made, the script does not gather any information and therefore doesn't know what the range is. I hope that made sense. I was in the same position as you but I just hit save and everything works as intended.
Is there a way to have it autoSort by multiple columns using this script (Ex. have sales be sorted within each region that is sorted)? I tried to have two statements under the .sort for both columns but it didn't work. I'm not very knowledgeable on this stuff so help would be appreciated.
I have been playing around with the code tonight and I can not get it to work properly, for what I need done. In short, I have several rows of data being sorted by the date entered in column B, after the input in Column G (7), but also want the sort to work on the same row if there is data entered into Column H (9). I want the sort to work with sorting by the dates in column B if there is a Debit or Credit value (Column G or H). I have tried many times to play around with the code to get this to work, but either the Apps Script pukes or the sort will not run. If you can help, that would be great! Here is the code I have currently: function autoSort() {
autoSort() } My headers for my spreadsheet are as follows: DATE (**) SECURITY ACTION QUANTITY (or 2:1 Split ex.) PRICE PER SHARE PAYMENT (-) DEPOSIT BALANCE Column A is in front of the Date column shown above, so the Date column is column 2. I hope I explained it well enough on what I am trying to get done. As of right now, if I enter a row of data, when I put a value in the Payment column, the row will be sorted with the data directly above, by the date entered for that particular row being added into the spreadsheet. Thanks! Kevin
Thank you for the information ! There are 8 columns available. I want to auto sort by 7th column. But after writing the 8th column, I want it to sort by the 7th column. Do you any advice? Thank u.
@@ExcelGoogleSheets thank you. I did as you said. Was successful. But the data in the 8th column after the 7th column, where the sorting is, remains constant. I need your help. :)
👍Great Video. Thanks a lot. I have successfully applied the script and it is working fine. Can you help on this? If I want to apply the script for multiple tab on a same Google Sheet, what is the neccessary code changes needed?
It was very cool when it worked the first time I saved it, but then it just stopped working for no reason, and I'm back to sorting manually. I'm getting the same 'range' undefined error everyone else is mentioning.
Those who get range undefined error need to learn a bit about how onEdit function works. This should be a good start ruclips.net/video/frAL7bJkU54/видео.html
![NOSFERATU - Official Trailer [HD] - Only In Theaters December 25](http://i.ytimg.com/vi/nulvWqYUM8k/mqdefault.jpg)
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
Yes, you can. Instead of stopping at one column description, you may keep going.
Exemple:
range.sort( [
{column: 3, ascending: true}, // sorted by column 3
{column: 4, ascending: true}, // then sorted by column 4
{column: 1, ascending: true}, // then sorted by column 1
{column: 6, ascending: true} // then sorted by column 6
])
In case you don't find "Script Editor" under tools, it might be under extensions now.
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)
}
Same thing is happening to me...everything worked fine until he started moving things around for personal preference.
Any fix on this?
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following:
In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar).
Click on Add Trigger.
Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit".
I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk
(shoutout to ChatGPT)
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.
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?
🥰AWESOME THANKS! Can you please show one where it sorts like this, but it is ordered in a specific way for example: On edit of Column C, it orders the data : NorthEast, South, West, MidWest ?
Why it not work on my spreadsheet ?
TypeError: Cannot read property 'range' of undefined
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following:
In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar).
Click on Add Trigger.
Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit".
I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk
(shoutout to ChatGPT)
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
I continually receive an error indicating "Cannot read property 'range' of undefined." for line 2. Does anyone have any thoughts?
I am getting the same error. I did everything the same so not sure what’s wrong.
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following:
In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar).
Click on Add Trigger.
Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit".
I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk
Thank you for the video.
Your video really help me.
CAN ANYONE EXPLAIN WHY I AM GETTING A RANGE ERROR AND THIS IS NOT WORKING??
function autosort(e){
const row = e.range.getRow()
const column = e.range.getColumn()
const ss = e.source
const currentSheet = ss.getActiveSheet()
const currentSheetName = currentsheet.getSheetByName()
if(!(currentSheetName === "Inspectors sheet" && column === 1 && row >=2)) return
const range = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,15)
range.sort({column:1,ascending:true})
}
function onEdit(e) {
const row = e.range.getRow()
const column = e.range.getColumn()
if(!(currentSheetName === "Inspectors sheet" && column === 1 && row >=2)) return
autosort(e)
}
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following:
In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar).
Click on Add Trigger.
Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit".
I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk
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)
}
Hey I know this is two years late but the way to fix this is by making this a trigger. (Essentially, the code won't work if you press run from the script editor but it WILL work in practice. To make it a trigger do the following:
In the Google Sheets Script Editor, go to Triggers (the clock icon on the left sidebar).
Click on Add Trigger.
Set it to trigger onEdit and ensure the event type is "From spreadsheet" and "On edit".
I renamed the project to 'AutoSort' and just had it run the project. Hope this fixes it! If not lmk
(shoutout to ChatGPT)
just realized the 'shoutout to chaptgpt make it seems like I'm a bot. I'm not 💀💀
Great video! Thank you! 🙏🏼
Question: Can this be used to auto sort any check box ☑️ to the top of the list if it’s check
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?
Simple and useful ! ...... Question : Your videos using Bootstrap, Materialize and Metro UI are really unique on RUclips, may be anytime soon we can see more videos ? Maybe userforms for searching and displaying the results inside the userform ? userforms with data validation ? Also more of map(), reduce(), filter() ..... Thanks.
I already have videos covering all of this. Check on the channel.
@@ExcelGoogleSheets Thanks for the reply. I've seen most of them ! what I mean is if you are not going to make other videos with more examples about that.
@@TheTramos00 There will be a new one coming tomorrow.
@@ExcelGoogleSheets Great, I'll keep my eye out
DUDE YOURE 🔥🔥🔥🔥🔥 THANK YOU!!!!
THANK YOU!!! YOU HELPED ME A LOT!!!!
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?
it works, when entering manually, which is great. But doesn't when I append using API, can you advise why? Probably need some other function, than onEdit().
Now I fixed it. Had to create a custom trigger running this sort function on 'onChange' event. Works like a charm, thanks for this tutorial!
Could you tell me how you got it to start autosorting?
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
Great!
@@ExcelGoogleSheets if there were several other pages that I wanted to do a similar thing for, would I have to create an entirely different script? Having trouble replicating it. In the other sheets, I can’t even use the sort filter for some reason. You can see it sort for a split second and then it goes back to the way it was immediately
Hello, thank you very much for your tips, they were of great help. A doubt: After performing the auto sort, I have a column in which, after being filled, the entire line is protected. How could I unite these two functionalities since, after being protected, the line numbers do not change in Protected Pages and Intervals. I would like after performing the auto sort and protection, the protected lines would adjust themselves as new data were inserted.
Really helpful! I looked so much for sth like this! Thank you!
Glad it was helpful!
i'm having an issue where it's only checking the first number, but the code looks exactly the same
but also something weird: i enter 11.12, and 10.98, but 11.12 goes to the top, yet i have the sort set to ascending: true
however the sorter has functionality, it is just doing the wrong thing
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?
Nice work. I appreciate your work
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?
Well done! Did you learn javascript first or did you go directly google appscript?
I've learned JavaScript for other reasons. Contrary to what most people believe, I don't write apps script code very often.
@@ExcelGoogleSheets i've been struggling learning javascript. Do you have any tip? Learning javascript so far is pushing me into learning html and css and i'm not intereted in developing websites, I'm more into self-service usage.
Thank you! Great Video. Nevertheless, I'm having a problem when I copy and paste new data at the end of the sheet. The onEdit() doesn't refresh. It seems to work only when I type new data in the desired cell. Any suggestions? Thank you again.
Hello!
Thank you very much. Is it possible to do this on different sheets. In this case the script runs on "My Sheet". Lets say that I have another sheet that is called "Adams Sheet" is it possible to have to different scripts running at the same time on edit? I am not getting to work. Would appreciate an answer. Thank you
Hi
Thanks for the superb video but in my case error is coming in the second line
Cannot read property 'range' of undefined
What should I do about this
TypeError: Cannot read property 'range' of undefined
onEdit @ Code.gs:14
same here 😭
I have same issue
ruclips.net/video/VBzjyQYICNw/видео.html it actually works, even tho you get error message, just do something in your sorting row and the function will kick in
@@daniellefever5408 thought it did'nt work but if you do a change in your row the function will begin! ruclips.net/video/VBzjyQYICNw/видео.html
Thank you so much. Now I know how to automatically sort my google sheet. But how about if there are more than 1 conditions? say, I want to autosort by SALES, then by CATEGORY then by SALES REP. Is it possible? Thanks in advance
Awesome, content, didn't know I could do this. Do you have a tutorial on sorting by a specific row? What is its not the last column. I want to sort a list by number of hospital beds. But I don't want to change the total count at the bottom of the page. I want to sort the list of hospital names, by the Highest number of Hospital Bed count. Is there a video, I could watch to figure this out from? Please and thank you for your time.
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.
I am having the same problem :(
hi thanks for the tutorial, I am trying to implement it with a doPost method where I add rows to the sheet but it does not recognize me as an event that causes the values to be ordered according to the column I choose, it only works when I edit a value of that column on the sheet, how could I do?
Is there a way to make this work for multiple work sheets within a spreadsheet? I've tried making a script for each sheet however it only auto sorts the sheet with the most recent executed script. I'm super green to writing scripts... thanks for the help!
Is it possible to keep the first row empty so that we can add details to it without scrolling all the way down?
This is really great, but I am getting an error: 'TypeError: Cannot read property 'range' of undefined'
This is my code:
function autoSort(e){
const row = e.range.getRow()
const column = e.range.getColumn()
const ss = e.source
const currentSheet = ss.getActiveSheet()
const currentSheetName = currentSheet.getSheetByName()
if(!(currentSheetName === "Phone Sheet" && column === 1 && row>=2)) return
const range = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,6)
range.sort({column: 1, ascending: true})
}
function onEdit(e){
autoSort(e)
}
const currentSheetName = currentSheet.getSheetByName()
const currentSheetName = currentSheet.getSheetName()
I'm stuck, I cant get the auto to work, and also where can I find the Last row video ?
Good but sadly I don’t have the autosort button
Are you still an active channel? I need your help. I'll venmo you
Entered this function autoSort(){
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName('TRANSACTIONS')
const range = ws.getRange(1,2,ws.getLastRow()-1,7)
range.sort({column: 1, ascending: true})
} got Exception: Cell Reference out of range... I am sorting by column 1 by a date which I will enter last as you indicated using the last column instead. What is the issue here?
Great video, should we copy the script from the video or there is easier way to get it? 😃
Hello! does this do even if the data is edited that comes from another sheet because it's linked?
How would I do this if I was using text instead of numbers. I’m wanting to mark certain things as “active” and “inactive” with the inactive items auto sorted to the bottom.
How do I make sure so that when anyone change the sheet, the script will trigger? For now it only work when I edit the sheet?
can you do it so it sorts it when all cells of the row aren't blank instead of when you edit the last one?
Sure, you can add another if statement and check if the content in all columns != "" then sort.
If you could post the script on the description also, it would be very nice and simple.
Then we use your explanation to update the script according to our data
It's available to channel supporters on Patreon
www.patreon.com/chicagocomputerclasses
@@ExcelGoogleSheets ok. Thank you
It's nice, can you tell us, is it possible to auto highlight the active cell in sheets
Even though it's technically possible, it would be very slow and unpleasant to work with.
Thank you for the quick answer to my questions !
Error TypeError: The comparison function must be either a function or undefined.
Colaboración, por favor!
Mengapa Kalau saya input lewat google form datanya tidak otomatis mengurut sendiri ?
Automatically sort date&time
how would I do this to sort by date?
This was killer
Exactly what I needed. Thanks!
Edit: the script is working, but I don't know why this error keeps showing when trying to run the script.
I am trying to run the script but this error appears: Error
TypeError: Cannot read property 'range' of undefined
autoSort @ Code.gs:2
Double checked many times and can't find the problem! Help please!
You can't manually run this function, because it can't work without an "event". It needs to automatically run when you make changes on the spreadsheet.
@@ExcelGoogleSheets Thanks! It was working but I did not understand the error. Now I got it.
Thank you! So quick question: my sheet is now auto-sorting when I manually add a new row, but data that populates from google form responses does not automatically get sorted. Anyone know how to fix this?
This video was great! I've seem to run into an issue however. I'm sending form entries via Gravity Forms to my Google Sheet through a zapier integration, and when it adds/updates a row, including the trigger column (Entry Date) that I am trying to sort by, it doesn't seem to trigger as an edit? If I manually change it or manually add a row to the sheet, it works no problem. Anyone have any thoughts?
This will only be triggered from manual edits.
@@ExcelGoogleSheets Do you have any recommendation for what I should look into for automatic edits?
Check if whatever system you use for automation has a callback or webhook implementation. Your question has more to do with your automation system than Google Sheets.
Hey brother, this all works great for me, no issues,
But is there a way for me to pick & choose which 'sheets' I want this added to?
For example I have "XYZ Group of Sheets" which contains Sheet1, Sheet2, Sheet3 within the "XYZ Group of Sheets".
Say I wanted to add this script formula to Sheet1 & Sheet3, is this possible?
Thanks a lot! Great tutorial!
It didn't work for me. I'm using Appsheet to add a row to a google sheet and for some reason the script does not activate. It works perfect when I add a row manually. Any suggestions?
The auto-sort script is great but only sorts for my account, the account Owner. The Sheet is shared with three other Editors. Auto sort does not work for them at all. Any insights would be greatly appreciated. Thank you for the great tutorial!!
Hey, I have tried this but it does not work for me. In my case I need to Auto-Sort the data if the sheet changes in anyway. The data is entered by another worksheet (from google forms). Any idea how to fix that? If I enter anything manually it work. GREAT TUTORIAL
Thanks for the help video, seems far too complicated for such a simple goal to make it sort them automatically. You would think they should of made this easier in the first place though as if you've sorted once should have the extra option to make this automatic, would think most users would want automatic as default. Oh well I'll look for an alternative.
Hey there , i would need to edit a data sheet that has no amount but instead of “completed” “Pending” “ongoing”. How to i edit those in the Script Editor.
the built in function names must have changed. THis isn't working anymore
I'm pretty sure it still works fine.
Thank you for the great tutorial! Is it possible to apply autosort on more than one tab in Google Sheets with different conditions?
How do I sort by column instead of by row? I need to sort horizontally instead of vertically.
Everything you teach works perfectly and is incredibly useful. Thank you!
Smooth
Thank you!
hi! quick question, how can i add this function to a different sheet on the same file, though it has a different number of columns. do i just repeat the same program?
You are a living god among n, a legend worthy of praise. What you've uploaded here, will echo into eternity!
Thank you for your knowledge sharing. one step further for slicker appearance and better fuctionality would be to have a separate line only for input and when finish input automatically sort in the table.
very nice vid!
I have two spreadsheets, I put the data in the first one and based on criteria data will be shown on the second spreadsheet, I was wondering if there was a script (I currently do it with filters manually) that I could auto hide blank rows on the second sheet and as soon as they get data they turn unhide.
Thank you
learn what they are doing. I would learn how to lay down drums on the channel rack and use so loops, and then once you have your
This video is very much helpful and handy. Great learning of basics. Thanks a TON !!.
How can you sort if you have headers and under the header there is a formula?
After seeing this tutorial 30++ times
I can do on my own
Thank you Sir
How to do a universal conditional format for multiple workbook?
Where is the copy and paste section? I just need a working code to copy and paste onto my sheet.
What if you would like to sort by multiple criteria?
@ Learn Google Spreadsheets
is it possible to auto sort based on different row colors?
Thank you !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Hello - is there anyway to change the function so it sorts only when the active spreadsheet is edited, rather than when any tab within the sheet is edited?
Super cool!! I have to get my hands on training some more scripts for sheets!
Amazing, thanks you
Hi there, I'm trying to organize a Google Spreadsheet by date, earliest to latest. I followed along this video but not sure how your's is moving altogether. The date will move but the row will not move with the corresponding info. Any idea how to fix this? Thanks!
Make sure you enter the right number of columns when you identify the range size.
Seems to be working but I'm getting the error: "TypeError: Cannot read property 'range' of undefined autoSort @ AutoSort.gs:2". Should I be concerned?
Not really.
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!!! :) :) :)
🙂👍
does this work on google sheets app too when i sync it?
I'm following all of your steps but still getting this error "TypeError: Cannot read property 'range' of undefined
autoSort @ Code.gs:2". What did I missed? The tutorial is great and clear tho! My failure makes no sense XD
I had the same issue but here's what I figured out.
Short answer: If you copied the code exactly how he has it at the end you did everything correct. Even though the range error code comes up just hit save and when you edit the spreadsheet it will work.
Long Answer:
The reason the error code comes up is because the whole script only gathers information from the spreadsheet when there is an edit made on the spreadsheet. When you hit "Run" to try and test it out you did not make any edits on the spreadsheet. So since no edit was made, the script does not gather any information and therefore doesn't know what the range is.
I hope that made sense. I was in the same position as you but I just hit save and everything works as intended.
Is there a way to have it autoSort by multiple columns using this script (Ex. have sales be sorted within each region that is sorted)? I tried to have two statements under the .sort for both columns but it didn't work. I'm not very knowledgeable on this stuff so help would be appreciated.
.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);
I have been playing around with the code tonight and I can not get it to work properly, for what I need done. In short, I have several rows of data being sorted by the date entered in column B, after the input in Column G (7), but also want the sort to work on the same row if there is data entered into Column H (9). I want the sort to work with sorting by the dates in column B if there is a Debit or Credit value (Column G or H). I have tried many times to play around with the code to get this to work, but either the Apps Script pukes or the sort will not run. If you can help, that would be great! Here is the code I have currently:
function autoSort() {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName("Sheet1")
const range = ws.getRange(2,2,ws.getLastRow()-1, 8)
range.sort({column: 2, ascending: true})
}
function onEdit(e){
const row = e.range.getRow()
const column = e.range.getColumn()
if(!(column === 7 && row >=2)) return
//if(!(column === 8 && row >=2)) return
autoSort()
//if(!(column === 7 && row >=2)) return
//if(!(column === 8 && row >=2)) return
autoSort()
}
My headers for my spreadsheet are as follows:
DATE (**) SECURITY ACTION QUANTITY (or 2:1 Split ex.) PRICE PER SHARE PAYMENT (-) DEPOSIT BALANCE
Column A is in front of the Date column shown above, so the Date column is column 2.
I hope I explained it well enough on what I am trying to get done. As of right now, if I enter a row of data, when I put a value in the Payment column, the row will be sorted with the data directly above, by the date entered for that particular row being added into the spreadsheet.
Thanks!
Kevin
if(!( [7,8].includes(column) && row >=2)) return
Thank you for the information ! There are 8 columns available. I want to auto sort by 7th column. But after writing the 8th column, I want it to sort by the 7th column. Do you any advice? Thank u.
It's the same, just in sort code use column 7.
@@ExcelGoogleSheets I set it as the 7th column. But I don't want it to sort without filling the 8th column. Can you do something about this situation?
Yes. Use the sort column as 7, but set the onEdit column to 8.
@@ExcelGoogleSheets thank you. I did as you said. Was successful.
But the data in the 8th column after the 7th column, where the sorting is, remains constant. I need your help. :)
@@ExcelGoogleSheets
function mySort(e){
const row = e.range.getRow()
const column = e.range.getColumn()
const ss = e.source
const currentSheet = ss.getActiveSheet()
if(!(column === 8 && row >= 8)) return
const range = currentSheet.getRange(8,2,currentSheet.getLastRow()-1,7)
range.sort({column: 7, ascending: true})
}
function onEdit(e){
mySort(e)
}
Hi, I copy paste your code but I get this, how to fix it?
TypeError: Cannot read property 'range' of undefined
Don't manually run the function, it should run when you make changes.
👍Great Video. Thanks a lot. I have successfully applied the script and it is working fine. Can you help on this? If I want to apply the script for multiple tab on a same Google Sheet, what is the neccessary code changes needed?
tried with more consecutives scripts with differents sheet name?
It was very cool when it worked the first time I saved it, but then it just stopped working for no reason, and I'm back to sorting manually. I'm getting the same 'range' undefined error everyone else is mentioning.
Those who get range undefined error need to learn a bit about how onEdit function works. This should be a good start ruclips.net/video/frAL7bJkU54/видео.html
TypeError: Cannot read property 'getRange' of null