Tech Tips #66 - Automatically Continuously Sort Data in Google Sheets
Summary
TLDRThis video demonstrates how to use a custom Google Apps Script to continuously auto-sort columns in Google Sheets. The script was created by Mike Brandtsey and keeps data sorted in real-time as new entries are added. The video walks through customizing the script by setting the sort column, sort order, and number of header rows. After saving the script and reloading the sheet, any new data entered will automatically be sorted in the selected column. This allows users to always view organized data without any manual sorting required.
Takeaways
- 😀 The video shows how to continuously auto-sort data in a Google Sheets column whenever new data is added
- 👉🏻 Uses a custom Google Apps Script written by Mike Bronski from GitHub
- 👨💻 Opens the Google Sheets script editor, copies in the script code, and makes edits to 3 parameters
- ➡️ Sort column index - which column to sort
- ➡️ Ascending or descending order
- ➡️ Number of header rows to ignore
- 👍🏻 Saves the script and reloads the sheet to test real-time auto-sorting
- 🆕 Adds new numbers to show auto-sorting in action, keeping data ordered
- 🙏🏻 Thanks Mike Bronski for sharingopen-source script to enable this functionality
- 💡 Suggests viewers leave comments/questions, subscribe for more content
Q & A
What was the initial question asked by one of the viewers?
-The initial question was if it is possible to continuously/automatically sort data in a spreadsheet column anytime new data is added.
What Google script is used to enable continuous sorting in Google Sheets?
-The script used is by Mike Brandt's key available on GitHub.
What are the three things that need to be configured in the script?
-The three things that need to be configured are: 1) The sort column index, 2) If the sorting should be ascending or descending, 3) The number of header rows.
How do you specify which column to sort?
-The sort column is specified using its index, where A = 1, B = 2, etc. So to sort column A, the sort column index would be set to 1.
How do you specify the sorting order?
-The sorting order is set using a True/False value. True means ascending order and False means descending order.
What does the number of header rows configure?
-It configures how many rows at the top of the sheet are static headers/titles and should not be sorted.
What happens after the script is configured and enabled?
-After enabling, any new data added to the configured column will automatically and continuously sort based on the configured settings.
Does the automatic sorting work for numeric as well as text data?
-Yes, the script supports automatic sorting for both text and numeric data entered in the configured column.
Do you need to rerun the script for the auto-sort to work?
-No, once configured and enabled, the auto-sort runs continuously in the background without needing to rerun the script.
What if you need sorting on multiple columns?
-You would need to configure and enable a separate instance of the script for each column that needs continuous, automatic sorting.
Outlines
📺 Introducing the Auto Data Sorting Script
Tom introduces himself as the director of technology and operations at Colonial Intermediate Unit. He states that this is tech tips video #66, which shows how to use a Google script created by Mike Brandt to automatically sort data in a Google Sheets column any time new data is added. Tom demonstrates how to install and configure the script.
Mindmap
Keywords
💡Google Sheets
💡GitHub
💡Auto-sort
💡Script Editor
💡Ascending vs. Descending
💡Column Index
💡Header Rows
💡Code
💡Customization
💡New Content
Highlights
The question was is it possible to sort data in a column continuously as new data is added
Thanks to Mike Brandt's script on GitHub that can auto sort Google Sheets data anytime new data is added
Go to Tools > Script editor to access a blank script page
Copy Mike's auto sort script code and paste it into your script editor
Update the script with the sort column index, ascending/descending preference, and number of header rows
Reload the sheet and now any new data added will automatically sort
The script puts new data in the correctly sorted position automatically
The script keeps data continuously sorted with no further effort needed
Special thanks to Mike Bronski for creating the reusable Google Sheets script
The auto sort script saves time and ensures properly ordered data
The script is customizable by configuring the sort parameters as needed
Feel free to suggest scripts or ask questions to improve future tech tips
Don't forget to subscribe and enable notifications for new tech tip content
Auto sorting data continuously is a huge timesaver with dynamic sheets
Customizable scripts like this help tap the full power of Google Sheets
Transcripts
good evening my name is Tom kalinosky
I'm the director of technology and
operations at Colonial Intermediate Unit
xx welcome to tech tips number 66 this
tip is a result of one of our viewers
questions the question was is it
possible to sort data in a column but
not just one time but continuously so
anytime new data is added the data would
Auto sort and it is so thanks to Mike
Brandt's key on github he is a Google
script that does just that and I'm going
to show you how to do it so the first
thing we do is we have ourself a
spreadsheet you can see I put test and
Row 1 there with a couple of random
numbers there below let's go to tools
script editor that brings up this blank
page here let's go to Mike's script and
I'll put a link to this in the
description so you can grab it and we
want to select all of his codes so start
at the top click and hold and slowly go
down then you get to the end right here
after that let's right click and copy ok
go back to our script window let's make
sure to delete anything that's in there
isn't it so we'll just highlight delete
now let's paste and hit save on the name
doesn't really matter in this case we'll
put keep data sorted
now there's three things that we need to
change and the script does tell you
exactly what you need to change but
we're going to go down here and I'm
going to show you exactly where it is
so sort column index which column needs
to be sorted so he tells you here a is
equals 1 B is equal to 2 so in this case
our data was in column a so we're gonna
put of 1 there and do we want it to be a
sending or descending
so a sending let's uh we want ours to be
a sending so we're gonna make this true
if you wanted to be descending it would
be false and the number of header rows
so how many rows at the top are static
or titles for the data in this case it
is just 1 let's go ahead and hit save
again you could close this now and now
let's go ahead and reload our document
okay and you can see there that that
data sorted it all by itself now if we
were to go to add any other numbers in
here it again will sort by itself so
let's put something that'll be up at the
top let's go 10 hit enter and you can
see it put it up at the top if we do
something in between let's say 75 and it
puts it better and if we put something
at the way end it should stay there
that's a way that you could keep your
data continuously sorted special thanks
again to Mike Bronski for coming up with
that code that we could use inside of
Google sheets I hope you found this tip
valuable if you have any other
suggestions or questions please feel
free to put it in the comments below
don't forget to subscribe and hit the
bell on that we can become aware of any
new content that I post have a great day
and I'll see you again next time bye
Просмотреть больше связанных видео
5.0 / 5 (0 votes)