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 ])
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
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) }
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!!! :) :) :)
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?
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.
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) }
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.
🥰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 ?
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
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?
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?
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
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!!
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.
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
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.
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.
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! 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?
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 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! 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?
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
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.
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.
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 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!
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?
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?
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?
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
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?
Simple and useful ! ...... Question : Your videos using Bootstrap, Materialize and Metro UI are really unique on RU-vid, 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.
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
@@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.
Firstly, thanks so much for this! It has been a great help! I’m so sorry if this is a “rookie” question, but I have got your formula working, however I still need to manually click on “run” in order for it to sort any new data that is entered. Did I miss a step somewhere?
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) }
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!
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.
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!
How do you make it sort automatically when the numbers change on their own without any input from you? Otherwise, this script works perfectly! Thank you for sharing.
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?
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.
@@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?
👍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?
ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-VBzjyQYICNw.html
Thank you. Can you make an educational video on the data of two tables, the first contains the identifier and the country, for example, and the second table contains the identifier and the capital. There must be a way to make the identifier itself and merge it with the state and the capital. I have student data that I have their identifiers and emails and I have their grades in Another table I do not know to add emails. Did you understand what I mean can you help me
@@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.
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.
