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
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!!! :) :) :)
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 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'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)
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?
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?
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
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.
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?
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
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.
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
Thank you very much this is a great video, learned and takes a lot. I am from Mexico City, even though my English is somewhat limited, I felt that I should at least thank you. Best regards.
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?
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.
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.
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!!
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?
🥰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 ?
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?
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!
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
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.
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?
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.
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?
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?
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.
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!
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
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.
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
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 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)
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?
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.
Simple and useful ! ...... Question : Your videos using Bootstrap, Materialize and Metro UI are really unique on UA-cam, 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.
Nice tutorials. I am learning a new things every day. when I do autosort function (onEdit),getting error. (12:07:44 PM Error TypeError: Cannot read property 'range' of undefined onEdit @ Code.gs:9) tried to figure it out, but no luck
One question there: I get my data from a pivot table and when it has new data, nothing happens. Is there a solution to update the auto sort once it receives the automated data? Thank you so much!
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
Great vid!! I needed this to sort a list of pending invoices and I wanted to sort by supplier name which I placed in the first column. What i did at 09:50 in my case was simply to not include the onEdit function, created a button on the sheet and assigned it to the autoSort function so that once I entered all information, I simply click the button
Hi! Could you share the steps for creating this button? I love the onEdit function but when I need to make more than 1 edit to a row, I will sometimes lose the row after it auto sorts but before I'm done editing. I'd love a better option
@@estanciahighschoolbaseball3610 to make the button, simply go to Insert > Image, then set the shape, text, colour etc as you want it, then simply assign it to the script function
@@Cirillu301 I don't know how to assign it to the script function...writing scripts is allllll new to me (though I successfully managed to figure out the one in this video)
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
@@ExcelGoogleSheets Thank you for your answer. I used modifed mikebranski's script: gist.github.com/BenoitDuffez/71dada8033e733d57f251d92de638ba4 The problem is that it works only onOpen and onInstall, whenever I try autoSort i receive following error: TypeError: Cannot read property 'getDataRange' of undefined autoSort @ code.gs:66 Because of that it doesn't work, do you know what should be changed?
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!
@@ExcelGoogleSheets THANK YOU FOR THIS. I was literally so stumped as to why my code wouldn't make my google sheet automatically sort. I didn't realize that the formula in the column I picked as the trigger column was causing problems. This solved my hours on end searching for the solution.
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
Will it work if sheet name is changed? For ex: You are mentioning the sheet name as "My Sheet". If someone changes the sheet name will that gets auto change in script or not?
I keep getting a "getSheetbyName is not a function" error when debugging - any ideas why it keeps saying that as I've heard it can mean the file is corrupted and a new one is required
Thank you for this easy to follow video! Can you please explain the difference between the Sort Range and Create Filter function? The both seem similar. I was able to figure out how to do the Create Filter when there is only one header row/column but the my spreadsheet has the same header names in three or four different sections on the same sheet and I don't know how to do that. Is this even possible?
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 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. :)
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) }
@@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! 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
In case you don't find "Script Editor" under tools, it might be under extensions now.
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!!! :) :) :)
🙂👍
After seeing this tutorial 30++ times
I can do on my own
Thank you Sir
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
])
You are a living god among n, a legend worthy of praise. What you've uploaded here, will echo into eternity!
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 :(
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?
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)
DUDE YOURE 🔥🔥🔥🔥🔥 THANK YOU!!!!
Everything you teach works perfectly and is incredibly useful. Thank you!
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?
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?
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
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.
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?
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
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.
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
Thank you very much this is a great video, learned and takes a lot.
I am from Mexico City, even though my English is somewhat limited, I felt that I should at least thank you.
Best regards.
Thanks! Your English seems great to me!
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?
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.
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.
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!!
Thank you for the quick answer to my questions !
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?
🥰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 ?
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 is very much helpful and handy. Great learning of basics. Thanks a TON !!.
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?
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!
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
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.
This is such a beautifully detailed video!
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?
@ Learn Google Spreadsheets
is it possible to auto sort based on different row colors?
Thank you for the great tutorial! Is it possible to apply autosort on more than one tab in Google Sheets with different conditions?
does this work on google sheets app too when i sync it?
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?
How do I sort by column instead of by row? I need to sort horizontally instead of vertically.
Very useful and quite good explanation. Thanks a lot dear makers.
:)
Thanks!
Appreciate it!
What if you would like to sort by multiple criteria?
Hello! does this do even if the data is edited that comes from another sheet because it's linked?
Exactly what I needed. Thanks!
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.
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's very useful to me ....Thank you Very much🙏👍 Sir...
Great!
Super cool!! I have to get my hands on training some more scripts for sheets!
Now it works, thanks for the tutorial!
Thank you for the video.
Your video really help me.
How to do a universal conditional format for multiple workbook?
Great video, should we copy the script from the video or there is easier way to get it? 😃
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?
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.
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?
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
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
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.
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
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 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 💀💀
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?
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}]);
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.
Really helpful! I looked so much for sth like this! Thank you!
Glad it was helpful!
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.
Excelente! Hope next time you discuss about auto lock cell after inserting some data using google script. Thanks!
Doing this makes no sense based on how user authorization works.
how would I do this to sort by date?
Simple and useful ! ...... Question : Your videos using Bootstrap, Materialize and Metro UI are really unique on UA-cam, 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
Thanks a lot! Great tutorial!
THANK YOU!!! YOU HELPED ME A LOT!!!!
I'm stuck, I cant get the auto to work, and also where can I find the Last row video ?
Nice tutorials. I am learning a new things every day. when I do autosort function (onEdit),getting error. (12:07:44 PM Error
TypeError: Cannot read property 'range' of undefined
onEdit @ Code.gs:9) tried to figure it out, but no luck
Facing the same problem.
Don't manually run the function. It should run automatically when you make changes to the spreadsheet.
@@ExcelGoogleSheets Its amazing! Works now perfectly. Thanks a lot from the core of my heart!
@@ExcelGoogleSheets But it doesn't run automatically when changes made. Pls help.
One question there: I get my data from a pivot table and when it has new data, nothing happens. Is there a solution to update the auto sort once it receives the automated data? Thank you so much!
Thanks a lot for sharing these. I was wondering if there is a way to set up the script for multiple tables under the same sheet?
Yes, it's possible.
Is it possible to keep the first row empty so that we can add details to it without scrolling all the way down?
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
Great vid!! I needed this to sort a list of pending invoices and I wanted to sort by supplier name which I placed in the first column. What i did at 09:50 in my case was simply to not include the onEdit function, created a button on the sheet and assigned it to the autoSort function so that once I entered all information, I simply click the button
Cool!
Any idea how to make it sort automatically when data changes automatically (without manual input)?
Hi! Could you share the steps for creating this button? I love the onEdit function but when I need to make more than 1 edit to a row, I will sometimes lose the row after it auto sorts but before I'm done editing. I'd love a better option
@@estanciahighschoolbaseball3610 to make the button, simply go to Insert > Image, then set the shape, text, colour etc as you want it, then simply assign it to the script function
@@Cirillu301 I don't know how to assign it to the script function...writing scripts is allllll new to me (though I successfully managed to figure out the one in this video)
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
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
Is it possible to make autosorting in column "A" + column "B"?
In case column "A" has the same value - it would by sorted by column "B"
.sort([{column: 1, ascending: true}, {column: 2, ascending: true}])
@@ExcelGoogleSheets Thank you for your answer.
I used modifed mikebranski's script: gist.github.com/BenoitDuffez/71dada8033e733d57f251d92de638ba4
The problem is that it works only onOpen and onInstall, whenever I try autoSort i receive following error:
TypeError: Cannot read property 'getDataRange' of undefined
autoSort @ code.gs:66
Because of that it doesn't work, do you know what should be changed?
Must the sheet be open to autosort or can I push data via api and it will still autosort?
You can add a callback function to your script that is pushing data via api.
Nice work. I appreciate your work
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.
The function autoSort works when I ran but I can get the function onedit to work. I've done it over 10x. 🥺😢
I'd suggest taking time to learn how onEdit works first. This video should help ua-cam.com/video/frAL7bJkU54/v-deo.html
@Learn Google Spreadsheets What if the column I want to sort is a formula?
Sorting it should be the same, but you need to choose a different trigger column to initiate sorting.
@@ExcelGoogleSheets THANK YOU FOR THIS. I was literally so stumped as to why my code wouldn't make my google sheet automatically sort. I didn't realize that the formula in the column I picked as the trigger column was causing problems. This solved my hours on end searching for the solution.
Where is the copy and paste section? I just need a working code to copy and paste onto my sheet.
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
Will it work if sheet name is changed?
For ex: You are mentioning the sheet name as "My Sheet". If someone changes the sheet name will that gets auto change in script or not?
Probably not becasue the comparison is hardcoded
I keep getting a "getSheetbyName is not a function" error when debugging - any ideas why it keeps saying that as I've heard it can mean the file is corrupted and a new one is required
getSheetByName NOT getSheetbyName
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.
Thank you for this easy to follow video! Can you please explain the difference between the Sort Range and Create Filter function? The both seem similar. I was able to figure out how to do the Create Filter when there is only one header row/column but the my spreadsheet has the same header names in three or four different sections on the same sheet and I don't know how to do that. Is this even possible?
Not with filters, but you can use sort on multiple separate ranges.
@@ExcelGoogleSheets Thanks for answering my question. :)
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
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.
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()
How to apply this to all sheets?
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.
Very helpful, thank you!!!
Thanks!